PostgreSQL如何无需手动列名输出含关联数据的JSON

针对你提到的三张表（Article、Account、Team）生成嵌套结构JSON，同时支持WHERE条件查询的需求，无需手动编写数百列名的高效方案如下：

一对一关联场景（单Article对应单Account、单Team） #

如果Article与Account是一对一，Account与Team是一对一，可通过JOIN后直接将关联表整行转为JSON对象，自动包含所有列：

SELECT row_to_json(article_data) AS nested_json
FROM (
  SELECT
    art.*,
    row_to_json(acc) AS account,
    row_to_json(tm) AS team
  FROM Article art
  JOIN Account acc ON art.account_id = acc.id
  JOIN Team tm ON acc.team_id = tm.id
  -- 在此添加任意WHERE条件，比如筛选特定文章或团队
  WHERE art.publish_time >= '2024-01-01' AND tm.name = '技术部'
) article_data;

一对多关联场景（单Team多Account、单Account多Article） #

如果存在一对多关系，比如一个团队下有多个账号，每个账号有多篇文章，用json_agg()将关联行转为JSON数组，嵌套生成层级结构：

SELECT row_to_json(team_data) AS nested_json
FROM (
  SELECT
    tm.*,
    json_agg(account_data) AS accounts
  FROM Team tm
  JOIN (
    SELECT
      acc.*,
      json_agg(art) AS articles
    FROM Account acc
    LEFT JOIN Article art ON acc.id = art.account_id
    -- 可在此添加账号或文章的过滤条件
    WHERE acc.status = '激活'
    GROUP BY acc.id
  ) account_data ON tm.id = account_data.team_id
  -- 可在此添加团队的过滤条件
  WHERE tm.region = '华东'
  GROUP BY tm.id
) team_data;

关键优势 #

• 无需手动枚举数百列：row_to_json(table_alias)自动将表的所有列转为JSON对象，json_agg(table_alias)自动将多行转为JSON数组
• 完全支持WHERE条件：可在主查询、子查询的任意环节添加过滤条件，精准筛选关联数据
• 灵活调整输出：如果需要排除敏感列，PostgreSQL 12+支持用table_alias.* EXCLUDE (column_name)剔除指定字段，比如acc.* EXCLUDE (password)

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