SQL中如何使用JSON_TABLE函数将JSON转为关系表？-SQL-PHP中文网

json_table函数必须提供json源、路径表达式和columns子句三部分；mysql 8.0.4+与oracle 12cr2+均支持但语法差异大，裸字符串需显式转换为json类型，路径须严格匹配数据结构，否则返回空或null。

sql中如何使用json_table函数将json转为关系表？

JSON_TABLE函数的基本用法和必需参数

MySQL 8.0.4+ 和 Oracle 12cR2+ 都支持 JSON_TABLE，但语法细节差异大，直接照搬会报错。核心是必须提供三个部分：一个 JSON 源（可以是列、字面量或子查询）、一个路径表达式（定位到数组或对象），以及至少一个 COLUMNS 子句定义输出字段。

常见错误是把 JSON 字符串直接写进函数当第一个参数——它不接受裸字符串，得用 JSON_OBJECT、JSON_ARRAY 或带引号的字符串加 CAST(... AS JSON) 包裹。

MySQL 示例：JSON_TABLE('{"id":1,"tags":["a","b"]}', '$' COLUMNS (id INT PATH '$.id', tag VARCHAR(10) PATH '$.tags[0]'))
Oracle 示例：JSON_TABLE('{"items":[{"x":1},{"x":2}]}', '$.items[*]' COLUMNS (x NUMBER PATH '$.x'))
路径必须匹配数据结构：如果源是对象但路径写成 $[*]，会返回空结果

处理嵌套数组时怎么避免只取第一个元素

默认用 PATH '$.array[0]' 只能拿到首项，真要“展开数组”必须把路径写成数组遍历形式，并在 COLUMNS 中声明 FOR ORDINALITY 或用 Nested Path（MySQL）/ Nested（Oracle）。

MySQL 要展开数组，路径得设为 '$[*]' 或 '$.items[*]'，然后在 COLUMNS 里用 Nested PATH 嵌套下一层：

JSON_TABLE(
  '[{"name":"A","scores":[90,85]},{"name":"B","scores":[95]}]',
  '$[*]' COLUMNS (
    name VARCHAR(10) PATH '$.name',
    NESTED PATH '$.scores[*]' COLUMNS (score INT PATH '$')
  )
)

Oracle 则用两级 JSON_TABLE 或单层中写 '$.scores[*]' 并配合 FOR ORDINALITY 来对齐行数。

chartjs-reporter

此技能用于在用户需要将结构化数据（查询结果、CSV 摘要、JSON 记录或 Python 字典/列表）转换为独立的 HTML 页面时使用。

下载

没加 NESTED PATH 就想展开多值字段 → 结果行数不变，只取第一个值
MySQL 中 NESTED PATH 后不能跟 FOR ORDINALITY，Oracle 可以
Oracle 对 $.arr[*] 的支持更严格，空数组会导致整行丢失，需提前 WHERE JSON_EXISTS(...) 过滤

NULL值、类型转换失败和缺失字段怎么控制

JSON_TABLE 遇到路径不存在、类型不匹配时默认返回 NULL，但不会报错——这容易掩盖数据质量问题。可用 ON ERROR 和 ON EMPTY 显式声明行为。

MySQL 支持 ON EMPTY NULL（默认）、ON EMPTY DEFAULT 'N/A'，以及 ON ERROR 处理解析失败；Oracle 类似，但关键字是 ERROR ON ERROR 或 NULL ON ERROR。

字段声明为 INT PATH '$.age'，但 JSON 里是 "age": "25" → MySQL 会静默转成 25，Oracle 可能报错，取决于版本和 SQL mode
想让缺失字段报错中断查询，MySQL 加 ON EMPTY ERROR，Oracle 加 ERROR ON EMPTY
路径写错如 '$.user.name' 但实际是 '$.profile.name' → 整列 NULL，很难排查，建议先用 JSON_EXTRACT(col, '$.user.name') 测试路径

性能和索引注意事项

JSON_TABLE 是运行时解析，无法利用 JSON 列上的函数索引（比如 CREATE INDEX idx_age ON t ((CAST(json_col ->> '$.age' AS UNSIGNED)))），所以别在大表上无过滤地展开整个 JSON 数组。

真正影响性能的是路径复杂度和嵌套层数。每多一层 NESTED PATH，MySQL 就多一次全量扫描；Oracle 的 JSON_TABLE 在未启用 INMEMORY 时也明显变慢。

先用 WHERE JSON_CONTAINS(json_col, '"active"', '$.status') 过滤再 JSON_TABLE，比反过来快得多
MySQL 8.0.22+ 支持 JSON_TABLE 推入条件下推，但仅限简单等值条件，范围查询仍无效
Oracle 如果 JSON 数据来自远程表（DBLINK），JSON_TABLE 会拉取全部 JSON 再解析，网络开销极大

最常被忽略的是：不同数据库对相同 JSON 字符串的解析精度可能不同，比如浮点数截断、时间格式兼容性、Unicode 转义处理——跨库迁移前务必用真实数据样本验证输出一致性。