PostgreSQL 14中SQL如何使用聚合函数生成JSONB对象-SQL-PHP中文网

jsonb_object_agg用于将多行数据聚合成JSONB键值对对象，需指定key和value字段，不支持record类型参数，key重复时后值覆盖前值，NULL key被跳过，NULL value保留为JSON null。

postgresql 14中sql如何使用聚合函数生成jsonb对象

jsonb_object_agg 生成键值对映射对象

想把多行数据转成一个 JSONB 对象（比如配置项 ID → 名称映射），直接用 jsonb_object_agg 最省事。它要求明确指定「哪个字段当 key」、「哪个表达式当 value」，不能传整行或 *。

常见错误是写成 jsonb_object_agg(t) —— 这会报错，因为函数不接受 record 类型参数。

正确写法：确保 key 是标量且唯一（否则后出现的 key 会覆盖前面的）
```
SELECT jsonb_object_agg(id, name) FROM categories;
```
如果 key 可能重复，先用 DISTINCT ON (key_col) 或子查询去重

value 支持任意类型，包括 jsonb_build_object 嵌套：

SELECT jsonb_object_agg(code, jsonb_build_object('label', label, 'order', sort_order)) FROM status_codes;

注意 NULL key 会被跳过，NULL value 会保留为 JSON null

jsonb_agg + row_to_json 或 to_jsonb 构建数组

要生成 JSONB 数组（比如把一组用户转成 [{"id":1,"name":"A"},{"id":2,"name":"B"}]），得先让每行变成 JSONB，再聚合。这里有两个主流路径：

Expanso yaml-to-json

使用 Expanso Edge 管道将 YAML 输入转换为 JSON 格式。

下载

row_to_json(t)::jsonb：兼容性好，但必须显式加 ::jsonb 强转，否则返回的是 json 类型，后续没法用 jsonb_path_query 等函数
to_jsonb(t)：更简洁，直接输出 jsonb，且自动忽略 NULL 字段（row_to_json 会保留 NULL 字段）
别在 jsonb_agg 里直接写复杂逻辑，比如 jsonb_agg(to_jsonb(t) || '{"ts": now()}'::jsonb) —— 这会让聚合变慢，建议先在子查询中拼好
排序必须显式声明：jsonb_agg(to_jsonb(t) ORDER BY t.created_at DESC)，否则结果顺序不可靠

GROUP BY 场景下嵌套结构生成（如订单+商品）

业务常要“每个订单一个对象，其 items 字段是商品数组”，这不是 FOR JSON AUTO 那种扁平化能解决的——PostgreSQL 没有原生 FOR JSON，得靠分层聚合。

核心思路：外层按订单分组，内层用 jsonb_agg 聚合商品

典型写法：

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

若商品表可能为空（无明细），用 LEFT JOIN + CASE WHEN COUNT(i.*) > 0 THEN jsonb_agg(...) ELSE '[]'::jsonb END 控制空数组逻辑
避免在 jsonb_build_object 内部调用聚合函数，语法不支持；所有聚合必须在外层 SELECT 或子查询中完成

性能与类型陷阱：为什么你拿到的是 json 而不是 jsonb？

很多查询看着返回了 JSON 字符串，但一用 jsonb_path_query 就报错“function does not exist”，根本原因是类型不对——你拿到的是 json，不是 jsonb。

json_agg 返回 json，jsonb_agg 才返回 jsonb；别混用
row_to_json 返回 json，to_jsonb 才返回 jsonb；漏掉 ::jsonb 强转也会掉坑里
大结果集时，jsonb_agg 内存占用比 json_agg 略低（二进制存储更紧凑），但两者都会把整组数据加载进内存，单组超 5 万行就该警惕 OOM
如果最终要导出文件，记住：jsonb_agg 只生成值，不落地；psql 里得配合 \t \pset tuples_only on \o output.json，且确保整个查询只返回**单个值**，否则输出不是合法 JSON