如何简化PostgreSQL 15查询返回的JSON结构？

问题核心 #

你碰到的两个问题根源很明确：

1. 结果多了外层"article"包裹：是因为最终的json_build_object把文章字段特意封装到了'article'键下
2. rel_articles存在"json_agg"嵌套：大概率是在CTE生成关联文章聚合时，错误地把json_agg结果又套在了一个多余的json_build_object键里

修改后的查询示例 #

假设你的表结构是articles(id, title, content)和related_articles(article_id, id, title, content)，调整后的查询如下：

WITH article_with_relations AS (
    SELECT 
        -- 直接保留文章原始字段，不做额外嵌套
        a.id,
        a.title,
        a.content,
        -- 直接用json_agg生成关联文章数组，避免多余嵌套
        json_agg(
            CASE WHEN ra.id IS NOT NULL THEN 
                json_build_object('id', ra.id, 'title', ra.title, 'content', ra.content)
            ELSE NULL END
        ) FILTER (WHERE ra.id IS NOT NULL) AS rel_articles
    FROM articles a
    LEFT JOIN related_articles ra ON a.id = ra.article_id
    GROUP BY a.id, a.title, a.content -- 所有非聚合字段必须加入分组
)
-- 直接构建目标结构：顶层是文章字段 + rel_articles数组
SELECT json_build_object(
    'id', id,
    'title', title,
    'content', content,
    'rel_articles', rel_articles
) AS target_json
FROM article_with_relations;

关键调整说明 #

• 移除外层"article"包裹：最终的json_build_object直接将文章的每个字段作为顶层键，不再嵌套到'article'键下
• 消除rel_articles的"json_agg"嵌套：确保json_agg直接生成关联文章的对象数组，不要在json_agg外层额外套json_build_object('json_agg', ...)
• 优化空关联场景：用FILTER (WHERE ra.id IS NOT NULL)避免无关联文章时生成含null的数组，返回空数组更符合业务预期

最终目标结构示例 #

调整后会得到你需要的JSON格式：

{
  "id": 1,
  "title": "主文章标题",
  "content": "主文章内容",
  "rel_articles": [
    {"id": 2, "title": "关联文章1", "content": "关联内容1"},
    {"id": 3, "title": "关联文章2", "content": "关联内容2"}
  ]
}

内容的提问来源于stack exchange，提问作者coterobarros