Split multi bracket data into row in postgres

I need to split data from bracket to row format.
If there is data in multi bracket, it does not update properly using below query.(id 4)
Following example

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 query
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;

Current query output.

id val ?coloumn?
4 1791 option_2
4 1791 option_3
4 1791 option_4
4 1791 option_5
4 1793 Test message
4 1795 option_4
4 1797 Test message2
4 976 option_2
4 978 Test message3
4 980 option_2
4 980 option_3
4 982 Test message4

Output should be below require.

id val ?coloumn?
4 976 option_2
4 976 option_3
4 976 option_4
4 976 option_5
4 1791 Test message
4 978 option_4
4 1793 Test message2
4 980 option_2
4 1795 Test message3
4 982 option_2
4 982 option_3
4 1797 Test message4

>Solution :

Most importantly, use WITH ORDINALITY instead of row_number() and join unnested questions and answers on their ordinal positions. See:

And use json_array_elements_text(). See:

SELECT t.id, qa.q_id
     , json_array_elements_text(qa.answers) AS answer
FROM   test t
CROSS  JOIN LATERAL (
   SELECT *
   FROM   json_array_elements_text(t.questionid::json) WITH ORDINALITY q(q_id, ord)
   JOIN   json_array_elements(t.questionanswer::json)  WITH ORDINALITY a(answers, ord) USING (ord)
   ) qa
ORDER  BY t.id, qa.ord;

fiddle

Aside: you should probably store JSON values as type json (or jsonb) to begin with.

Leave a Reply