PostgreSQL拆分括号内数据为行时出现空值问题求助

在PostgreSQL的test表中存储了以下数据，其中questionid为201的记录对应option_3和option_4，但执行给定查询时，第二条结果的questionid为空，期望能正常显示201。

表结构及初始化数据：

create table test 
(id integer,
questionid character varying (255),
questionanswer character varying (255));

INSERT INTO test (id,questionid,questionanswer) VALUES
     (1,'[101,102,103]','[["option_11"],["KILA ALIPOKUWA ANAUMWA YEYE ALIJUWA NI AROSTO HALI YA KUKOSA KUVUTA MADAWA.."],["option_14"]]'),
     (2,'[201]','[["option_3","option_4"]]');

原查询语句：

SELECT *, 
replace(unnest(string_to_array(translate(questionid, '],[ ', '","'), ',' ))::text,'"','') as questionid,
replace(unnest(string_to_array(translate(questionanswer, '],[ ', '","'), ',' ))::text,'"','') as questionanswer
from test;

在SELECT中同时对两个数组执行unnest时，PostgreSQL会按数组元素数量逐行展开。如果两个数组长度不一致（比如id=2的记录中，questionid拆分为1个元素，questionanswer拆分为2个元素），短数组的元素耗尽后，后续行对应的短数组列就会显示为null。

可以通过LATERAL JOIN结合generate_series对齐两个数组的元素，确保每个questionanswer元素都能匹配到正确的questionid。以下是修正后的查询：

SELECT 
    t.id,
    -- 若questionid数组长度不足，重复使用第一个元素
    COALESCE(qid.val, qid_list[1]) AS questionid,
    qans.val AS questionanswer
FROM test t
-- 先把questionid转换为数组
CROSS JOIN LATERAL (
    SELECT string_to_array(translate(t.questionid, '[] ', ''), ',') AS qid_list
) qid_arr
-- 拆分questionanswer并生成带索引的行
CROSS JOIN LATERAL (
    SELECT 
        replace(unnest(string_to_array(translate(t.questionanswer, '[] ', ''), ',')), '"', '') AS val,
        generate_series(1, array_length(string_to_array(translate(t.questionanswer, '[] ', ''), ','), 1)) AS idx
) qans
-- 按索引关联questionid的元素
LEFT JOIN LATERAL (
    SELECT 
        replace(unnest(qid_arr.qid_list), '"', '') AS val,
        generate_series(1, array_length(qid_arr.qid_list, 1)) AS idx
) qid ON qid.idx = qans.idx;

也可以用unnest WITH ORDINALITY按位置关联，逻辑更直观：

SELECT
    t.id,
    CASE 
        WHEN qid.idx IS NOT NULL THEN qid.val
        ELSE (SELECT replace(unnest(string_to_array(translate(t.questionid, '[] ', ''), ',')), '"', '') LIMIT 1)
    END AS questionid,
    qans.val AS questionanswer
FROM test t
-- 拆分questionanswer并带上序号
CROSS JOIN LATERAL (
    SELECT 
        replace(unnest(string_to_array(translate(t.questionanswer, '[] ', ''), ',')), '"', '') AS val,
        ordinality AS idx
    FROM unnest(string_to_array(translate(t.questionanswer, '[] ', ''), ',')) WITH ORDINALITY
) qans
-- 拆分questionid并带上序号，按序号关联
LEFT JOIN LATERAL (
    SELECT 
        replace(unnest(string_to_array(translate(t.questionid, '[] ', ''), ',')), '"', '') AS val,
        ordinality AS idx
    FROM unnest(string_to_array(translate(t.questionid, '[] ', ''), ',')) WITH ORDINALITY
) qid ON qid.idx = qans.idx;

• 对于id=1的记录，questionid和questionanswer都拆分为3个元素，会一一对应显示；
• 对于id=2的记录，questionanswer拆分为2个元素，questionid只有1个，第二个元素会自动复用questionid的201，不会再出现null。

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