PostgreSQL：如何将jsonb列数组内元素转为snake_case格式

要转换profileGroups数组内部的negativeProfiles为negative_profiles，需要先拆分数组处理每个元素，再重新聚合，完整的视图创建语句如下：

CREATE OR REPLACE VIEW v_table AS
SELECT json_build_object(
    'country', column_1 -> 'country',
    'channel', column_1 -> 'channel',
    'profile_groups', (
        SELECT json_agg(
            json_build_object(
                'profiles', elem -> 'profiles',
                'negative_profiles', elem -> 'negativeProfiles'
            )
        )
        FROM jsonb_array_elements(column_1 -> 'profileGroups') AS elem
    )
)::jsonb AS column_1
FROM "table";

关键部分说明： #

• jsonb_array_elements(column_1 -> 'profileGroups') AS elem：将profileGroups数组拆分为单个JSON对象，每个对象用elem指代
• json_build_object('profiles', elem -> 'profiles', 'negative_profiles', elem -> 'negativeProfiles')：对每个拆分出的对象，保留profiles字段，将negativeProfiles重命名为negative_profiles
• json_agg(...)：把处理后的单个对象重新聚合成数组
• 用双引号包裹"table"是因为table是PostgreSQL的保留关键字，避免语法冲突

验证效果 #

执行上述语句后，视图v_table中的column_1字段会完全符合你需要的snake_case格式，包括数组内部的字段转换，同时保留原数据中的null值。

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