PostgreSQL 8.0.2中如何拆分JSON数组为单行记录

你的需求是将qr.answer字段中的JSON数组拆分为每个元素对应一行记录，结合原用户和问题信息返回。以下是针对不同PostgreSQL版本和字段类型的解决方案：

针对PostgreSQL 9.3及以上版本（推荐方案） #

PostgreSQL 9.3引入了LATERAL JOIN，这是你尝试的CROSS APPLY在PostgreSQL中的等价写法。同时需要根据qr.answer的字段类型（json或jsonb）选择对应的数组展开函数：

情况1：qr.answer为jsonb类型 #

select 
    u.user_id
    ,u.first_name
    ,u.last_name
    ,u.email_address
    ,u.zip_code
    ,u.birthdate
    ,u.gender
    ,answer_item as answer
    ,qr.question_id
from fancompass_acurve.users u
left join fancompass_acurve.question_responses qr
    on u.user_id = qr.user_id
left join lateral jsonb_array_elements_text(qr.answer) as answer_item
    on true

情况2：qr.answer为json类型 #

将上述SQL中的jsonb_array_elements_text替换为json_array_elements_text即可：

select 
    u.user_id
    ,u.first_name
    ,u.last_name
    ,u.email_address
    ,u.zip_code
    ,u.birthdate
    ,u.gender
    ,answer_item as answer
    ,qr.question_id
from fancompass_acurve.users u
left join fancompass_acurve.question_responses qr
    on u.user_id = qr.user_id
left join lateral json_array_elements_text(qr.answer) as answer_item
    on true

关键说明：

• 使用LEFT JOIN LATERAL ... ON TRUE而非CROSS JOIN LATERAL，可以保留那些没有对应问题回答（qr.answer为null）的用户记录；如果不需要这些记录，可换成CROSS JOIN LATERAL。
• PostgreSQL不支持CROSS APPLY，必须用LATERAL JOIN替代。

针对PostgreSQL 9.2及以下版本（兼容方案） #

如果你的PostgreSQL版本低于9.3，不支持LATERAL JOIN，可以通过字符串处理的方式临时解决（仅适用于无特殊字符的简单JSON数组）：

select 
    u.user_id
    ,u.first_name
    ,u.last_name
    ,u.email_address
    ,u.zip_code
    ,u.birthdate
    ,u.gender
    ,trim(both '"' from unnest(string_to_array(replace(replace(qr.answer, '[', ''), ']', ''), ','))) as answer
    ,qr.question_id
from fancompass_acurve.users u
left join fancompass_acurve.question_responses qr
    on u.user_id = qr.user_id

注意：这种方法通过去除JSON数组的括号、拆分字符串来模拟数组展开，若数组元素包含逗号或引号等特殊字符会出错，优先建议升级PostgreSQL版本使用标准方法。

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