PHP小码农

Appearance

JSONB 构建函数（jsonb_build_object / jsonb_agg） ​

把关系数据"组装成 JSON"是后端最频繁的操作之一：一个订单要带出用户信息与订单明细，一个列表接口要把多行折叠成 JSON 数组。PostgreSQL 把这类需求压缩成了几个函数——jsonb_build_object 拼装单个对象、jsonb_build_array 拼装数组、jsonb_agg 把多行折叠成数组、jsonb_object_agg 把多行折叠成对象。本节从函数视角拆解它们的参数规则、类型转换与嵌套手法。

与「JSONB 聚合与转换」的分工

JSONB 聚合与转换 一章从数据流向出发，系统梳理"行 → JSON"与"JSON → 行"两方向的全部函数；本节聚焦构建方向（值 → JSON、行 → JSON），重点讲 jsonb_build_object 的可变参数与键规则、jsonb_agg 的排序与 NULL 语义、以及用 LATERAL + 嵌套聚合拼装分层 JSON 的工程手法。两节互补，本节不重复展开 jsonb_to_record / jsonb_populate_record 等反向函数。

前置知识

阅读本节前，建议先了解：ARRAY 构造函数与操作

基础概念 ​

构建 JSON 的函数只有两类源头：从当前行的若干值拼，和从多行折叠。记住这条分界，就知道该选哪个函数。

概念术语说明
对象构造Object Builderjsonb_build_object，键值对交替传入
数组构造Array Builderjsonb_build_array，参数列表直接成为数组
行折叠Row Aggregationjsonb_agg，多行聚合成一个 JSON 数组
对象聚合Object Aggregationjsonb_object_agg，多行的 key/value 聚合成对象
可变参数VARIADIC "any"构造函数的参数类型不限、个数不限
类型转换to_jsonb把任意 SQL 值转成 jsonb
记录转 JSONrow_to_json把一个复合类型/整行转成 json（非 jsonb）
嵌套聚合Nested Aggregation在子查询里先聚合，再拼进外层对象
去空字段Null Strippingjsonb_strip_nulls 递归删除 null 值字段

先看一张"来源 → 手段"对照表，这是选型的核心：

数据来源想输出的形态该用的函数
一行的几个列对象jsonb_build_object('k', v, ...)
一行的几个值数组jsonb_build_array(v1, v2, ...)
一行的整行对象to_jsonb(t) 或 row_to_json(t)
多行的某一列数组jsonb_agg(x)
多行的 key/value 两列对象jsonb_object_agg(k, v)
多行 + 每行是对象数组的对象jsonb_agg(jsonb_build_object(...))

最小示例：

sql
SELECT
  jsonb_build_object('id', 1, 'name', '甲')          AS obj,
  jsonb_build_array(1, 'a', true, NULL)              AS arr,
  jsonb_agg(x)                                       AS agg_arr
FROM (VALUES (1), (2), (3)) AS t(x);
text
              obj              |           arr            |   agg_arr
-------------------------------+--------------------------+-------------
 {"id": 1, "name": "甲"}       | [1, "a", true, null]     | [1, 2, 3]
(1 row)

注意 jsonb_build_array(1, 'a', true, NULL) 里的 NULL 变成了 JSON 的 null，而不是被丢弃——这是构建函数与聚合函数在 NULL 处理上的第一个分水岭。

语法与用法 ​

text
-- 值 → JSON
jsonb_build_object(VARIADIC "any")            → jsonb     -- 键值对交替
jsonb_build_array(VARIADIC "any")             → jsonb
to_jsonb(anyelement)                          → jsonb
row_to_json(record [, boolean])               → json      -- 注意返回 json
jsonb_strip_nulls(jsonb)                      → jsonb     -- 递归删 null 字段

-- 行 → JSON（聚合）
jsonb_agg(expression [ORDER BY ...])          → jsonb
jsonb_agg_strict(expression)                  → jsonb     -- PG16+，跳过 NULL
jsonb_object_agg(key, value [ORDER BY ...])   → jsonb
jsonb_object_agg_strict(key, value)           → jsonb     -- PG16+
jsonb_object_agg_unique(key, value)           → jsonb     -- PG16+，重复键报错

jsonb_build_object 的参数规则 ​

它是 VARIADIC "any"，因此参数个数不限、类型不限，但有两条硬规则：

  1. 参数个数必须是偶数，否则报错；
  2. 键必须是能转成 text 的非 NULL 值，键为 NULL 直接报错。
sql
-- 错误复现一：奇数个参数
SELECT jsonb_build_object('a');
text
ERROR:  argument list must have even number of elements
HINT:  The arguments of jsonb_build_object() must consist of alternating keys and values.
sql
-- 错误复现二：键为 NULL
SELECT jsonb_build_object(NULL, 1);
text
ERROR:  argument 1: key must not be null

键可以是任何类型，会被隐式转成 text；数字键会自动变成字符串键：

sql
SELECT
  jsonb_build_object('name', '甲', 'age', 30)   AS str_keys,
  jsonb_build_object(1, 'one', 2, 'two')        AS num_keys;
text
              str_keys              |       num_keys
------------------------------------+---------------------
 {"age": 30, "name": "甲"}          | {"1": "one", "2": "two"}
(1 row)

jsonb_build_array 与 to_jsonb 的区别 ​

两者都能产出 JSON，但适用对象不同：

维度jsonb_build_array(v1, v2)to_jsonb(v)
输入若干独立表达式一个值（可为整行）
输出数组该值的 JSON 表示
整行支持需逐个列出to_jsonb(t) 直接展开为对象
返回类型jsonbjsonb
sql
-- 整行转对象：to_jsonb 最省事
SELECT to_jsonb(t) AS row_as_obj
FROM (SELECT 1 AS id, '甲' AS name, ARRAY['a','b'] AS tags) AS t;
text
                    row_as_obj
--------------------------------------------------
 {"id": 1, "name": "甲", "tags": ["a", "b"]}
(1 row)

to_jsonb 会把一行里的每一列变成一个键，列名就是键名，嵌套的数组也会被正确转成 JSON 数组——这是拼装接口响应最快的一条路。

sql
-- 对比：手工 build_object 也能做，但要逐列书写
SELECT jsonb_build_object('id', t.id, 'name', t.name, 'tags', to_jsonb(t.tags)) AS manual
FROM (SELECT 1 AS id, '甲' AS name, ARRAY['a','b'] AS tags) AS t;

row_to_json 与 to_jsonb 的差别只在返回类型（json vs jsonb），通常应优先选 jsonb，因为它是规范化的、可比较、可索引。

jsonb_agg 的排序与 NULL 语义 ​

jsonb_agg 是聚合函数，接收多行。顺序不保证，必须显式 ORDER BY：

sql
-- 不保证顺序
SELECT jsonb_agg(name) FROM products;

-- 推荐：显式排序
SELECT jsonb_agg(name ORDER BY id) FROM products;

NULL 的处理是 jsonb_agg 最需要留意的地方：

函数输入为 NULL 时版本
jsonb_agg(x)输出 JSON null（保留位置）9.4+
jsonb_agg_strict(x)跳过该行16+
jsonb_agg(x) FILTER (WHERE x IS NOT NULL)跳过该行9.4+
sql
SELECT
  jsonb_agg(x)                                   AS keep_null,
  jsonb_agg(x) FILTER (WHERE x IS NOT NULL)      AS filter_null,
  jsonb_strip_nulls(jsonb_agg(x))                AS stripped
FROM (VALUES ('a'), (NULL), ('c')) AS t(x);
text
      keep_null      | filter_null |    stripped
---------------------+-------------+---------------
 ["a", null, "c"]    | ["a", "c"]  | ["a", null, "c"]
(1 row)

关键坑点：jsonb_strip_nulls 只删对象里的 null 字段，不删数组里的 null 元素。所以上面第三个结果是 ["a", null, "c"]，与 keep_null 完全一致。要去掉数组里的 null，只能用 FILTER（PG16 之前）或 jsonb_agg_strict（PG16+）。

sql
-- PG16+ 的更简洁写法
SELECT jsonb_agg_strict(x) AS strict_agg
FROM (VALUES ('a'), (NULL), ('c')) AS t(x);
text
 strict_agg
------------
 ["a", "c"]
(1 row)

jsonb_object_agg 与重复键 ​

jsonb_object_agg(k, v) 把两列折成一个对象。它的两条规则与 jsonb_build_object 一致：键不能为 NULL；但重复键不报错，后者覆盖前者：

sql
SELECT jsonb_object_agg(k, v) AS obj
FROM (VALUES ('a', 1), ('b', 2), ('a', 3)) AS t(k, v);
text
       obj
------------------
 {"a": 3, "b": 2}
(1 row)

上面的输出能看到 'a' 出现了两次，最终只保留最后一个值 3。这是静默的数据丢失，聚合前若不确定键唯一，应先确认或在 PG16+ 用 _unique 变体让重复键直接报错：

sql
-- PG16+：重复键直接抛错，把静默问题变成显式问题
SELECT jsonb_object_agg_unique(k, v)
FROM (VALUES ('a', 1), ('a', 3)) AS t(k, v);
text
ERROR:  duplicate JSON object key value

详细说明 ​

三种"整行转 JSON"的写法对比 ​

把一行变成 JSON 对象，有 to_jsonb、row_to_json、jsonb_build_object 三条路，选择依据是列是否需要筛选/改名/加工：

写法返回列筛选列改名计算列适用场景
to_jsonb(t)jsonb否（全列）否否快速导出整行
to_jsonb(t) + 子查询jsonb是是是推荐的通用做法
row_to_json(t)json否否否需要 json 类型时
jsonb_build_object(...)jsonb是是是列少、需精确控制

推荐范式：先用子查询把列选好、改名、算好，再 to_jsonb 一行包住。既能有 to_jsonb 的简洁，又能任意控制输出内容：

sql
SELECT to_jsonb(u) AS user_json
FROM (
  SELECT id, name, email, created_at::date AS reg_date
  FROM users
  WHERE id = 1
) AS u;
text
                                     user_json
---------------------------------------------------------------------------------
 {"id": 1, "name": "甲", "email": "a@b.com", "reg_date": "2026-01-01"}
(1 row)

这比手写 jsonb_build_object('id', id, 'name', name, ...) 更不容易漏改字段。

拼装数组：jsonb_build_array 与 jsonb_agg 的边界 ​

同一个"数组"需求，两个函数的适用面完全不同：

维度jsonb_build_array(a, b, c)jsonb_agg(x)
输入来源当前行的若干表达式多行的一列
元素个数写死（或 VARIADIC 传入数组）由行数决定
能否排序按书写顺序需 ORDER BY
NULL 元素保留保留（_strict 跳过）
典型用途固定的元组，如 [lat, lng]变长列表，如"该用户的所有订单号"

常见错误：想聚合多行却用了 jsonb_build_array，结果只拿到当前一行；或想拼固定元组却用了 jsonb_agg，被迫 GROUP BY。

sql
-- 固定元组：build_array
SELECT jsonb_build_array(lat, lng) AS coord FROM locations;

-- 变长列表：agg
SELECT jsonb_agg(order_no ORDER BY created_at) AS orders
FROM orders WHERE user_id = 1;

用 LATERAL + 嵌套聚合拼装分层 JSON ​

这是本节最实用的工程手法：主表一行对应从表多行时，用 LATERAL 子查询在每行内部做一次聚合，把从表折成数组塞进主对象。

sql
SELECT jsonb_build_object(
         'id',    u.id,
         'name',  u.name,
         'orders', o.orders
       ) AS user_with_orders
FROM users u
LEFT JOIN LATERAL (
  SELECT jsonb_agg(
           jsonb_build_object(
             'order_no', od.order_no,
             'amount',   od.amount,
             'items',    it.items
           ) ORDER BY od.created_at
         ) AS orders
  FROM orders od
  LEFT JOIN LATERAL (
    SELECT jsonb_agg(jsonb_build_object('sku', oi.sku, 'qty', oi.qty) ORDER BY oi.sku) AS items
    FROM order_items oi
    WHERE oi.order_id = od.id
  ) AS it ON true
  WHERE od.user_id = u.id
) AS o ON true;

得到的就是典型的三层嵌套结构：

text
{"id": 1, "name": "甲", "orders": [
  {"order_no": "SO-001", "amount": 299.00,
   "items": [{"sku": "A", "qty": 2}, {"sku": "B", "qty": 1}]}
]}

为什么必须用 LATERAL：内层子查询需要引用外层当前行的 u.id / od.id，普通 JOIN 子查询无法引用同级外层的列。LATERAL 允许右侧子查询引用左侧的列，实现"每行按需展开"。

为什么用 LEFT JOIN LATERAL ... ON true 而不是逗号连接：LEFT JOIN LATERAL 保证即使从表没有匹配行，主行也会保留，此时 o.orders 为 NULL。若要"没有订单就输出空数组"，需在 jsonb_agg 外套一层 coalesce：

sql
'orders', coalesce(o.orders, '[]'::jsonb)

嵌套聚合的两种写法：LATERAL 与"先聚合再连接" ​

LATERAL 不是唯一解。也可以先在各从表上聚合，再 JOIN 主表。两种写法的差别主要在可读性与执行计划：

写法可读性执行计划超多行时
LEFT JOIN LATERAL嵌套层次清晰每行执行一次聚合主表巨大时可能较慢
子查询先聚合再 JOIN平铺，较冗长各从表各扫一次通常更稳
sql
-- 写法二：先聚合，再连接
SELECT jsonb_build_object('id', u.id, 'name', u.name, 'orders', o.orders)
FROM users u
LEFT JOIN (
  SELECT user_id, jsonb_agg(order_no ORDER BY created_at) AS orders
  FROM orders
  GROUP BY user_id
) AS o ON o.user_id = u.id;

两种写法结果等价，写法二在从表数据量大时通常更容易被优化成一次扫描 + 哈希连接，写法一在层数多时更易读。实际项目里建议先写 LATERAL（可读），再用 EXPLAIN (ANALYZE, BUFFERS) 验证，必要时改写。

jsonb_strip_nulls 与"空对象"的区别 ​

jsonb_strip_nulls 递归删除对象中值为 null 的字段，但对数组元素无效；更要注意它会把嵌套对象里所有 null 字段一并删掉：

sql
SELECT
  jsonb_strip_nulls('{"a": 1, "b": null}'::jsonb)                 AS simple,   -- {"a": 1}
  jsonb_strip_nulls('{"a": {"x": null, "y": 2}}'::jsonb)          AS nested,   -- {"a": {"y": 2}}
  jsonb_strip_nulls('{"a": [1, null, 2]}'::jsonb)                 AS in_array; -- {"a": [1, null, 2]}
text
     simple     |        nested         |      in_array
----------------+-----------------------+--------------------
 {"a": 1}       | {"a": {"y": 2}}       | {"a": [1, null, 2]}
(1 row)

如果业务上"值为 null 就等于该字段不存在"，用它是合理的；但如果 null 本身有语义（比如"显式置空"），则不应删除。

构建函数的性能特征 ​

构建 JSON 是 CPU 密集型操作，代价主要来自三处：

环节成本来源规避手段
类型转换每个值都要转成 jsonb 内部表示减少不必要的列
字符串键每次都要建 key 的文本键多时考虑 to_jsonb(子查询)
排序jsonb_agg ... ORDER BY 需额外排序若顺序无所谓则省略 ORDER BY
sql
-- 只选需要的列，而不是 to_jsonb(整行)
SELECT to_jsonb(t) FROM (SELECT id, name FROM users) AS t;

-- 不推荐：把大字段（如 TEXT 正文）也带进 JSON
SELECT to_jsonb(u) FROM users u;

与 jsonb_agg 配合的类型转换 ​

jsonb_agg 接管任意类型，numeric 会以数字形式输出，date/timestamp 会以 ISO 字符串输出，text 带引号——这符合直觉，但要留意 numeric 的输出格式：

sql
SELECT jsonb_agg(v ORDER BY v) AS nu
FROM (VALUES (1.50::numeric), (2.00::numeric)) AS t(v);
text
      nu
---------------
 [1.50, 2.00]
(1 row)

numeric 会保留声明的小数位（因为 numeric 自带 scale），而 float8 会输出最短表示。做金额字段时，用 numeric 拼 JSON 能保持精度一致。

实战示例 ​

场景：为电商后台构造订单详情接口的 JSON，包含用户、订单、订单明细三层。

第 1 步：建表

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

CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id),
  order_no   TEXT NOT NULL UNIQUE,
  amount     NUMERIC(10,2) NOT NULL,
  status     TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id),
  sku      TEXT NOT NULL,
  qty      INTEGER NOT NULL CHECK (qty > 0),
  price    NUMERIC(10,2) NOT NULL
);

第 2 步：插入数据

sql
INSERT INTO users (name, email) VALUES
  ('甲', 'a@example.com'),
  ('乙', 'b@example.com'),
  ('丙', 'c@example.com');   -- 无订单，用于验证空数组

INSERT INTO orders (user_id, order_no, amount, status, created_at) VALUES
  (1, 'SO-001', 299.00, 'paid',    '2026-01-01 10:00:00+08'),
  (1, 'SO-002', 158.00, 'pending', '2026-01-02 11:00:00+08'),
  (2, 'SO-003', 499.00, 'paid',    '2026-01-03 09:00:00+08');

INSERT INTO order_items (order_id, sku, qty, price) VALUES
  (1, 'SKU-A', 2, 99.00),
  (1, 'SKU-B', 1, 101.00),
  (2, 'SKU-C', 1, 158.00),
  (3, 'SKU-D', 1, 499.00);

第 3 步：单层聚合——把明细折成数组

sql
SELECT o.order_no,
       jsonb_agg(jsonb_build_object('sku', i.sku, 'qty', i.qty, 'price', i.price)
                 ORDER BY i.sku) 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;
text
 order_no |                                              items
----------+--------------------------------------------------------------------------------------------------
 SO-001   | [{"qty": 2, "sku": "SKU-A", "price": 99.00}, {"qty": 1, "sku": "SKU-B", "price": 101.00}]
 SO-002   | [{"qty": 1, "sku": "SKU-C", "price": 158.00}]
 SO-003   | [{"qty": 1, "sku": "SKU-D", "price": 499.00}]
(3 rows)

第 4 步：两层嵌套——订单对象内含明细数组

sql
SELECT jsonb_build_object(
         'order_no',   o.order_no,
         'amount',     o.amount,
         'status',     o.status,
         'created_at', to_jsonb(o.created_at),
         'items',      i.items
       ) AS order_json
FROM orders o
LEFT JOIN LATERAL (
  SELECT jsonb_agg(jsonb_build_object('sku', oi.sku, 'qty', oi.qty, 'price', oi.price)
                   ORDER BY oi.sku) AS items
  FROM order_items oi
  WHERE oi.order_id = o.id
) AS i ON true
WHERE o.order_no = 'SO-001';
text
                                          order_json
-----------------------------------------------------------------------------------------------
 {"items": [{"qty": 2, "sku": "SKU-A", "price": 99.00}, {"qty": 1, "sku": "SKU-B", "price": 101.00}],
  "amount": 299.00, "status": "paid", "order_no": "SO-001", "created_at": "2026-01-01T02:00:00+00:00"}
(1 row)

第 5 步：三层嵌套——用户 → 订单数组 → 明细数组

sql
SELECT jsonb_build_object(
         'id',     u.id,
         'name',   u.name,
         'email',  u.email,
         'orders', coalesce(o.orders, '[]'::jsonb)
       ) AS user_json
FROM users u
LEFT JOIN LATERAL (
  SELECT jsonb_agg(
           jsonb_build_object(
             'order_no', od.order_no,
             'amount',   od.amount,
             'status',   od.status,
             'items',    coalesce(it.items, '[]'::jsonb)
           ) ORDER BY od.created_at
         ) AS orders
  FROM orders od
  LEFT JOIN LATERAL (
    SELECT jsonb_agg(jsonb_build_object('sku', oi.sku, 'qty', oi.qty) ORDER BY oi.sku) AS items
    FROM order_items oi
    WHERE oi.order_id = od.id
  ) AS it ON true
  WHERE od.user_id = u.id
) AS o ON true
ORDER BY u.id;
text
 id | name |        orders
----+------+--------------------------------------------------------------
  1 | 甲   | [{"items": [...], "amount": 299.00, "status": "paid", "order_no": "SO-001"},
          {"items": [...], "amount": 158.00, "status": "pending", "order_no": "SO-002"}]
  2 | 乙   | [{"items": [{"qty": 1, "sku": "SKU-D"}], "amount": 499.00, "status": "paid", ...}]
  3 | 丙   | []          -- 无订单，coalesce 兜底为空数组
(3 rows)

用户"丙"没有订单，coalesce(o.orders, '[]'::jsonb) 保证了输出是 [] 而不是 null——这是接口设计里非常重要的一致性保证。

第 6 步：用 to_jsonb 快速导出整行

sql
-- 整行导出，省去逐列书写
SELECT to_jsonb(u) AS user_row
FROM (SELECT id, name, email FROM users WHERE id = 1) AS u;

第 7 步：清理 null 字段

sql
-- 把值为 null 的字段删掉，让响应更干净
SELECT jsonb_strip_nulls(
         jsonb_build_object('id', 1, 'name', '甲', 'phone', NULL, 'remark', NULL)
       ) AS cleaned;
text
             cleaned
----------------------------------
 {"id": 1, "name": "甲"}
(1 row)

第 8 步：清理

sql
DROP TABLE order_items;
DROP TABLE orders;
DROP TABLE users;

注意事项 ​

  • jsonb_build_object 的参数个数必须是偶数，键值交替，漏写一个值会直接报错。
sql
-- 错误
SELECT jsonb_build_object('a', 1, 'b');
text
ERROR:  argument list must have even number of elements
  • 键不能为 NULL，会直接抛错而不是跳过。若键来自数据列，需先用 coalesce 兜底。
sql
-- 错误
SELECT jsonb_build_object(NULL, 1);

-- 正确：给键兜底（但要警惕兜底后产生的键冲突）
SELECT jsonb_build_object(coalesce(k, 'unknown'), v) FROM t;
  • jsonb_object_agg 遇重复键静默覆盖，不报错，容易造成数据丢失；键不唯一时用 PG16+ 的 jsonb_object_agg_unique。
sql
-- 静默丢数据
SELECT jsonb_object_agg(k, v) FROM (VALUES ('a',1), ('a',2)) AS t(k, v);
-- {"a": 2}
  • jsonb_strip_nulls 不删数组里的 null 元素，只删对象字段。
sql
SELECT jsonb_strip_nulls('{"a": [1, null, 2]}'::jsonb);   -- {"a": [1, null, 2]}
  • jsonb_agg 不保证顺序，必须显式 ORDER BY，否则测试会间歇性失败。
sql
-- 推荐
SELECT jsonb_agg(name ORDER BY id) FROM products;
  • jsonb_agg 保留 NULL 输入并转成 JSON null，要去掉需 FILTER 或 jsonb_agg_strict（PG16+）。
sql
SELECT jsonb_agg(x) FILTER (WHERE x IS NOT NULL) FROM t;
  • row_to_json 返回 json 而非 jsonb，json 不能比较、不能建 GIN 索引；除非下游要求 json，否则一律用 to_jsonb。
sql
-- 返回类型不同
SELECT pg_typeof(to_jsonb(1))  AS a,   -- jsonb
       pg_typeof(row_to_json(ROW(1))) AS b;  -- json
  • to_jsonb(整行) 会带上所有列，包括不该暴露的密码哈希、内部字段，接口层务必用子查询先选列。
sql
-- 危险
SELECT to_jsonb(u) FROM users u;

-- 安全
SELECT to_jsonb(x) FROM (SELECT id, name FROM users) AS x;
  • LATERAL 子查询不要忘记 ON true，写成逗号连接会丢失无匹配行的主行。
sql
-- 正确：保留无订单的用户
LEFT JOIN LATERAL (...) AS o ON true;

-- 错误：无订单的用户会整行消失
, LATERAL (...) AS o
  • 嵌套聚合层数越多代价越高，三层以上开始明显地随主表行数线性放大，接口层应考虑拆分为多次查询。
sql
-- 每多一层 LATERAL，就多一次按行的聚合执行
EXPLAIN (ANALYZE, BUFFERS) SELECT jsonb_build_object(...) FROM users u LEFT JOIN LATERAL (...) ON true;
  • jsonb 会重排键并去重，与手写 json 文本的输出顺序不同，不要依赖键顺序做断言。
sql
SELECT jsonb_build_object('b', 1, 'a', 2);   -- {"a": 2, "b": 1}
  • 构建出来的 JSON 不参与索引。若这些 JSON 需要被查询，应把关键字段落成独立列并建索引，JSON 只作响应载体。
sql
-- 需要按 status 查询时，依赖独立列而非 JSON 内部字段
SELECT jsonb_agg(x) FROM orders WHERE status = 'paid';

最佳实践 ​

  • 先用子查询选列，再 to_jsonb 包一层，这是最不易漏字段的写法。
sql
SELECT to_jsonb(u) FROM (SELECT id, name, email FROM users LIMIT 10) AS u;
  • "多行折叠"一律显式写 ORDER BY，让输出稳定可测。
sql
jsonb_agg(order_no ORDER BY created_at DESC)
  • 嵌套从表用 LEFT JOIN LATERAL ... ON true + coalesce(..., '[]'::jsonb)，"无子行"统一输出空数组而不是 null。
sql
'orders', coalesce(o.orders, '[]'::jsonb)
  • 区分三处 NULL 语义：构建函数保留 NULL、jsonb_agg 保留 NULL、jsonb_strip_nulls 只删对象字段的 NULL。展示层要"干净"就在最外层统一 jsonb_strip_nulls。
sql
SELECT jsonb_strip_nulls(final_json) FROM (...);
  • PG16+ 优先用 _strict / _unique 变体，把"跳过 NULL""重复键"从隐式行为变成显式语义。
sql
jsonb_agg_strict(x)
jsonb_object_agg_unique(k, v)
  • 金额用 numeric 而非 float，numeric 会按 scale 输出，避免浮点尾数进入 JSON。
sql
jsonb_build_object('amount', amount)   -- amount 为 numeric(10,2)
  • 接口层只输出必要字段，绝不 to_jsonb(整行)，防止内部字段泄露。
sql
SELECT to_jsonb(x) FROM (SELECT id, name FROM users) AS x;
  • 聚合层数控制在三层以内，返回给前端时用 jsonb_pretty 只在调试环境打印，生产不要用它（额外格式化开销）。
sql
-- 仅调试
SELECT jsonb_pretty(order_json) FROM (...);
  • 需要按 JSON 内部条件筛选时，先筛行再构造，不要构造完再在应用层过滤。
sql
-- 推荐：先过滤
SELECT jsonb_agg(jsonb_build_object('no', order_no))
FROM orders WHERE status = 'paid';
  • 用 EXPLAIN (ANALYZE, BUFFERS) 验证嵌套聚合，LATERAL 写法在行数多时可能退化为逐行扫描，必要时改成"先聚合再 JOIN"。
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT jsonb_build_object('id', u.id, 'orders', o.orders) FROM users u LEFT JOIN LATERAL (...) ON true;

下一节

继续学习：JSONB 查询函数（jsonb_path_query）

参考链接 ​