I have data in a table. I want to split the array data into separated rows.

create table test1 (
  _id serial, 
  questionId character varying (50), 
  answer character varying (50), 
  subquestionId character varying (50), 
  subquestionAnswer character varying (50), 
  isActive character varying (1)
);

INSERT INTO test1 (questionid,answer,subquestionid,subquestionanswer,isactive) 
values 
('question 1','true','[100,101,102]','[[true],[false],[true]]','1'),('question 2','false','[101,106]','[[false],[true]]','1');

Output should be needed.

CodePudding user response：

As explained in this answer you may convert the string representation of arrays to real arrays with translate and cast. (As commented you should not store the arrays as strings)

select 
   _id, 
   answer,
   translate(subquestionid, '[]', '{}')::int[] subquestionid,
   translate(subquestionanswer, '[]', '{}')::boolean[] subquestionanswer, 
   isactive
from test1;

result

_id|answer|subquestionid|subquestionanswer      |isactive|
--- ------ ------------- ----------------------- -------- 
  1|true  |{100,101,102}|{{true},{false},{true}}|1       |
  2|false |{101,106}    |{{false},{true}}       |1       |

To split the arrays use unnest and keep the order using WITH ORDINALITY AS

Finaly limit the result to rows with the identical ordinality, which I assume is the expected result, though differing from your output

with t as (
select 
   _id, 
   answer,
   translate(subquestionid, '[]', '{}')::int[] subquestionid,
   translate(subquestionanswer, '[]', '{}')::boolean[] subquestionanswer, 
   isactive
from test1
)  
select   
 t._id, 
 t.answer,
 a.subquestionid, 
 b.subquestionanswer,
 t.isactive
from t
cross join lateral unnest(subquestionid) WITH ORDINALITY AS a(subquestionid, nr)
cross join lateral unnest(subquestionanswer) WITH ORDINALITY AS b(subquestionanswer, nr)
where a.nr = b.nr


_id|answer|subquestionid|subquestionanswer|isactive|
--- ------ ------------- ----------------- -------- 
  1|true  |          100|true             |1       |
  1|true  |          101|false            |1       |
  1|true  |          102|true             |1       |
  2|false |          101|false            |1       |
  2|false |          106|true             |1       |

CodePudding user response：

You can do it using string_to_array to convert string to array, and generate multiple rows from single one as follows :

SELECT *
FROM (
  SELECT t._id, t.questionid, t.answer, REGEXP_REPLACE(theAnswer, '\[|\]|\[\[|\]\]', '') as subquestionanswer, t.isactive
  FROM   test1 t, 
       unnest(string_to_array(subquestionanswer, '],[')) theAnswer
  ) S

this how it should be :

SELECT t._id, t.questionid, t.answer, s.subquestionid, t.subquestionanswer, t.isactive
FROM (
  SELECT _id, questionid, answer, REGEXP_REPLACE(subquestionans, '\[|\]|\[\[|\]\]', '') as subquestionanswer, isactive,
  ROW_NUMBER () OVER (
           ORDER BY _id
        )
  FROM   test1, 
       unnest(string_to_array(subquestionanswer, '],[')) subquestionans
) t
inner join (
  SELECT _id , REGEXP_REPLACE(subquestion, '\[|\]', '') as subquestionid,
  ROW_NUMBER () OVER (
           ORDER BY _id
        )
  FROM  test1 , 
       unnest(string_to_array(subquestionid, ',')) subquestion
) s on s.ROW_NUMBER = t.ROW_NUMBER;

Demo here : https://dbfiddle.uk/b1w3RyCJ