SQL JSON_TABLE / JSON_VALUE / JSON_QUERY 的标准 SQL JSON 处理实践

json_table仅在from子句中有效，必须作为派生表使用；json_value默认静默返回NULL，建议加Error on error调试；json_query需据场景选with/ without wrapper；跨数据库使用时注意类型、语法及索引失效问题。

JSON_TABLE 用错位置就查不出数据

JSON_TABLE 不是万能解析器，它只在 FROM 子句里生效，不能当普通函数嵌在 select 或 WHERE 里用。常见错误是写成 SELECT JSON_TABLE(...) 或 WHERE col = JSON_TABLE(...)，这会直接报语法错误。

• 必须作为派生表出现在 FROM 后，比如 FROM JSON_TABLE(...) AS jt
• 每个 JSON_TABLE 调用会生成一行或多行结果，结构由 COLUMNS 子句定义，字段名要和后续 SELECT 中的引用一致
• 如果源 JSON 是 null、空字符串或格式非法，整行会被跳过（不是报错），容易漏数据但不提示
• mysql 8.0.22+ 和 oracle 12c+ 支持，postgresql 不支持原生 JSON_TABLE，得用 jsonb_to_recordset() 替代

示例：从 JSON 数组提取用户 ID 和状态

SELECT u.id, u.status FROM JSON_TABLE(   '[{"id":1,"status":"active"},{"id":2,"status":"pending"}]',   '$[*]' COLUMNS (     id int PATH '$.id',     status VARCHAR(20) PATH '$.status'   ) ) AS u;

JSON_VALUE 返回 NULL 不一定是数据问题

JSON_VALUE 默认行为是 strict mode，路径不存在或类型不匹配时返回 NULL，而不是报错。这会让调试变得隐蔽——你以为字段丢了，其实是路径写错了或者 JSON 嵌套比预期深一层。

• 加 ERROR ON ERROR 可强制报错，快速暴露路径问题，开发阶段建议加上
• 路径表达式里别漏掉 $，'$.user.name' 和 'user.name' 效果完全不同
• 如果目标值可能是数字但你声明了 VARCHAR，MySQL 会自动转，但 Oracle 要求类型严格匹配，否则也返回 NULL
• 性能上，JSON_VALUE 比 JSON_EXTRACT + 类型转换略快，因为一步到位，但只适合取单个标量值

示例：安全提取用户名

SELECT JSON_VALUE(data, '$.user.profile.name' ERROR ON ERROR) AS name FROM logs WHERE id = 123;

JSON_QUERY 选错 wrapper 会导致前端解析失败

JSON_QUERY 默认不加引号，返回的是“裸” JSON 片段（如 {"age":30}），但如果字段被当成字符串处理（比如塞进另一个 JSON 的 value 里），就会多一层引号变成 "{"age":30}"，前端 JSON.parse() 直接炸。

• 用 WITH WRAPPER 得到带引号的字符串，适合存进文本字段或拼接日志
• 用 WITHOUT WRAPPER（默认）得到可嵌套的合法 JSON 值，适合返回给 API 或再进 JSON_TABLE
• MySQL 不支持 JSON_QUERY，只能用 JSON_EXTRACT，但它返回的是带引号的字符串，行为更接近 JSON_QUERY ... WITH WRAPPER
• Oracle 中若路径指向原子值（如字符串、数字），WITHOUT WRAPPER 会报错，必须用 WITH Conditional WRAPPER 或改用 JSON_VALUE

示例：提取地址对象并保持 JSON 结构

SELECT JSON_QUERY(payload, '$.address' WITHOUT WRAPPER) AS address_json FROM orders;

混合使用时注意 SQL 标准差异和隐式类型转换

同一个 SQL 在 MySQL、Oracle、SQL Server 上跑，JSON_VALUE 可能返回字符串，JSON_QUERY 可能被当成 TEXT，JOIN 条件里直接比较会因类型不一致而失效。

• Oracle 把 JSON_VALUE 结果当 VARCHAR2，MySQL 当 TEXT，SQL Server 当 NVARCHAR(MAX)，跨库迁移时字段长度限制可能突然卡住
• 别在 WHERE 里直接写 JSON_VALUE(col, '$.id') = 123，数值比较前先显式转类型，比如 CAST(JSON_VALUE(col, '$.id') AS SIGNED)（MySQL）或 TO_NUMBER(JSON_VALUE(...))（Oracle）
• JSON_TABLE 的 COLUMNS 定义类型不参与运行时校验，如果 JSON 里填了 “abc” 却声明 INT PATH '$.id'，MySQL 会转成 0，Oracle 报错，行为不统一
• 大 JSON 字段走这些函数时，索引基本失效，WHERE 中依赖 JSON_VALUE 过滤千万级表，务必提前建生成列 + 索引

真正麻烦的不是语法，是同一份 SQL 脚本在不同环境执行结果不一致，而且错误往往延迟出现——比如上线后某天某个边缘 JSON 格式变化，才开始返回空或报错。