PostgreSQL中json_agg查询添加关联表WHERE条件方法

需要从PostgreSQL查询返回如下嵌套结构的JSON结果：

[
  {"id": 1, "name": "some organisation name", "alias": [{"alias":"alt name"}, {"alias":"another name"}]}
  ...
]

初始查询可正常返回结果，但新增基于别名的过滤条件时报错，报错原因为外层WHERE子句无法访问LATERAL子查询内部定义的表别名，SQL作用域规则限制子查询内的表别名仅在当前子查询层级可见，外层无权直接引用。
原错误查询如下：

SELECT json_build_object('id', table1.id, 'name', table1.name, 'alias', a.alias) as json
   FROM orgs table1
    CROSS JOIN LATERAL (
      SELECT json_agg(agg) AS alias
      FROM  (
        SELECT table2.alias as name
        FROM org_aliases table2
        WHERE 
            table2.org_id = table1.id
         ) agg
    ) a
    WHERE
        table1.name ilike 'nspcc' 
        or table2.alias ilike 'nspcc';

不需要重复关联org_aliases表，直接在现有LATERAL子查询中同步计算别名匹配标记，外层直接基于该标记过滤即可，仅需单次扫描别名表，性能最优。
修正后SQL：

SELECT json_build_object(
    'id', table1.id,
    'name', table1.name,
    'alias', a.alias
) AS json
FROM orgs table1
-- 如果存在无任何别名的组织需要保留，将CROSS JOIN LATERAL改为LEFT JOIN LATERAL
CROSS JOIN LATERAL (
    SELECT
        -- 直接生成符合结构要求的别名JSON数组，修正原查询字段名不匹配的问题
        json_agg(json_build_object('alias', table2.alias)) AS alias,
        -- 聚合判断当前组织是否存在匹配搜索词的别名
        bool_or(table2.alias ILIKE 'nspcc') AS has_match_alias
    FROM org_aliases table2
    WHERE table2.org_id = table1.id
) a
WHERE
    table1.name ILIKE 'nspcc'
    -- 用LEFT JOIN时这里改为OR COALESCE(a.has_match_alias, false)
    OR a.has_match_alias;

方案优势 #

• 仅对org_aliases表做一次关联扫描，无重复扫表的额外开销，可正常利用alias字段上的索引
• 复用子查询中已读取的别名数据做匹配判断，无多余计算成本
• 同步修正了原查询中别名数组字段名错误的问题（原查询生成的是{"name":"xxx"}结构，不符合需求的{"alias":"xxx"}要求）

不推荐的方案 #

• 重复关联org_aliases表做过滤：会造成同表二次扫描，数据量大时性能损耗明显
• 外层解析已生成的JSON数组做匹配：需要先完成JSON序列化再反序列化解析，额外增加计算开销，且无法利用表上索引，性能极差

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