Postgres生成GeoJSON时嵌套聚合调用受限的解决方法问询

你遇到的嵌套聚合报错问题，根源是单层查询无法同时处理两个独立多对多关联的聚合逻辑，同时多表直接Join还会产生笛卡尔积，导致聚合结果不符合预期。可以通过CTE预先聚合两个关联集合的方式解决，具体实现如下：

完整实现代码 #

WITH agg_protections AS (
    -- 预先聚合每个基础设施的防护措施键值对
    SELECT
        i.infra_id,
        json_object_agg(p.ptype, i.pscore) AS protections
    FROM infraprotection i
    JOIN protection p ON p.id = i.protection_id
    GROUP BY i.infra_id
),
agg_responses AS (
    -- 预先聚合每个基础设施的专家回复键值对
    SELECT
        er.infra_id,
        json_object_agg(ep.etype, er.response) AS responses
    FROM expertresponse er
    JOIN expert ep ON ep.id = er.expert_id
    GROUP BY er.infra_id
)
SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(
        json_build_object(
            'type', 'Feature',
            'geometry', ST_AsGeoJSON(h.geom)::json,
            'properties', json_build_object(
                'id', h.id,
                'protections', COALESCE(ap.protections, '{}'::json),
                'responses', COALESCE(ar.responses, '{}'::json),
                'category', c.category
            )
        )
    )
)
FROM hardinfra h
JOIN category c ON c.id = h.category_id
-- 左连接兼容无关联数据的场景，避免漏掉基础设施记录
LEFT JOIN agg_protections ap ON ap.infra_id = h.id
LEFT JOIN agg_responses ar ON ar.infra_id = h.id;

逻辑说明 #

1. 先通过两个独立的CTE子查询，按infra_id分组分别聚合得到每个基础设施对应的protections和responses键值对象，两个聚合逻辑互相独立，不会产生冲突
2. 外层查询直接关联基础表和预聚合的结果，不需要再做嵌套聚合，既规避了嵌套聚合的报错问题，也避免了多表Join导致的重复数据干扰聚合结果
3. 使用COALESCE函数处理无关联数据的场景，默认返回空JSON对象，保证输出格式统一

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