把 JSON 数组拆成多行
同一条记录里的 JSON 数组要参与过滤和统计，就得先展开成多行：MySQL 8 用 JSON_TABLE 把 JSON 文档当表来查，PostgreSQL 用 jsonb_array_elements_text 这类集合返回函数配合 LATERAL 横向展开。展开后行数由数组长度决定，是处理去规范化 JSON 数据最常用的手法。
基本信息
| MySQL 写法 | FROM 表, JSON_TABLE(列, '$[*]' COLUMNS (… PATH '…')) AS 别名 |
|---|---|
| PostgreSQL 写法 | CROSS JOIN LATERAL jsonb_array_elements_text(列) AS t(值) |
| 结果行数 | 等于原行数乘以数组元素个数 |
| 索引 | 展开出来的值通常用不上普通索引 |
| 空数组 | 该行不产生任何结果行 |
典型使用场景
- 按 JSON 数组里的标签筛选订单或用户
- 把订单里的商品明细展开成行做统计
- 统计每个标签被多少条记录引用
- 把 JSON 日志展开成可查询的行集
- 替代应用层循环解析 JSON 再拼 SQL 的做法
MySQL 8 写法
MySQL 8 写法
-- 把 tags 数组展开成多行，再按标签过滤
SELECT o.id, jt.tag
FROM orders o,
JSON_TABLE(o.tags, '$[*]'
COLUMNS (tag VARCHAR(64) PATH '$')) AS jt
WHERE jt.tag = 'vip';
-- 统计每个标签覆盖的订单数（注意用 DISTINCT 还原订单口径）
SELECT jt.tag, COUNT(DISTINCT o.id) AS orders
FROM orders o,
JSON_TABLE(o.tags, '$[*]' COLUMNS (tag VARCHAR(64) PATH '$')) AS jt
GROUP BY jt.tag;PostgreSQL 写法
PostgreSQL 写法
-- 展开 jsonb 数组为行，再用 LATERAL 横向连接
SELECT o.id, t.tag
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements_text(o.tags) AS t(tag)
WHERE t.tag = 'vip';
-- 统计每个标签覆盖的订单数
SELECT t.tag, COUNT(DISTINCT o.id) AS orders
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements_text(o.tags) AS t(tag)
GROUP BY t.tag;两库写法对照
| 维度 | MySQL 8 | PostgreSQL |
|---|---|---|
| 展开方式 | JSON_TABLE | jsonb_array_elements_text |
| 连接写法 | 逗号连接（隐式 CROSS JOIN） | CROSS JOIN LATERAL |
| 取对象字段 | COLUMNS 里用 PATH 指定 | 用 ->> 取字段值 |
| 空数组 | 该行不产生结果行 | 同样不产生结果行 |
| 保留原行 | 需改写为 LEFT JOIN 形式 | 改用 LEFT JOIN LATERAL |
展开会改变聚合口径：展开前一行代表一张订单，展开后一行代表一个标签，COUNT(*) 的含义完全不同。统计订单数必须写成 COUNT(DISTINCT 订单号)。
JSON 列里的字段通常用不上普通索引：高频按标签查的场景，考虑拆成关联表，或在生成列上建索引来加速。
展开函数对空数组与 NULL 的处理要提前想清楚：两库遇到空数组都不产生任何行，业务若需要保留这些记录，就要先 COALESCE 成空数组再展开。
展开后的行数由数组长度决定：个别超长数组会把结果集明显撑大，做聚合前先看一眼长度分布，避免少数记录拖慢整条查询。