SQL如何解析JSON格式的字段_JSON_EXTRACT与路径表达式

MySQL 5.7+ 怎么用 JSON_EXTRACT 取出嵌套字段

直接用 JSON_EXTRACT 拿值，但路径写错就返回 NULL，不是报错——这点特别容易让人误以为数据丢了。

路径必须以 $ 开头，比如取顶层键 name： JSON_EXTRACT(json_col, ‘$.name’)数组下标从 0 开始，$.items[0].price 才能取第一个元素的 price，写成 [1] 就越界键名含空格或特殊字符得用双引号括起来：JSON_EXTRACT(json_col, ‘$."user id"’)如果字段本身是 NULL 或不是合法 JSON，JSON_EXTRACT 也返回 NULL，建议先用 JSON_VALID(json_col) 做过滤

PostgreSQL 里怎么等效实现 -> 和 ->> 的区别

PostgreSQL 不用 JSON_EXTRACT，而是靠操作符区分返回类型：一个返 JSON 对象，一个返文本字符串。

json_col -> ‘data’ 返回 json 类型（还能继续链式取值），json_col ->> ‘data’ 返回 text（直接可参与字符串比较或拼接）取数组元素用 -> 0，不是 [0]；嵌套时连写，比如 json_col -> ‘items’ -> 0 ->> ‘id’如果路径不存在，-> 返回 NULL（类型仍是 json），->> 返回 NULL（类型是 text），注意和空字符串 ” 区分对性能敏感的查询，避免在 WHERE 里反复调用 ->>，可建生成列 + 索引：ALTER TABLE t ADD COLUMN name_text TEXT GENERATED ALWAYS AS (data ->> ‘name’) STORED

为什么 JSON_EXTRACT 返回带引号的字符串？怎么去掉

因为标准行为就是返回 JSON 类型值，字符串会自动加双引号——这不是 bug，是规范要求。你看到 "value" 是对的，value 反而是错的。

MySQL 中用 JSON_UNQUOTE(JSON_EXTRACT(…)) 去掉外层引号，但只对字符串有效；数字、布尔值、NULL 用它会出问题更稳妥的是用 ->>（MySQL 5.7.13+）：它等价于 JSON_UNQUOTE(JSON_EXTRACT(…))，且自动处理类型，SELECT json_col ->> ‘$.name’ 直接得干净字符串PostgreSQL 没这困扰：->> 天然去引号，-> 保留结构，分工明确

JSON 路径表达式在 WHERE 条件里慢，怎么优化

直接在 WHERE 写 JSON_EXTRACT(col, ‘$.status’) = ‘active’，基本等于全表扫描——JSON 字段没法走普通 B-tree 索引。

MySQL 支持虚拟列 + 索引：ALTER TABLE t ADD status_virt VARCHAR(20) AS (json_col ->> ‘$.status’), ADD INDEX idx_status (status_virt)PostgreSQL 推荐用 jsonb_path_ops 索引，但只加速存在性判断（@?）；精确匹配还得配合生成列或 jsonb_path_query_array 配合 GIN别在 JSON 字段上做函数索引以外的计算，比如 UPPER(JSON_EXTRACT(…))，索引完全失效如果只是偶尔查，且数据量小，不如应用层解析，数据库专干存和关联的事

路径表达式看着像 JS，其实各数据库语法细节差很多，连 $ 后面能不能跟空格、要不要转义点号都不同。上线前一定拿真实数据跑一遍边界 case，比如 key 为 user.name 或 0 这种数字字符串。