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

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 for id = 4:

Associations between questions and answers come out wrong. Output should be:

CodePudding user response：

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

• PostgreSQL unnest() with element number

And use json_array_elements_text(). See:

• How to turn JSON array into Postgres array?

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.