如何在PostgreSQL中使用SQL聚合JSONB字段生成结构化对象-SQL-PHP中文网

jsonb_object_agg()用于将键值对聚合成jsonb对象，需key显式转text，value为null时被忽略；jsonb_agg()+jsonb_build_object()构造对象数组；聚合空时用coalesce(..., '{}'::jsonb)兜底。

如何在postgresql中使用sql聚合jsonb字段生成结构化对象

用 `jsonb_object_agg()` 把键值对聚合成 JSONB 对象

当源数据是「键-值」两列（比如 key 和 value），且你想按某一分组生成一个结构化 JSONB 对象时，jsonb_object_agg() 是最直接的选择。它会把每组内的键值对收集成一个 jsonb 对象，自动处理重复键（后出现的覆盖前一个）。

常见错误是传入非字符串类型的 key —— PostgreSQL 会报错 ERROR: field name must be type text。必须显式转换：jsonb_object_agg(key::text, value)。

使用场景举例：一张配置表 config_settings(group_id, key, value)，想按 group_id 汇总为每个组一个配置对象：

SELECT group_id, jsonb_object_agg(key::text, value) AS config
FROM config_settings
GROUP BY group_id;

若 value 是数字或布尔，它会被原样转为 JSONB 类型（不需要额外 to_jsonb()）
若 value 是 NULL，默认被忽略；如需保留 "key": null，得写成 jsonb_object_agg(key::text, coalesce(value::jsonb, 'null'::jsonb))
性能上，该函数在大数据量分组时比手写 jsonb_build_object() + jsonb_concat_agg() 更快、更稳定

用 `jsonb_agg()` + `jsonb_build_object()` 构造数组内嵌对象

当你需要把多行记录“转成”一个 JSONB 对象数组（例如 API 返回的 items: [{...}, {...}]），就不能用 jsonb_object_agg() —— 它只产出单个对象，不是数组。此时组合 jsonb_agg() 和 jsonb_build_object() 是标准解法。

典型误用是漏掉 jsonb_build_object()，直接对原始行做 jsonb_agg(t.*)，结果会包含系统字段（如 tableoid）、类型信息冗余，且不可控。

示例：从用户表构造带字段重命名的数组：

dmap-jsapi-skill

DMap GL地图JSAPI（dmapgl）开发指南。用于编写、审查、调试 DMap 地图 API 代码，涵盖地图初始化、覆盖物渲染、图层管理、事件处理、控件交互等任务。当用户提及 dmap、dmapgl、jsapi‑gl 或相关地图开发需求时自动触发。

下载

SELECT jsonb_agg(
  jsonb_build_object(
    'id', id,
    'name', name,
    'active', is_active,
    'tags', COALESCE(tags, '[]'::jsonb)
  )
) AS users
FROM users
WHERE created_at > '2024-01-01';

jsonb_build_object() 参数必须成对出现（奇数个参数会报错），且键必须是文本字面量或强制转 ::text
如果某字段可能为 NULL 且你希望它在 JSON 中也表现为 null，不用额外处理；但若想跳过该字段，得用 CASE WHEN ... THEN ... END 控制是否参与构建
注意 jsonb_agg() 默认不保序，如需按某列排序，必须加 ORDER BY 子句：jsonb_agg(...) ORDER BY created_at DESC

聚合含嵌套结构的 JSONB 字段时避免意外扁平化

如果源字段本身是 jsonb（比如日志中的 payload），又想按某维度聚合多个 payload 到一个数组里，直接 jsonb_agg(payload) 是对的；但若试图用 jsonb_object_agg() 就会出问题 —— 它要求第一个参数是标量键，而你传的是整个 JSONB 值，PostgreSQL 会报 cannot cast type jsonb to text。

更隐蔽的问题是：有人想“合并多个 JSONB 对象的字段”，比如把每行的 metadata 合并成一个大对象。这时不能用 jsonb_object_agg()，也不能简单 jsonb_concat_agg()（它会把同名键的值拼成数组）。正确做法取决于需求：

要深度合并（递归覆盖）：用 jsonb_merge_agg()（需扩展 jsonb_ops 或自定义聚合函数）
只要顶层键合并、冲突时后覆盖前：用 jsonb_concat_agg(metadata)（9.5+ 内置）
想把所有 metadata 收进数组留待客户端处理：回到 jsonb_agg(metadata)

别忘了：所有涉及 jsonb 的聚合函数都跳过 NULL 输入项，这点和普通聚合（如 sum()）一致，但容易被忽略。

空组或无匹配数据时返回空对象而非 NULL

聚合函数在没有输入行时默认返回 NULL，这常导致应用层解析失败（比如期望一个 {} 却收到 NULL）。解决方法不是加 COALESCE(..., '{}') —— 因为 COALESCE 无法改变返回类型，而 jsonb_object_agg() 返回的是 jsonb，'{}' 是 text，类型不匹配会报错。

正确写法是显式转类型：COALESCE(jsonb_object_agg(...), '{}'::jsonb)。