PostgreSQL关联表用json_agg生成嵌套JSON时遇列不存在错误

错误原因分析 #

PostgreSQL默认会把未加双引号的标识符转为小写，你的members表中关联列是**patternsUuid**（包含大写字母），但SQL里写的members.patternsUuid没加双引号，数据库会去查找全小写的members.patternsuuid，因此触发找不到列的错误。

修复后的完整SQL #

要实现你需要的嵌套JSON输出，需修正列名引用方式，同时调整json_agg的用法来生成嵌套对象，最终SQL如下：

SELECT json_build_object(
  'patterns', json_agg(
    json_build_object(
      'patternUuid', p.patternUuid,
      'title', p.title,
      'urlName', p.urlName,
      'description', 'Lorem ipsum dolor sit', -- 原表无此字段，使用示例值
      'members', json_agg(
        json_build_object('memberId', m.memberId)
      )
    )
  )
) AS result
FROM public.patterns p
JOIN public.members m
  ON m."patternsUuid" = p.patternUuid -- 用双引号包裹大小写敏感的列名
GROUP BY p.patternUuid, p.title, p.urlName;

关键说明 #

1. 列名大小写处理：所有包含大写字母的列名必须用双引号包裹，比如m."patternsUuid"，确保数据库能正确识别目标列。
2. 嵌套JSON构造：
   • 用json_build_object构造单个pattern的JSON对象，包含所有需要返回的字段。
   • 内层通过json_agg(json_build_object('memberId', m.memberId))生成每个pattern对应的members数组，每个元素是包含memberId的对象。
   • 外层用json_agg将所有pattern对象组合成数组，再用json_build_object包装成patterns键对应的最终结构。
3. 分组规则修正：SELECT中所有非聚合字段（p.patternUuid, p.title, p.urlName）都需要加入GROUP BY子句，避免分组逻辑错误。

输出示例 #

执行后会生成符合预期的JSON格式：

{
  "patterns": [
    {
      "patternUuid": "1b3d8a0f-2bc3-4c50-a00a-6cf99335dd20",
      "title": "Credit Card Fraud",
      "urlName": "card-fraud",
      "description": "Lorem ipsum dolor sit",
      "members": [
        {"memberId": 1},
        {"memberId": 2}
      ]
    },
    {
      "patternUuid": "04bdcd7b-d61b-45cc-b9ea-c2decc77c852",
      "title": "Return Policy Fraud",
      "urlName": "return-policy-fraud",
      "description": "Lorem ipsum dolor sit",
      "members": [
        {"memberId": 3},
        {"memberId": 3}
      ]
    }
  ]
}

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