PostgreSQL中jsonb_build_object使用及结果格式问题求助

问题分析 #

原查询存在几个关键问题：

1. 语法错误：cte1中jsonb_build_object的写法错误，嵌套的select无需额外括号，且字段定义不符合函数语法；
2. 表引用错误：最后查询时引用了不存在的cte表，正确应为cte1；
3. 结构逻辑错误：直接关联cte1和cte2会产生笛卡尔积，返回两列数据，无法合并成期望的数组结构。

修正后的查询语句 #

WITH cte_personne AS (
    -- 构建所有personne类型的JSON对象
    SELECT jsonb_build_object(
        'personne', 
        jsonb_build_object(
            'active', false,
            'descriptionPersonne', jsonb_build_object('a', 'p1')
        )
    ) AS entity_item
    UNION ALL
    SELECT jsonb_build_object(
        'personne', 
        jsonb_build_object(
            'active', false,
            'descriptionPersonne', jsonb_build_object('a', 'p2')
        )
    ) AS entity_item
),
cte_company AS (
    -- 构建company类型的JSON对象
    SELECT jsonb_build_object(
        'company', 
        jsonb_build_object(
            'active', false,
            'descriptionCompany', jsonb_build_object('a', 'c1')
        )
    ) AS entity_item
)
-- 合并所有条目并包装成最终的JSON结构
SELECT jsonb_build_object(
    'entity', jsonb_agg(entity_item)
) AS result
FROM (
    SELECT entity_item FROM cte_personne
    UNION ALL
    SELECT entity_item FROM cte_company
) AS combined;

说明 #

1. 在cte_personne和cte_company中分别构建单个实体JSON对象，每个对象为{"personne": {...}}或{"company": {...}}格式；
2. 通过UNION ALL将两类实体合并为一个数据集；
3. 用jsonb_agg函数将所有实体条目聚合为数组，最后用jsonb_build_object包装成包含entity键的最终JSON结构。

执行后会得到期望结果：

{"entity": [
    {"company": {"descriptionCompany": {"a": "c1"}, "active": false}}, 
    {"personne": {"descriptionPersonne": {"a": "p1"}, "active": false}},
    {"personne": {"descriptionPersonne": {"a": "p2"}, "active": false}}
]}

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