How to split array data to next row in Postgres

Question

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

_id	questionid	answer	subquestionid	subquestionanswer	isactive
1	question 1	true	[100,101,102]	[[true],[false],[true]]	1
2	question 2	false	[101,106]	[[false],[true]]	1

Output should be needed.

isActive should be a boolean and subquestionid and subquestionanswer should be normalized into a one-to-many relationship. But at least those columns should be text[] or maybe combined into a single jsonb where the ID is the key and the flag is the value. — user330315
– user330315, Commented Jan 16, 2023 at 12:54
output is not available in the link. please share in the question body. — Kazi Mohammad Ali Nur Romel
– Kazi Mohammad Ali Nur Romel, Commented Jan 16, 2023 at 13:10

Marmite Bomber · Accepted Answer · 2023-01-16 13:29:56Z

1

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       |

answered Jan 16, 2023 at 13:29

Marmite Bomber

21.3k4 gold badges31 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sahil Over a year ago

Getting error while data in below format. insert query for new data. INSERT INTO test1 (questionid,answer,subquestionid,subquestionanswer,isactive) values ('question 3','true','[151,152,153,154]','[["option1"],["option1"],["option1","option2"],["option3"]]','1');

Marmite Bomber Over a year ago

This is worth a new question along with the explanation what do you expect as an output for 153 - one or two lines? @Srk

SelVazi · Accepted Answer · 2023-01-16 14:02:45Z

1

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

answered Jan 16, 2023 at 14:02

SelVazi

16.2k2 gold badges19 silver badges35 bronze badges

1 Comment

Marmite Bomber Over a year ago

Well, unnest does guarantee to preserve the order, but the the join with the unnested table not, so for larger example you may get messed with the order of the rows in the row_number (with ordinaltiy was invented for this purpose - see the other answer)

sahil · Accepted Answer · 2023-01-17 09:22:00Z

0

Thank you for the reply!

Below table data needs to convert into screenshot format. Earlier there was one bracket one value, now there are one bracket two values. (subquestionid-153). I have extract this data from Talend.

create table test1 (_id serial, questionId character varying (50), answer character varying (50), subquestionId character varying (50), subquestionAnswer character varying (225), 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');

answered Jan 17, 2023 at 9:22

sahil

1191 silver badge8 bronze badges

Collectives™ on Stack Overflow

How to split array data to next row in Postgres

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related