JSON_BUILD_OBJECT需显式嵌套调用，内层必须用json_build_object或to_json转为JSON类型，否则字段被当字符串处理；一对多用json_agg包裹json_build_object；NULL值默认保留键，删键需json_strip_nulls或json_object_agg。
JSON_BUILD_OBJECT 本身不支持直接嵌套，得靠“套娃”写法
PostgreSQL 的 json_build_object 不会自动递归解析子查询或表达式为 JSON 对象——它只认你传进去的**值**。想生成嵌套结构，必须把内层也用 json_build_object（或 json_build_array）先构造成 JSON 类型，再作为字段值传给外层。
常见错误是直接写字段名或子查询，结果字段变成字符串字面量，比如：json_build_object('user', users.*) → {"user": "users.*"}（字符串），而不是展开的 JSON 对象。
- 内层必须显式调用
json_build_object或to_json/row_to_json转成json类型 - 别用
*展开，除非配合row_to_json；否则列名丢失、顺序不可控 - 如果子对象来自 JOIN 表，注意 NULL 值：默认生成
"profile": null，如需跳过，得用CASE或FILTER配合json_agg
用子查询 + json_build_object 构建单层嵌套对象
这是最常用、最可控的方式。把关联数据查出来，用子查询返回一个 json 值，再塞进外层对象里。
SELECT json_build_object(
'id', u.id,
'name', u.name,
'profile', (
SELECT json_build_object(
'age', p.age,
'city', p.city,
'tags', p.tags -- 假设 tags 是 text[]，会自动转成 JSON array
)
FROM profiles p
WHERE p.user_id = u.id
)
)
FROM users u
WHERE u.id = 123;
注意：子查询必须返回 **0 或 1 行**，否则报错 more than one row returned by a subquery used as an expression。如果一对多（比如用户有多个地址），就得换 json_agg + json_build_object 组合。
一对多关系用 json_agg 包裹 json_build_object
当嵌套的是数组（如用户的所有订单），不能用单行子查询，得用 json_agg 聚合多行结果。
SELECT json_build_object(
'user', json_build_object('id', u.id, 'name', u.name),
'orders', (
SELECT json_agg(
json_build_object(
'order_id', o.id,
'total', o.total,
'items', (
SELECT json_agg(json_build_object('name', i.name, 'qty', i.qty))
FROM order_items i
WHERE i.order_id = o.id
)
)
)
FROM orders o
WHERE o.user_id = u.id
)
)
FROM users u
WHERE u.id = 456;
关键点：
-
json_agg内部仍要用json_build_object构造每个元素，不能直接json_agg(o.*) - 三层嵌套（用户→订单→商品）时，最内层子查询的
WHERE条件必须精确关联上一层的别名（如o.id），否则结果错乱或为空 - 性能敏感场景慎用多层相关子查询；可考虑先 CTE 预聚合，再 JOIN 组装
NULL 值处理和空数组边界情况
没匹配到 profile 或 orders 时，对应字段是 null，不是缺失字段。如果希望彻底省略该键，得用 CASE 判断：
SELECT json_build_object(
'id', u.id,
'name', u.name,
'profile', CASE
WHEN EXISTS (SELECT 1 FROM profiles p WHERE p.user_id = u.id)
THEN (
SELECT json_build_object('age', p.age, 'city', p.city)
FROM profiles p WHERE p.user_id = u.id
)
ELSE NULL
END
)
FROM users u;
但注意：json_build_object 里某字段值为 NULL，最终 JSON 中仍会保留该键并置为 null（如 {"profile": null}）。真要“删键”，得用 json_strip_nulls 后处理，或改用 json_object_agg 动态拼 key-value 对。
嵌套层级越深，子查询的可读性和执行计划越难控制；生产环境建议把复杂 JSON 拼装逻辑移到应用层，数据库只负责提供扁平化结果集。
