JSON 取值是什么
从 JSON 文档里按键或下标取值：MySQL 提供 JSON_EXTRACT 函数以及 -> 与 ->> 运算符，PostgreSQL 用 -> 取 JSON 片段、->> 取文本，嵌套路径用 #>>。两库的路径写法不同：MySQL 用 '$.user.name' 这样的带美元符表达式，PostgreSQL 用 '{user,name}' 的数组形式。取值结果类型要分清：单箭头 -> 返回的仍是 JSON，参与比较前通常需要转换；双箭头 ->> 直接返回文本。对 JSON 列做过滤一般用不上普通索引，可借助生成列或函数索引。
基本信息
| MySQL 函数 | JSON_EXTRACT(doc, '$.k') |
|---|---|
| MySQL 运算符 | doc -> '$.k' 返回 JSON，doc ->> '$.k' 返回文本 |
| PostgreSQL 运算符 | doc -> 'k' 返回 JSON，doc ->> 'k' 返回文本 |
| 嵌套路径 | MySQL 用 '$.a.b'，PostgreSQL 用 #>> '{a,b}' |
| 类型注意 | -> 的结果仍是 JSON，比较前要转成标量 |
| 索引 | JSON 条件一般无法走普通索引，可建生成列或函数索引 |
典型使用场景
- 扩展字段存储：把变动频繁的属性放在 JSON 列里
- 从上游报文里取值：解析接口返回的结构化数据
- 多值字段查询：数组里是否包含某个元素
- 日志与埋点数据：按字段筛选事件属性
- 配置读取：从配置文档里取单个开关
- 数据迁移：把 JSON 里的字段拆成独立列
示例
示例
-- MySQL
SELECT JSON_EXTRACT(payload, '$.user.name') AS name_json, # 返回 JSON
payload ->> '$.user.name' AS name_text, # 返回文本
payload ->> '$.tags[0]' AS first_tag
FROM events;
-- PostgreSQL
SELECT payload -> 'user' ->> 'name' AS name_text,
payload #>> '{user,name}' AS name_text2,
payload -> 'tags' ->> 0 AS first_tag
FROM events;
-- 过滤：注意文本与类型的区别
SELECT * FROM events WHERE payload ->> 'status' = 'paid';运算符对照
| 需求 | MySQL | PostgreSQL |
|---|---|---|
| 取 JSON 片段 | doc -> '$.k' | doc -> 'k' |
| 取文本值 | doc ->> '$.k' | doc ->> 'k' |
| 取嵌套路径文本 | doc ->> '$.a.b' | doc #>> '{a,b}' |
| 数组元素 | doc ->> '$.tags[0]' | doc -> 'tags' ->> 0 |
| 判断键存在 | JSON_CONTAINS_PATH | ? 运算符或 jsonb_exists |
PostgreSQL 的 jsonb 支持索引（GIN），字段多、查询频繁时优先用 jsonb 而不是 json；MySQL 的 JSON 类型是二进制存储，可直接建生成列并为其加索引。
用 JSON 列做过滤条件通常走不了索引，数据量大时先考虑把高频查询字段拆成独立列，JSON 只留低频扩展属性。
取值结果的类型推断要留意：MySQL 的 ->> 返回字符串，与数字比较时可能发生字符串比较，必要时显式 CAST 成目标类型。
使用要点
- 路径写错不会报错，只会返回 NULL，取完先校验几行结果
- 高频过滤字段拆成普通列，JSON 只放低频扩展属性
- MySQL 可用生成列加索引提升 JSON 过滤性能
- PostgreSQL 用 jsonb 以获得更好的索引与更新支持
- 导出 JSON 时统一编码与转义规则，避免下游解析失败
- 更新 JSON 内部字段优先用库提供的 JSON_SET 等函数，而不是字符串替换