JSON_TABLE 是什么
JSON_TABLE 把一段 JSON 文档当成虚拟表来查：用 COLUMNS 子句声明输出的列与类型，配合 FOR ORDINALITY 给展开出来的行编号，还能用 NESTED PATH 展开嵌套数组。它解决的是「JSON 数组要按行参与连接、过滤与聚合」的典型需求。MySQL 8.0 提供该函数；PostgreSQL 的等价做法是 LATERAL 配合 jsonb_array_elements，或用 jsonb_to_recordset 一次性映射成强类型列。
基本信息
| 适用平台 | MySQL 8.0+（PostgreSQL 无此函数） |
|---|---|
| 语法骨架 | JSON_TABLE(json_doc, 路径 COLUMNS (列定义…)) AS 别名 |
| 常用列定义 | 标量类型（varchar / int / decimal）、EXISTS PATH 判断键是否存在、FOR ORDINALITY 行号 |
| 嵌套展开 | NESTED PATH '$.子数组[*]' COLUMNS (…) 逐层展开 |
| PostgreSQL 等价 | LATERAL jsonb_array_elements 或 jsonb_to_recordset |
典型使用场景
- 把订单里的商品数组展开成行，按商品维度统计销量
- 展开埋点参数做维度聚合，省掉应用层解析
- 与普通表连接：把 JSON 数组里的 sku 关联到商品主表取名称
- 校验数组元素完整性：找出缺少必填键的元素
- 把 JSON 数组转成关系行后写回临时表或宽表
- 按数组长度做筛选：先展开计数，再找出元素过多或为空的记录
MySQL 展开与聚合
MySQL 展开与聚合
-- items 列存的是 [{"sku":"A1","qty":2},{"sku":"B2","qty":1}]
SELECT t.rn, t.sku, t.qty
FROM orders o,
JSON_TABLE(o.items, '$[*]' COLUMNS (
rn FOR ORDINALITY, # 数组序号，从 1 开始
sku varchar(32) PATH '$.sku',
qty int PATH '$.qty',
has_price int EXISTS PATH '$.price' # 判断元素里是否含 price 键
)) AS t
WHERE o.id = 100;
SELECT t.sku, SUM(t.qty) AS total_qty
FROM orders o,
JSON_TABLE(o.items, '$[*]' COLUMNS (
sku varchar(32) PATH '$.sku',
qty int PATH '$.qty'
)) AS t
GROUP BY t.sku
ORDER BY total_qty DESC;PostgreSQL 等价写法
PostgreSQL 等价写法
-- 用 LATERAL + jsonb_array_elements 展开数组
SELECT o.id, item->>'sku' AS sku, (item->>'qty')::int AS qty
FROM orders o,
LATERAL jsonb_array_elements(o.items) AS item
WHERE o.id = 100;
-- 键固定时用强类型映射，写法更接近 JSON_TABLE
SELECT o.id, r.sku, r.qty
FROM orders o,
LATERAL jsonb_to_recordset(o.items) AS r(sku text, qty int)
WHERE o.id = 100;
-- 带行号展开
SELECT o.id, item, ord
FROM orders o, LATERAL jsonb_array_elements(o.items) WITH ORDINALITY AS t(item, ord);等价写法对照
| 目标 | MySQL 8.0 | PostgreSQL |
|---|---|---|
| 展开数组 | JSON_TABLE(items, '$[*]' COLUMNS(…)) | LATERAL jsonb_array_elements |
| 带行号 | FOR ORDINALITY | WITH ORDINALITY |
| 嵌套数组 | NESTED PATH 逐层展开 | 多层 LATERAL 嵌套 |
| 键存在判断 | EXISTS PATH | jsonb_path_exists 或 @? |
| 强类型映射 | COLUMNS 里声明类型 | jsonb_to_recordset 指定列类型 |
COLUMNS 里的类型声明决定输出类型，路径取不到值时该列为 NULL 而不报错；需要区分「键不存在」与「值为空」时用 EXISTS PATH，比事后判断 IS NULL 更明确。
展开是行数放大器：数组里有 100 个元素，一行明细就会变成 100 行。统计「订单数」时如果直接 COUNT(*)，得到的其实是商品明细条数，需要去重或先取订单主键。
大数组展开有实打实的性能代价：需要先解析整段 JSON 再生成行，配合 ORDER BY 或 GROUP BY 还可能落临时表；高频接口里不要用它替代正常的明细表设计。