PHP小码农

Appearance

JSONB 聚合与转换 ​

前面几节都在处理「一行之内的 JSON」。本节换个方向，讲清两个互补的搬运过程：把多行数据聚合成一个 JSON 值（行 → JSON），以及把一个 JSON 值展开成多行（JSON → 行）。前者用来构造分层的 API 响应与审计快照，后者用来把 JSON 数组接进关系运算。中间还夹着一大类结构转换函数——jsonb_build_object、to_jsonb、row_to_json、jsonb_populate_record，它们的共同点是在 JSON 与 SQL 类型之间来回倒数据。

前置知识

阅读本节前，建议先了解：JSONB 的查询操作

基础概念 ​

先建立一张方向图，把本节所有函数归位：

方向手段输入输出典型用途
行 → JSONjsonb_agg、jsonb_object_agg多行单个 jsonb构造 API 响应、快照归档
值 → JSONjsonb_build_object、jsonb_build_array、to_jsonb、row_to_json单个/一行值单个 jsonb手工拼装结构
JSON → 行jsonb_array_elements、jsonb_each单个 jsonb多行展开数组参与过滤与聚合
JSON → 列jsonb_to_record、jsonb_populate_record单个 jsonb强类型行把 JSON 还原成表结构

聚合函数是本节的主角，先把 JSONB 相关的全部列出来：

函数返回NULL 处理引入版本
jsonb_agg(anyelement)jsonb保留 NULL（转成 JSON null）9.4
jsonb_agg_strict(anyelement)jsonb跳过 NULL16
json_object_agg(key, value)jsonbvalue 可为 null；key 不能为 null9.4
jsonb_object_agg_strict(key, value)jsonbvalue 为 null 时跳过该条目16
jsonb_object_agg_unique(key, value)jsonbvalue 可为 null；重复键报错16
jsonb_object_agg_unique_strict(key, value)jsonb跳过 null 值 + 重复键报错16
jsonb_arrayagg(value_expression)jsonb由 NULL/ABSENT ON NULL 决定16

关键术语：

  • 聚合（Aggregate）：把多行输入折叠成一个输出值。jsonb_agg 是「折叠成 JSON 数组」，jsonb_object_agg 是「折叠成 JSON 对象」。
  • 严格变体（_strict）：PostgreSQL 16 为 JSON 聚合新增的一族函数，语义是「跳过 NULL 输入」，用来替代过去手写 FILTER (WHERE x IS NOT NULL) 的写法。
  • 展开（Unnest / Expand）：集合返回函数（Set-Returning Function，SRF），把 JSON 数组/对象的元素摊成多行。
  • WITH ORDINALITY：给集合返回函数附加一个从 1 开始的行号列，用来保留数组顺序。
  • 关系记录（Record）：jsonb_to_record / jsonb_populate_record 把 JSON 对象映射成具有明确列类型的行。

语法与用法 ​

text
-- 1. 聚合：行 → JSON
jsonb_agg(expression [ORDER BY sort_columns])                        → jsonb
jsonb_agg_strict(expression)                                         → jsonb      -- PG16+
jsonb_object_agg(key, value [ORDER BY sort_columns])                 → jsonb
jsonb_object_agg_strict(key, value)                                  → jsonb      -- PG16+
jsonb_object_agg_unique(key, value)                                  → jsonb      -- PG16+
jsonb_object_agg_unique_strict(key, value)                           → jsonb      -- PG16+

-- 2. 构造：值 → JSON
jsonb_build_object(VARIADIC "any")                                   → jsonb
jsonb_build_array(VARIADIC "any")                                    → jsonb
to_jsonb(anyelement)                                                 → jsonb
row_to_json(record [, boolean])                                      → json

-- 3. 展开：JSON → 行
jsonb_array_elements(jsonb)                                          → setof jsonb
jsonb_array_elements_text(jsonb)                                     → setof text
jsonb_each(jsonb)                                                    → setof (key text, value jsonb)
jsonb_each_text(jsonb)                                               → setof (key text, value text)

-- 4. 还原：JSON → 强类型列
jsonb_to_record(jsonb)                  AS x(col_a type, col_b type)  → record
jsonb_to_recordset(jsonb)               AS x(col_a type, col_b type)  → setof record
jsonb_populate_record(base anyelement, from_json jsonb)              → anyelement
jsonb_populate_recordset(base anyelement, from_json jsonb)           → setof anyelement

先看最小对照，理解 jsonb_agg 与 jsonb_agg_strict 的差别：

sql
SELECT
  jsonb_agg(v)         AS keep_nulls,     -- 保留 NULL
  jsonb_agg_strict(v)  AS skip_nulls      -- 跳过 NULL（PG16+）
FROM (VALUES (1), (NULL), (3)) AS t(v);
text
 keep_nulls | skip_nulls
------------+------------
 [1, null, 3] | [1, 3]
(1 row)

一行之差，语义完全不同。很多「数组里莫名多了 null」的线上问题都源于默认保留 NULL 的这条规则。

聚合内的 ORDER BY 决定元素顺序：

sql
SELECT jsonb_agg(v ORDER BY v DESC) AS sorted
FROM (VALUES (1), (3), (2)) AS t(v);
text
  sorted
-----------
 [3, 2, 1]
(1 row)

不写 ORDER BY 时顺序是没有保证的。聚合的输入顺序取决于扫描与并行计划，同一份数据在不同执行计划下可能给出不同顺序的数组。

详细说明 ​

jsonb_agg：把多行叠成 JSON 数组 ​

最直接的用途是「一个主键对应多条明细」的场景。假设 orders 表存订单头，order_items 是明细行，要一次性把明细打包：

sql
SELECT
  o.order_no,
  jsonb_agg(jsonb_build_object('sku', i.sku, 'qty', i.qty)) AS items
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.order_no
ORDER BY o.order_no;

聚合函数必须配合 GROUP BY：GROUP BY 决定「按什么分组折叠」，jsonb_agg 决定「折叠成什么形态」。

聚合内的 ORDER BY 与语句末尾的 ORDER BY 是两回事，容易写混：

位置作用对象是否影响数组内部顺序
聚合参数内 jsonb_agg(x ORDER BY y)聚合的输入是
语句末尾 ORDER BY col结果行的顺序否
sql
SELECT
  o.order_no,
  jsonb_agg(i.sku ORDER BY i.line_no) AS skus_ordered,   -- 数组内部有序
  jsonb_agg(i.sku)                    AS skus_raw        -- 数组内部无序
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.order_no
ORDER BY o.order_no;                                     -- 结果行有序

聚合还可以配合 FILTER 做条件收集，比 CASE WHEN 更直观：

sql
SELECT
  o.order_no,
  jsonb_agg(i.sku) FILTER (WHERE i.qty >= 2) AS bulk_skus,   -- 只要大量的
  count(i.sku)                               AS all_lines
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.order_no
ORDER BY o.order_no;

DISTINCT 同样可用：

sql
SELECT jsonb_agg(DISTINCT p.category) AS categories
FROM products p;

jsonb_agg 不能嵌套，jsonb_agg(jsonb_agg(...)) 会直接报错。要生成多层嵌套数组，只能在子查询里先聚合一层，外层再聚合一层。

jsonb_object_agg：把多行叠成 JSON 对象 ​

jsonb_object_agg(key, value) 需要两列：键列与值列。键会被强制转成 text，键不能为 NULL，值可以为 NULL。

sql
SELECT jsonb_object_agg(k, v) AS settings
FROM (VALUES ('theme', 'dark'), ('page_size', 20), ('beta', true)) AS t(k, v);
text
                               settings
--------------------------------------------------------------------------
 {"beta": true, "theme": "dark", "page_size": 20}
(1 row)

「键值对表」转「配置对象」是这个函数最经典的用法，也是 EAV 模型的出口形态。

四个变体的边界行为值得对比记忆：

场景jsonb_object_agg_strict_unique_unique_strict
value 为 NULL保留（JSON null）跳过该条目保留跳过该条目
重复 key不报错（结果不可依赖）不报错报错报错
key 为 NULL报错报错报错报错
sql
-- 默认变体：value 为 null 时条目仍在
SELECT jsonb_object_agg(k, v) AS with_null
FROM (VALUES ('a', 'x'), ('b', NULL)) AS t(k, v);
text
   with_null
----------------
 {"a": "x", "b": null}
(1 row)
sql
-- PG16+：_strict 直接跳过 value 为 null 的条目
SELECT jsonb_object_agg_strict(k, v) AS strict_result
FROM (VALUES ('a', 'x'), ('b', NULL)) AS t(k, v);
text
 strict_result
---------------
 {"a": "x"}
(1 row)

重复键是另一个高频陷阱。默认变体不报错，但哪一个值最终胜出取决于输入顺序，而输入顺序又取决于执行计划：

sql
-- 危险：重复键时结果不可依赖
SELECT jsonb_object_agg(k, v) AS dup_default
FROM (VALUES ('a', 1), ('a', 2)) AS t(k, v);
text
 dup_default
-------------
 {"a": 2}
(1 row)
sql
-- PG16+：要「发现脏数据」就用 _unique，遇到重复键直接报错
SELECT jsonb_object_agg_unique(k, v)
FROM (VALUES ('a', 1), ('a', 2)) AS t(k, v);
text
ERROR:  duplicate JSON object key value: "a"

生产环境里，键的唯一性应该由 GROUP BY 的粒度或上游数据质量保证；_unique 变体的价值在于把静默的数据问题转成显式报错。

SQL/JSON 标准聚合：json_arrayagg 与 json_objectagg ​

PostgreSQL 16 引入了 SQL/JSON 标准语法的 json_arrayagg / json_objectagg，它们比 jsonb_agg 系列更啰嗦，但表达能力更强。

json_arrayagg 支持 ABSENT ON NULL 直接跳过 NULL，取代 _strict 变体：

sql
-- PG16+
SELECT json_arrayagg(v ABSENT ON NULL) AS no_nulls
FROM (VALUES (1), (NULL), (3)) AS t(v);
text
 no_nulls
----------
 [1, 3]
(1 row)

json_objectagg 用 KEY ... VALUE ... 或 KEY : VALUE 的写法，并能声明唯一键约束：

sql
-- PG16+
SELECT json_objectagg(k VALUE v WITH UNIQUE KEYS) AS obj
FROM (VALUES ('a', 1), ('b', 2)) AS t(k, v);
text
        obj
--------------------
 {"a": 1, "b": 2}
(1 row)

两者的对照关系：

需求传统写法SQL/JSON 写法（PG16+）
跳过 NULLjsonb_agg_strict(v)json_arrayagg(v ABSENT ON NULL)
重复键报错jsonb_object_agg_unique(k, v)json_objectagg(k VALUE v WITH UNIQUE KEYS)
排序jsonb_agg(v ORDER BY s)json_arrayagg(v ORDER BY s)
返回 json 而非 jsonbjson_agg(v)json_arrayagg(v RETURNING json)

日常项目用 jsonb_agg 系列更简洁；需要贴合 SQL 标准、或要让 ABSENT ON NULL 语义显式写在语句里时，用 json_arrayagg。

展开：JSON → 行的进阶用法 ​

展开函数在上一节已介绍过基本形态，这里补充聚合场景下的三个要点。

要点一：WITH ORDINALITY 还原数组顺序。jsonb 不保存数组元素的来源信息，展开后如果还要按原顺序拼回去，必须靠行号：

sql
SELECT
  e.value ->> 'sku'      AS sku,
  e.ordinality           AS line_no
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items)
     WITH ORDINALITY AS e(value, ordinality)
WHERE o.order_no = 'SO-2026-0001'
ORDER BY e.ordinality;
text
   sku   | line_no
---------+---------
 A-1001  |       1
 B-2002  |       2
(2 rows)

要点二：空数组会吃掉整行，用 LEFT JOIN LATERAL 兜底：

sql
SELECT o.order_no, e.value ->> 'sku' AS sku
FROM orders o
LEFT JOIN LATERAL jsonb_array_elements(o.items) AS e ON true
ORDER BY o.order_no, sku;

要点三：展开再聚合可以完成「数组内部的重算」。这是聚合与展开配合最典型的形态——先摊开算，再收拢回去：

sql
WITH expanded AS (
  SELECT
    o.id,
    o.order_no,
    e.value ->> 'sku'             AS sku,
    (e.value ->> 'qty')::int      AS qty,
    (e.value ->> 'price')::numeric AS price
  FROM orders o
  CROSS JOIN LATERAL jsonb_array_elements(o.items) AS e
)
SELECT
  order_no,
  jsonb_agg(jsonb_build_object('sku', sku, 'amount', qty * price) ORDER BY sku) AS lines
FROM expanded
GROUP BY id, order_no
ORDER BY order_no;
text
                              lines
------------------------------------------------------------------
 [{"sku": "A-1001", "amount": 398.00}, {"sku": "B-2002", "amount": 200.00}]
(1 row)

JSON 里存的是「数量 + 单价」，输出要的是「金额」，这种「结构变形」只能靠展开—计算—重聚合三步完成，jsonb_set 做不到。

行与 JSON 的互转：构造与还原 ​

构造方向最常用的两个函数是 to_jsonb 与 row_to_json，它们的差别只在返回类型：

函数返回说明
to_jsonb(anyelement)jsonb任意类型的值转 jsonb，可与 jsonb 运算混用
row_to_json(record [, pretty_bool])json整行转 json，保留列顺序，可选择格式化输出
jsonb_build_object(k1, v1, k2, v2, ...)jsonb手工指定键名与值，键名是标量不能为 NULL
jsonb_build_array(v1, v2, ...)jsonb手工构造数组，参数个数可变
sql
SELECT
  to_jsonb('hello'::text)          AS text_val,     -- "hello"
  to_jsonb(42)                     AS int_val,      -- 42
  to_jsonb(true)                   AS bool_val,     -- true
  to_jsonb(NULL::text) IS NULL     AS null_is_sql_null;
text
 text_val | int_val | bool_val | null_is_sql_null
----------+---------+----------+------------------
 "hello"  | 42      | true     | t
(1 row)

最后一行是最容易踩的坑：to_jsonb(NULL) 返回的是 SQL NULL，而不是 JSON null。这个差异在 jsonb_set 场景下会直接把整列写成 NULL（见上一节「注意事项」）。到了聚合场景，它表现为两种后果：jsonb_agg(NULL) 会把这行记成 JSON null（数组里多出一个 null），而 jsonb_object_agg 的 key 为 SQL NULL 则直接报错：

sql
-- 错误！jsonb_object_agg 的键不能为 NULL
SELECT jsonb_object_agg(k, v)
FROM (VALUES (NULL, 1)) AS t(k, v);
text
ERROR:  null value not allowed for object key

row_to_json 适合把整行快照下来，字段名直接来自列名：

sql
SELECT row_to_json(u) AS snapshot
FROM users u
WHERE u.id = 1;
text
                                    snapshot
---------------------------------------------------------------------------------
 {"id": 1, "username": "alice", "email": "alice@example.com", "created_at": "2026-01-05T10:00:00+08:00"}
(1 row)

jsonb_build_object 则用来精确控制键名与输出字段，是构造 API 响应的主力：

sql
SELECT jsonb_build_object(
         'id',       u.id,
         'name',     u.username,
         'contact',  jsonb_build_object('email', u.email)
       ) AS payload
FROM users u
WHERE u.id = 1;
text
                                          payload
--------------------------------------------------------------------------------------------
 {"id": 1, "name": "alice", "contact": {"email": "alice@example.com"}}
(1 row)

反方向用 jsonb_to_record / jsonb_populate_record 把 JSON 还原成强类型列。区别在于「列名从哪来」：

函数列定义来源适用场景
jsonb_to_record(j) AS x(a int, b text)语句里当场声明临时、一次性解析
jsonb_populate_record(base, j)已有类型/表定义复用类型，多一个「默认值补齐」能力
sql
SELECT *
FROM jsonb_to_record('{"sku": "A-1001", "qty": 2, "price": 199.00}'::jsonb)
     AS x(sku text, qty int, price numeric(12, 2));
text
   sku   | qty | price
---------+-----+--------
 A-1001  |   2 | 199.00
(1 row)

jsonb_to_recordset 是它作用于数组的版本，等价于「先 jsonb_array_elements 再 jsonb_to_record」，一行搞定：

sql
SELECT *
FROM jsonb_to_recordset(
       '[{"sku": "A-1001", "qty": 2}, {"sku": "B-2002", "qty": 1}]'::jsonb
     ) AS x(sku text, qty int);
text
   sku   | qty
---------+-----
 A-1001  |   2
 B-2002  |   1
(2 rows)

列声明里缺失的键会得到 SQL NULL，多余的键被静默丢弃，不报错。这让它非常适合「宽容解析」，但也意味着拼错列名不会立刻暴露。

jsonb_populate_record 多出的能力是「以已有行/类型为模板补齐默认值」：

sql
-- 用 users 表的行类型作为模板，缺失字段自动取模板值
SELECT (jsonb_populate_record(u, '{"username": "bob"}'::jsonb)).*
FROM users u
WHERE u.id = 1;
text
 id | username |        email         |     created_at
----+----------+----------------------+---------------------
  1 | bob      | alice@example.com    | 2026-01-05 10:00:00+08
(1 row)

username 被 JSON 里的值覆盖，其余字段沿用模板行。这个特性在「部分更新」与「导入时给默认值」两个场景下都很顺手。

json 与 jsonb 的互转 ​

两个类型之间的转换是显式 :: 转换，看起来简单，但语义差异必须记住：

sql
SELECT
  '{"b": 1, "a": 2}'::jsonb               AS normalized,      -- 键被重排
  ('{"b": 1, "a": 2}'::jsonb)::json::text  AS back_to_json;    -- 已规范化
text
      normalized       |     back_to_json
-----------------------+----------------------
 {"a": 2, "b": 1}      | {"a": 2, "b": 1}
(1 row)

两个方向的代价完全不同：

转换代价说明
json → jsonb需要解析 + 去重 + 排序一次性开销，转换后就能用索引与包含判断
jsonb → json几乎零成本只是换个标识，内部文本已规范

聚合函数也有 json 与 jsonb 两套（json_agg / jsonb_agg）。优先用 jsonb 版本：json 版本保留原始文本与键顺序，无法索引、无法比较、去重时按文本次序判断，几乎总是错的。

sql
-- 不推荐：json_agg 保留键顺序与重复键，无法用于包含判断
SELECT json_agg(v) FROM (VALUES (1), (2)) AS t(v);

-- 推荐：jsonb_agg 输出规范化、可比较、可索引
SELECT jsonb_agg(v) FROM (VALUES (1), (2)) AS t(v);

实战示例 ​

下面用 users、orders 两张表完整走一遍「构造分层 API 响应」的流程：先聚合单层明细，再嵌套成多层结构，最后验证聚合的执行计划。

第一步：建表

sql
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username   varchar(64) NOT NULL UNIQUE,
  email      varchar(128),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint      NOT NULL REFERENCES users (id),
  order_no   varchar(32) NOT NULL UNIQUE,
  status     varchar(16) NOT NULL DEFAULT 'pending',
  items      jsonb       NOT NULL DEFAULT '[]'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),

  -- 守护结构：items 必须是数组
  CONSTRAINT ck_orders_items_is_array CHECK (jsonb_typeof(items) = 'array')
);
text
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE

第二步：写入数据

sql
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob',   'bob@example.com');

INSERT INTO orders (user_id, order_no, status, items) VALUES
(1, 'SO-2026-0001', 'paid',    '[{"sku": "A-1001", "qty": 2, "price": 199.00},
                                  {"sku": "B-2002", "qty": 1, "price": 200.00}]'),
(1, 'SO-2026-0002', 'pending', '[{"sku": "C-3003", "qty": 3, "price": 59.90}]'),
(2, 'SO-2026-0003', 'paid',    '[{"sku": "A-1001", "qty": 1, "price": 199.00},
                                  {"sku": "D-4004", "qty": 2, "price": 12.50}]');
text
INSERT 0 2
INSERT 0 3

第三步：单层聚合——把 items 数组里的商品名取出来

sql
SELECT
  o.order_no,
  jsonb_agg(item.value ->> 'sku' ORDER BY item.ordinality) AS skus,
  count(*)                                               AS line_count
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items)
     WITH ORDINALITY AS item(value, ordinality)
GROUP BY o.id, o.order_no
ORDER BY o.order_no;
text
    order_no    |            skus             | line_count
----------------+-----------------------------+------------
 SO-2026-0001  | ["A-1001", "B-2002"]        |          2
 SO-2026-0002  | ["C-3003"]                  |          1
 SO-2026-0003  | ["A-1001", "D-4004"]        |          2
(3 rows)

第四步：展开重算——把「数量 × 单价」变成「金额」

sql
SELECT
  o.order_no,
  jsonb_agg(
    jsonb_build_object('sku', item.value ->> 'sku', 'amount', (item.value ->> 'qty')::int * (item.value ->> 'price')::numeric)
    ORDER BY item.ordinality
  )                                                        AS lines,
  sum((item.value ->> 'qty')::int * (item.value ->> 'price')::numeric) AS total
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items)
     WITH ORDINALITY AS item(value, ordinality)
GROUP BY o.id, o.order_no
ORDER BY o.order_no;
text
    order_no    |                                    lines                                     |  total
----------------+------------------------------------------------------------------------------+---------
 SO-2026-0001  | [{"sku": "A-1001", "amount": 398.00}, {"sku": "B-2002", "amount": 200.00}]   |  598.00
 SO-2026-0002  | [{"sku": "C-3003", "amount": 179.70}]                                        |  179.70
 SO-2026-0003  | [{"sku": "A-1001", "amount": 199.00}, {"sku": "D-4004", "amount": 25.00}]    |  224.00
(3 rows)

第五步：两层嵌套——构造「用户 → 订单 → 明细」的 API 响应

注意 jsonb_agg 不能嵌套，所以要在子查询里先聚出订单层，外层再聚用户层：

sql
WITH order_payload AS (
  SELECT
    o.user_id,
    o.order_no,
    o.status,
    o.created_at,
    jsonb_agg(
      jsonb_build_object(
        'sku',    item.value ->> 'sku',
        'qty',    (item.value ->> 'qty')::int,
        'amount', (item.value ->> 'qty')::int * (item.value ->> 'price')::numeric
      )
      ORDER BY item.ordinality
    ) AS lines
  FROM orders o
  CROSS JOIN LATERAL jsonb_array_elements(o.items)
       WITH ORDINALITY AS item(value, ordinality)
  GROUP BY o.id, o.user_id, o.order_no, o.status, o.created_at
)
SELECT
  jsonb_build_object(
    'id',     u.id,
    'name',   u.username,
    'orders', jsonb_agg(
                jsonb_build_object(
                  'order_no',   op.order_no,
                  'status',     op.status,
                  'created_at', op.created_at,
                  'lines',      op.lines
                )
                ORDER BY op.created_at
              ) FILTER (WHERE op.order_no IS NOT NULL)   -- 无订单时不留 null
  ) AS response
FROM users u
LEFT JOIN order_payload op ON op.user_id = u.id
GROUP BY u.id, u.username
ORDER BY u.id;
text
                                     response
-------------------------------------------------------------------------------------
 {"id": 1, "name": "alice", "orders": [{"order_no": "SO-2026-0001", "status": "paid",   "lines": [{"sku": "A-1001", "qty": 2, "amount": 398.00}, {"sku": "B-2002", "qty": 1, "amount": 200.00}]}, {"order_no": "SO-2026-0002", "status": "pending", "lines": [{"sku": "C-3003", "qty": 3, "amount": 179.70}]}]}
 {"id": 2, "name": "bob",   "orders": [{"order_no": "SO-2026-0003", "status": "paid",   "lines": [{"sku": "A-1001", "qty": 1, "amount": 199.00}, {"sku": "D-4004", "qty": 2, "amount": 25.00}]}]}
(2 rows)

三个关键点：LEFT JOIN 保证没有订单的用户也出现在结果里；FILTER (WHERE op.order_no IS NOT NULL) 把 LEFT JOIN 产生的那个 null 从数组里剔掉（否则会得到 [null] 或数组里混入一个 null）；ORDER BY 写在聚合参数里才能保证嵌套数组的内部顺序。

第六步：验证执行计划

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.user_id, jsonb_agg(item.value ->> 'sku' ORDER BY item.ordinality) AS skus
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items)
     WITH ORDINALITY AS item(value, ordinality)
GROUP BY o.user_id;
text
                                   QUERY PLAN
---------------------------------------------------------------------------------
 HashAggregate  (cost=52.30..57.80 rows=200 width=40) (actual time=0.152..0.171 rows=2 loops=1)
   Group Key: o.user_id
   Batches: 1  Memory Usage: 24kB
   Buffers: shared hit=12
   ->  Nested Loop  (cost=0.00..36.30 rows=800 width=68) (actual time=0.031..0.106 rows=5 loops=1)
         Buffers: shared hit=12
         ->  Seq Scan on orders o  (cost=0.00..11.30 rows=200 width=68) (actual time=0.014..0.022 rows=3 loops=1)
               Buffers: shared hit=4
         ->  Function Scan on jsonb_array_elements item  (cost=0.00..0.06 rows=4 width=0) (actual time=0.015..0.018 rows=2 loops=3)
 Planning Time: 0.312 ms
 Execution Time: 0.289 ms

两行要读：Function Scan on jsonb_array_elements item 说明数组确实被展开成了行，rows=2 loops=3 表示三张订单各展开出 2 行左右；HashAggregate ... Group Key: o.user_id 说明聚合是哈希方式完成的，Memory Usage 会随分组数与数组规模一起增长——分组非常多、数组非常长时它会溢出到磁盘（Batches 大于 1），这时要考虑先用 WHERE 缩小输入。

第七步：清理

sql
DROP TABLE orders;
DROP TABLE users;
text
DROP TABLE
DROP TABLE

注意事项 ​

  • 聚合函数在无输入行时返回 NULL，而不是空数组或空对象。这是 jsonb_agg 最容易踩的坑：

    sql
    SELECT jsonb_agg(v) AS empty_agg
FROM (VALUES (1)) AS t(v)
WHERE false;
    text
     empty_agg
-----------
(1 row)

    结果是一行、值为 SQL NULL。前端拿到 null 去 for 循环就会崩，所以一定要兜底：

    sql
    SELECT coalesce(jsonb_agg(v), '[]'::jsonb) AS safe_empty
FROM (VALUES (1)) AS t(v)
WHERE false;

  • jsonb_agg 默认保留 NULL，jsonb_agg_strict 才跳过。默认行为经常把 null 混进数组，前端要额外过滤。PostgreSQL 16+ 直接用 _strict 变体：

    sql
    -- PostgreSQL 16+
jsonb_agg_strict(v)
-- 16 之前的等价写法
jsonb_agg(v) FILTER (WHERE v IS NOT NULL)

  • jsonb_agg 不能嵌套。jsonb_agg(jsonb_agg(x)) 会报 "aggregate function calls cannot be nested"。要两层结构必须借子查询或 CTE，像实战示例第五步那样。

  • jsonb_object_agg 的键不能为 NULL，会直接报错。上游数据有脏值时先在子查询里过滤：

    sql
    WHERE k IS NOT NULL

  • jsonb_object_agg 遇到重复键不报错，但结果不可依赖。默认变体保留哪一个值取决于输入顺序，而输入顺序又受执行计划影响。要尽早暴露问题就用 _unique（PostgreSQL 16+）。

  • 聚合内的顺序必须显式声明。不写 ORDER BY 时元素顺序由计划决定，多次执行可能不同：

    sql
    -- 不可靠：顺序随机
jsonb_agg(i.sku)
-- 可靠：按 line_no 排序
jsonb_agg(i.sku ORDER BY i.line_no)

  • ORDER BY 写在聚合里才管数组内部顺序，写在语句末尾只管结果行顺序。两者作用域完全不同。

  • to_jsonb(NULL) 得到的是 SQL NULL，不是 JSON null。往 jsonb 列里写变量时务必兜底：

    sql
    SELECT to_jsonb(NULL::text) IS NULL AS is_sql_null;   -- t

  • jsonb_to_record 的多余键被静默丢弃、缺失的键静默变 NULL，列名拼错不会报错。生产代码里建议对解析结果加一次非空校验。

  • json_agg / json_object_agg 返回 json 而不是 jsonb，结果无法参与包含判断、无法比较、无法索引。除非确实要保留原始文本与键顺序，否则一律用 jsonb 版本。

  • LEFT JOIN LATERAL 配合聚合时会产生「幽灵 null 行」。空数组或没有匹配明细时，聚合参数为 NULL，会被 jsonb_agg 记成 JSON null，需要 FILTER (WHERE ... IS NOT NULL) 或空表判断剔除：

    sql
    jsonb_agg(x) FILTER (WHERE x IS NOT NULL)

  • 展开再聚合会放大中间行数。一条订单含 100 个明细，GROUP BY 前就会变成 100 行。大数组、多分组同时出现时，HashAggregate 的内存会迅速上涨（EXPLAIN 里 Batches 大于 1 即表示已溢出到磁盘），先用 WHERE 缩小范围比事后调参有效得多。

  • jsonb_populate_record 的第一个参数是模板行，不是列定义。传 NULL::users 会得到「全 NULL 的模板」，缺失字段不会拿到表的默认值，只有传真实行才有补齐效果。

最佳实践 ​

  • 聚合结果统一用 coalesce 兜底，把「无数据」的语义固定成空数组/空对象，别让 null 穿透到应用层：

    sql
    coalesce(jsonb_agg(x ORDER BY y), '[]'::jsonb)  AS items
coalesce(jsonb_object_agg(k, v), '{}'::jsonb)   AS attrs

  • 「先过滤再聚合」而不是「先聚合再过滤」。HAVING 在聚合之后、WHERE 在聚合之前，能用 WHERE 缩小的绝不要留到 HAVING：

    sql
    -- 推荐：先缩小输入
SELECT user_id, jsonb_agg(sku) FROM orders WHERE status = 'paid' GROUP BY user_id;

-- 不推荐：先聚全量再筛
SELECT user_id, jsonb_agg(sku) FROM orders GROUP BY user_id HAVING bool_and(status = 'paid');

  • 只在数组内部顺序有业务含义时才写聚合内 ORDER BY。排序本身有代价，可省则省；一旦写了，就要在注释里说明「这个顺序被谁依赖」，避免后人误删。

  • 用 jsonb_build_object 显式声明 API 字段名，而不是直接 to_jsonb(row) 暴露全部列。列名一改，接口就静默变了：

    sql
    jsonb_build_object('id', u.id, 'name', u.username)

  • 多层嵌套统一用 CTE 逐层构建，每层只负责一件事（算金额 / 聚明细 / 聚订单 / 聚用户），比写成一个巨大的嵌套表达式可读、可测。

  • 优先用 jsonb_agg_strict 与 _unique 变体（PostgreSQL 16+），把「跳过 NULL」和「键必须唯一」写在函数名里，比事后写 FILTER 或加约束更不易漏。

  • 聚合前用 jsonb_typeof 或检查约束确认结构，避免 jsonb_array_elements 在非数组数据上直接报错：

    sql
    CONSTRAINT ck_orders_items_is_array CHECK (jsonb_typeof(items) = 'array')

  • 需要「数组内部重算」时用「展开 → 计算 → 重聚合」三步式，别试图用 jsonb_set 逐元素修改：

    sql
    WITH expanded AS (SELECT ... FROM t CROSS JOIN LATERAL jsonb_array_elements(t.items) e)
SELECT jsonb_agg(jsonb_build_object('amount', qty * price)) FROM expanded;

  • jsonb_to_record 解析外部 JSON 时，列声明要与数据对齐并做非空校验。它太宽容，拼错列名不会报错，只会在下游表现为「字段莫名是空」。

  • 给聚合查询配 EXPLAIN (ANALYZE, BUFFERS) 做体检，重点看 HashAggregate 的 Memory Usage 与 Batches，以及展开函数是 Function Scan 还是被下推成了 Index Scan。

下一节

继续学习：JSONB 典型应用场景

参考链接 ​