PostgreSQL中JSON_BUILD_OBJECT要求标量子查询必须返回单行，否则报错；正确做法是用JSON_AGG聚合多行、COALESCE处理空值、LATERAL连接替代嵌套子查询，并注意手动转义特殊字符。
标量子查询必须返回单行，否则 JSON_BUILD_OBJECT 直接报错
PostgreSQL 的 JSON_BUILD_OBJECT 不接受多行结果作为某个键的值。如果你写成 SELECT JSON_BUILD_OBJECT('id', id, 'tags', (SELECT tag FROM tags WHERE post_id = posts.id))，只要某篇 posts 对应多个 tags，就会触发错误：more than one row returned by a subquery used as an expression。
这不是 JSON 函数的问题，而是标量子查询本身的语义限制：它只能返回 0 或 1 行。常见误操作是忘了聚合、漏了 LIMIT 1、或没加 GROUP BY。
- 安全做法是用
(SELECT JSON_AGG(JSON_BUILD_OBJECT('name', tag)) FROM tags WHERE post_id = posts.id) - 若允许空数组，加
COALESCE(..., '[]'::json)；若允许 NULL，不加也行，但前端需兼容 - 想取第一个标签？用
(SELECT tag FROM tags WHERE post_id = posts.id LIMIT 1)，但注意这不保证顺序，建议加ORDER BY created_at
LEFT JOIN LATERAL 是替代标量子查询的更可控方案
当嵌套层级变深（比如文章 → 分类 → 分类下的属性），硬塞多个标量子查询会难读、难调、性能差。这时 LATERAL 能把子查询“拉平”为可连接的行集，再配合 JSON_AGG 组装。
例如查文章及其所有分类及每个分类的元数据：
SELECT JSON_BUILD_OBJECT(
'id', p.id,
'title', p.title,
'categories', COALESCE(c.cat_list, '[]'::json)
)
FROM posts p
LEFT JOIN LATERAL (
SELECT JSON_AGG(
JSON_BUILD_OBJECT(
'name', c.name,
'meta', COALESCE(m.meta_list, '[]'::json)
)
) AS cat_list
FROM categories c
LEFT JOIN LATERAL (
SELECT JSON_AGG(JSON_BUILD_OBJECT('key', k.key, 'value', k.value)) AS meta_list
FROM category_meta k
WHERE k.category_id = c.id
) m ON true
WHERE c.post_id = p.id
) c ON true;
-
LATERAL允许子查询引用外层表字段（如c.id），这是普通JOIN做不到的 - 每层
LATERAL都要确保只返回一行（靠JSON_AGG聚合），否则外层JSON_BUILD_OBJECT仍会报错 - 用
LEFT JOIN LATERAL而非INNER JOIN LATERAL，避免丢失无分类的文章
字符串字段含双引号或反斜杠时，JSON_BUILD_OBJECT 不自动转义
JSON_BUILD_OBJECT 只做类型转换，不做内容清洗。如果字段值本身含 " 或 （比如用户输入的 He said: "Hi!"），直接拼进 JSON 会导致语法错误或解析失败。
- 简单处理：用
REPLACE(field, '"', '\"')和REPLACE(..., '', '\')手动转义（注意顺序：先处理反斜杠） - 更稳妥：在应用层序列化前统一处理，数据库层尽量不承担转义责任
- 验证方式：把结果用
json_typeof()检查是否为object，或用json_valid()（PostgreSQL 16+）判断合法性
空值和缺失键会让嵌套结构断裂，得靠 COALESCE + 显式默认值兜底
标量子查询返回 NULL 时，JSON_BUILD_OBJECT 会把对应键设为 null 值，而不是跳过该字段。而很多前端或 API 规范要求“不存在的字段不出现”，或者要求空数组而非 null。
- 数组字段：始终用
COALESCE((SELECT ...), '[]'::json)，不能依赖JSON_AGG自动补空 —— 它在子查询无结果时返回NULL，不是空数组 - 对象字段：用
COALESCE((SELECT JSON_BUILD_OBJECT(...)), '{}'::json) - 标量字段（如
author_name）：用COALESCE(author_name, '')或NULLIF(author_name, '')控制是否保留键
最易被忽略的是多层嵌套中某一层子查询意外返回空，导致外层整个 JSON 构建中断或结构错位 —— 必须每一层都独立做空值防护，不能只在外层包一次 COALESCE。
