Split bracket data into row in postgres-CodePudding

I have a data in table. The questionid 201 belongs to option_3 and option_4 but when the following query is used it returns null for second output. It Should be display 201 as well.

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"]]');

--my query

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

CodePudding user response：

Using a series of cross joins:

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

See fiddle.