Question 90125 in PostgreSQL Question posted in
The official documentation can be found here.

How to split array data to next row in Postgres – Postgresql

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.
enter image description here

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    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');

    enter image description here


  2. 0

    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       |
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search