PostgreSQL：将string_agg生成的文本列转为jsonb的查询改写求助

原场景与问题 #

原本通过字符串拼接生成JSON格式数据，插入到TEXT类型的records列中。现在该列改为JSONB类型，需要用jsonb_build_object和jsonb_object_agg改写原查询，避免手动拼接的语法风险。

原查询代码 #

select 'my_table' as name,
    '{"my_table":{' ||
    string_agg('"' || nr || code || '":{"code":"' || nr || code || '","name_eng":"' || eng || '","name_de":"' || de || '","name_se":"' || se || '","nr_cd":"' || nr || '","code":"' || code || '"}', ',') || '}}' as records,
    '[{"attribut":"nr_cd","codetable_type":"String"},{"attribut":"cus_immopaccode","codetable_type":"String"}]' as custom_attributes
from (select '01' as code, 'Rat' as eng, 'Ratte' as de, 'Ratta' se 
        union all select  '02', 'Cow','Kuh','Ko'
        union all select  '03', 'Dog','Hund','Hund'
        union all select  '04', 'Cat','Katze','Katt'
    )d
        cross join (select '1' as nr 
                union all select '2'
                union all select '3'
                )m  

改写后的JSONB版本查询 #

select 
    'my_table' as name,
    jsonb_build_object(
        'my_table',
        jsonb_object_agg(
            nr || code,
            jsonb_build_object(
                'code', nr || code,
                'name_eng', eng,
                'name_de', de,
                'name_se', se,
                'nr_cd', nr,
                'code', code
            )
        )
    ) as records,
    '[{"attribut":"nr_cd","codetable_type":"String"},{"attribut":"cus_immopaccode","codetable_type":"String"}]'::jsonb as custom_attributes
from (select '01' as code, 'Rat' as eng, 'Ratte' as de, 'Ratta' se 
        union all select  '02', 'Cow','Kuh','Ko'
        union all select  '03', 'Dog','Hund','Hund'
        union all select  '04', 'Cat','Katze','Katt'
    )d
cross join (select '1' as nr 
            union all select '2'
            union all select '3'
           )m  

关键改写说明 #

• jsonb_build_object：替代字符串拼接，直接构建键值对形式的JSONB对象，自动处理转义逻辑，避免语法错误。
• jsonb_object_agg：将多行数据聚合为单个JSONB对象，第一个参数是对象的唯一键（此处用nr || code拼接生成），第二个参数是对应的值（由内层jsonb_build_object生成的子对象）。
• 外层用jsonb_build_object包裹my_table键，对应聚合后的JSONB对象，完全匹配原查询的结构。
• custom_attributes列也转为JSONB类型（通过::jsonb强制转换），更贴合JSONB列的存储特性。

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