JSON 路径语法是什么
访问 JSON 内部的值得先写对路径。MySQL 从 $ 开始，支持嵌套键 $.a.b、数组下标 $[0]、通配 $[*] 与递归 $**.key、区间 $[1 to 3]、$[last]，但不支持过滤表达式。PostgreSQL 有两套：操作符链 payload->'a'->>'b'（-> 返回 json/jsonb，->> 返回文本），以及 SQL/JSON path——jsonb_path_query(payload, '$.items[*] ? (@.price > 100)')，它支持过滤谓词，能力比 MySQL 的路径更强。
基本信息
| MySQL 路径 | 以 $ 开头：$.a.b、$[0]、$[*]、$**.name、$[last] |
|---|---|
| MySQL 特殊键 | 键名含点号、横线或空格时用双引号包住，如 $."order-id" |
| PostgreSQL 操作符 | -> 取 json/jsonb，->> 取 text，#> 与 #>> 按路径数组取 |
| PostgreSQL jsonpath | jsonb_path_query / jsonb_path_exists / @? / @@，支持过滤器 |
| 能力差异 | MySQL 路径不支持 ? (…) 过滤表达式；PostgreSQL 支持 |
典型使用场景
- 从 JSON 列里取单个字段做展示或比较
- 取数组元素（第一条标签、第一个商品）
- 递归查找同名键，处理结构不固定的报文
- 判断某个路径是否存在，用于数据完整性校验
- 按 JSON 内部条件筛选行（PostgreSQL 的 jsonpath 过滤器）
- 对比迁移前后取值结果，确认路径语法在新库里同样有效
MySQL 路径写法
MySQL 路径写法
SELECT JSON_EXTRACT(payload, '$.user.name') AS name_1, # 取嵌套字段
payload->'$.user.age' AS age_json, # -> 返回 JSON 类型
payload->>'$.user.age' AS age_text, # ->> 返回字符串
JSON_EXTRACT(payload, '$.tags[0]') AS first_tag, # 数组第一个元素
JSON_EXTRACT(payload, '$**.id') AS any_id, # ** 递归查找同名键
JSON_EXTRACT(payload, '$[last]') AS last_elem; # 数组最后一个元素
# 键名含特殊字符时用双引号包住
SELECT JSON_EXTRACT(payload, '$."order-id"') AS oid;PostgreSQL 路径写法
PostgreSQL 路径写法
SELECT payload->'user'->>'name' AS name_1, -- 操作符链，末步 ->> 取文本
payload #> '{user,age}' AS age_json,-- 路径数组写法等价于 ->->
payload #>> '{user,tags,0}' AS first_tag,
jsonb_path_query_first(payload, '$.user.tags[0]') AS q1
FROM events;
-- 带过滤器的 jsonpath：取价格大于 100 的元素
SELECT jsonb_path_query(payload, '$.items[*] ? (@.price > 100)') FROM events;
SELECT count(*) FROM events WHERE payload @? '$.items[*] ? (@.price > 100)';能力对照
| 能力 | MySQL 8.0 | PostgreSQL |
|---|---|---|
| 嵌套键 | $.a.b | payload->'a'->>'b' |
| 数组下标 | $.a[0]、$[last] | payload->'a'->0 |
| 通配与递归 | *、** | jsonpath 的 *、** |
| 区间取值 | $[1 to 3] | 用 jsonpath 或数组函数处理 |
| 过滤表达式 | 不支持 | 支持 ? (@.price > 10) |
| 返回类型 | JSON_EXTRACT 返回 JSON，->> 返回字符串 | -> 返回 json/jsonb，->> 返回文本 |
MySQL 的旧式路径字符串与 JSON_TABLE、JSON_EXTRACT 共用同一套语法，写错不会报错只会返回 NULL；建议先在真实样本上验证路径，再写进业务语句。
路径取值失败是静默的：返回 NULL 既可能表示键不存在，也可能表示值为 JSON null，两者在业务上含义不同，需要区分时应配合 JSON_CONTAINS_PATH 或 EXISTS PATH 判断。
通配与递归（*、**）返回的是数组或集合，通常还要再迭代取值；这类表达式无法走普通索引，只适合结果集较小、或配合生成列与多值索引使用的场景。