修复PostgreSQL中JSON数组拆分后问答关联错误的问题

需要将存储为JSON数组格式的questionid和questionanswer拆分成行并正确关联。当前使用的SQL在处理id=4的数据时，问答关联关系错误，需修正查询以保证对应关系正确。

示例表结构与数据 #

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"],["Test message 1"],["option_14"]]'),
(2,'[201]','[["option_3","option_4"]]'),
(3,'[301,302]','[["option_1","option_3"],["option_1"]]'),
(4,'[976,1791,978,1793,980,1795,982,1797]','[["option_2","option_3","option_4","option_5"],["Test message"],["option_4"],["Test message2"],["option_2"],["Test message3"],["option_2","option_3"],["Test message4"]]');

当前错误查询语句 #

select t.id, t1.val, v1#>>'{}' from test t 
cross join lateral (select row_number() over (order by v.value#>>'{}') r, v.value#>>'{}' val 
   from json_array_elements(t.questionid::json) v) t1
join lateral (select row_number() over (order by 1) r, v.value val 
   from json_array_elements(t.questionanswer::json) v) t2 on t1.r = t2.r
cross join lateral json_array_elements(t2.val) v1;

当前错误输出 #

期望正确输出 #

修正后的SQL查询 #

select t.id, t1.val, v1#>>'{}'
from test t 
cross join lateral (
    select row_number() over (order by (select null)) r, v.value#>>'{}' val 
    from json_array_elements(t.questionid::json) v
) t1
join lateral (
    select row_number() over (order by (select null)) r, v.value val 
    from json_array_elements(t.questionanswer::json) v
) t2 on t1.r = t2.r
cross join lateral json_array_elements(t2.val) v1;

修正说明 #

原查询错误的核心原因是：对questionid生成行号时使用了order by v.value#>>'{}'，这会将questionid的数值按字符串顺序排序（比如字符串"1791"会排在"976"前面），而questionanswer的行号是按数组原始顺序生成的，导致两者的行号无法正确对应。

修正后，对两个数组的行号生成都使用order by (select null)，确保行号严格按照元素在原始JSON数组中的位置生成，这样questionid和questionanswer的元素就能一一对应，最终得到正确的关联结果。

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