jsonb_array_elements 是什么
jsonb_array_elements(jsonb) 是 PostgreSQL 的集合返回函数：把 JSON 数组展开成多行，每行是一个元素（类型仍是 jsonb），写在 FROM 里并配合 LATERAL 语义就能逐行关联主表。另有 jsonb_array_elements_text 直接返回 text，省掉 ->> 转换；加 WITH ORDINALITY 还能附带元素序号。它是 PostgreSQL 展开 JSON 数组的标准手段，对应 MySQL 的 JSON_TABLE。
基本信息
| 适用平台 | PostgreSQL 9.4+ |
|---|---|
| jsonb_array_elements | 返回 setof jsonb，元素继续保持 JSON 类型 |
| jsonb_array_elements_text | 返回 setof text，可直接比较与拼接 |
| WITH ORDINALITY | 追加序号列，从 1 开始，可用于保留数组顺序 |
| 参数不是数组 | 报错 cannot extract elements from a non-array |
| 与 MySQL 对应 | JSON_TABLE |
典型使用场景
- 展开订单商品数组，按商品维度统计销量
- 展开标签数组做关联查询与反向筛选
- 展开埋点参数做维度聚合
- 统计数组元素数量、按位置取特定元素
- 与主表连接取维度名称等补充信息
- 统计元素为空的记录：先算数组长度，再找出长度为 0 的行
基础展开
基础展开
-- 每行一个元素
SELECT value FROM jsonb_array_elements('[{"sku":"A1"},{"sku":"B2"}]'::jsonb) AS value;
-- 展开并带序号
SELECT elem, ord
FROM jsonb_array_elements('[10,20,30]'::jsonb) WITH ORDINALITY AS t(elem, ord);
-- 直接取 text，用于比较与字符串聚合
SELECT value FROM jsonb_array_elements_text('["vip","new"]'::jsonb) AS value;与主表逐行关联
与主表逐行关联
-- FROM 中的函数隐含 LATERAL 语义，可以直接引用左侧表的列
SELECT o.id, item->>'sku' AS sku, (item->>'qty')::int AS qty
FROM orders o,
jsonb_array_elements(o.items) AS item
WHERE o.id = 100;
-- 展开后聚合，统计每个 sku 的总数量
SELECT item->>'sku' AS sku, sum((item->>'qty')::int) AS total_qty
FROM orders o,
jsonb_array_elements(o.items) AS item
GROUP BY 1
ORDER BY total_qty DESC;函数与用途对照
| 写法 | 返回 | 典型用途 |
|---|---|---|
| jsonb_array_elements | setof jsonb | 还要继续用 JSON 操作符取值 |
| jsonb_array_elements_text | setof text | 直接比较、拼接与字符串聚合 |
| WITH ORDINALITY | 追加序号列 | 保留数组顺序、定位元素位置 |
| LATERAL 关联 | 每行展开一次 | 与主表明细一起输出 |
| jsonb_array_length | 整数 | 只想知道元素个数，不必展开 |
只想判断数组长度时用 jsonb_array_length 即可，不必展开；展开的动作会按元素个数放大行数，是后续聚合口径出错的主要来源。
列里的值不是数组时会直接报 cannot extract elements from a non-array：线上语句应先用 jsonb_typeof 判断类型分支，再决定是否展开，避免个别脏数据中断整个查询。
展开后的行没有隐含顺序：需要保持数组原有次序时必须加 WITH ORDINALITY 并在外层按序号排序，否则输出顺序取决于执行计划，多次查询可能不同。