PostgreSQL中如何将JSON字段解析为单列多行而非多列

你需要将JSON数组展开为多行，而不是拆分为多列，这样就能实现一个leadID对应多行agent记录的需求。以下是修改后的查询语句：

WITH agent_pool_cte AS (
    SELECT 
        cast(_airbyte_data->>'leadId' as bigint) as lead_id,
        -- 展开agents数组，每个agent对象生成一行
        agent->>'aggregateId' as agent_id,
        _airbyte_data
    FROM agent_list
    -- 展开JSON数组，根据字段类型选择json_array_elements或jsonb_array_elements
    CROSS JOIN json_array_elements(_airbyte_data->'agents') as agent
),
agent_name_cte AS (
    SELECT 
        apc.lead_id,
        apc.agent_id,
        concat(pup.first_name,' ',pup.last_name) as agent_name,
        apc._airbyte_data
    FROM agent_pool_cte apc
    -- 只需一次关联即可获取对应agent的姓名
    LEFT JOIN partner_user_profiles pup 
        ON pup.aggregate_id::text = apc.agent_id
)
SELECT lead_id, agent_id, agent_name FROM agent_name_cte;

关键修改说明： #

• 数组展开：使用CROSS JOIN json_array_elements(_airbyte_data->'agents')将JSON数组中的每个agent对象拆分为单独的行，自动处理最多9个agent的情况（即使agent数量少于9也只会生成对应行数）。
• 简化关联：不再需要9次LEFT JOIN，只需一次关联就能匹配agent_id和姓名，避免了冗余的列和复杂的关联逻辑。
• 结果结构：最终结果会以lead_id、agent_id、agent_name的形式呈现，完全符合你需要的一行对应一个agent的格式。

注意：如果你的_airbyte_data是jsonb类型，将json_array_elements替换为jsonb_array_elements即可。

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