如何在SQL中通过视图将JSON字段解析为扁平关系表-SQL-PHP中文网

JSON_TABLE是MySQL 8.0.14+将JSON数组展开为多行关系表的唯一原生函数，必须在FROM子句中使用$[*]路径匹配数组元素，配合COLUMNS提取字段，不可用JSON_EXTRACT替代。

如何在sql中通过视图将json字段解析为扁平关系表

MySQL 8.0+ 怎么用 `JSON_TABLE` 把 JSON 字段展开成行

MySQL 8.0.14 起才支持 JSON_TABLE，这是最直接的解法。它能把嵌套 JSON 当作“虚拟表”来 JOIN，不用写一堆 JSON_EXTRACT。

常见错误是把路径写成 $.items[0].name 这种固定下标——这只能取第一个，实际要展开数组得用 $[*]。

路径必须用 $[*]（匹配数组所有元素）或 $（匹配整个 JSON）开头，否则解析失败
COLUMNS 子句里用 FOR ORDINALITY 可以加序号列，方便调试顺序
如果 JSON 字段为 NULL 或格式非法，整行会静默丢弃，建议先用 JSON_VALID() 过滤

SELECT u.id, jt.name, jt.price
FROM users u,
JSON_TABLE(u.preferences, '$.cart.items[*]' COLUMNS (
  name VARCHAR(50) PATH '$.product_name',
  price DECIMAL(10,2) PATH '$.amount'
)) AS jt
WHERE JSON_VALID(u.preferences);

PostgreSQL 怎么用 `jsonb_to_recordset` 展开数组字段

PostgreSQL 的 jsonb_to_recordset 更灵活，但要求你显式声明返回列的类型和名字，不能靠推断。

容易踩的坑是：输入 JSON 必须是数组（[{...}, {...}]），不是对象（{...}）。如果源字段是单个对象，得先包一层 ARRAY[the_json]。

列名必须和 JSON 键名完全一致（区分大小写），否则值为 NULL
如果某条记录里某个键缺失，对应列就是 NULL，不会报错
性能上，jsonb_to_recordset 比多次 ->> 提取快，尤其字段多时

SELECT u.id, jt.name, jt.price
FROM users u,
jsonb_to_recordset(u.preferences->'cart'->'items') AS jt(
  name TEXT,
  price NUMERIC
)
WHERE u.preferences ? 'cart';

SQL Server 怎么用 `OPENJSON` 配合 `WITH` 子句做映射

SQL Server 的 OPENJSON 是唯一能直接把 JSON 数组转成结果集的函数，但必须配 WITH 显式定义 schema。

Expanso yaml-to-json

使用 Expanso Edge 管道将 YAML 输入转换为 JSON 格式。

下载

最常出错的是路径写法：$.cart.items 是对的，但写成 $.cart.items[*] 就会报错——SQL Server 不支持这种通配语法，数组路径本身就隐含了展开语义。

WITH 里的列名不一定要和 JSON 键名相同，可以用别名，但类型必须匹配（比如 JSON 字符串不能映射到 INT）
如果 JSON 中有 null 值，默认映射为 SQL NULL；想转成默认值，得在 WITH 里加 DEFAULT 子句
没加 WITH 直接调 OPENJSON(json) 只返回键/值/类型三列，没法直接当业务表用

SELECT u.id, jt.name, jt.price
FROM users u
CROSS APPLY OPENJSON(u.preferences, '$.cart.items')
  WITH (
    name VARCHAR(50) '$.product_name',
    price DECIMAL(10,2) '$.amount'
  ) AS jt
WHERE ISJSON(u.preferences) = 1;