How to split two columns array into rows in Postgresql

Question

When running this query:

SELECT id,col1,col2
  FROM  app_data.content_cards

I get a table like this:

+----+-------------------------------+--------+
| id | col1                          | col2   |
+----+-------------------------------+--------+
| 90 | {'one', 'two', 'three'}       | {1,2,3}|
+----+-------------------------------+--------+
| 91 | {'abc', 'def'}                | {1,2}  |
+----+-------------------------------+--------+
| 92 | {'asdf}                       | {1}    |
+----+-------------------------------+--------+

What I want to do now is get this same information but with the arrays splitted into rows so I get a result like this:

+----+---------------------+-------+
| id | col1                | col2  |
+----+---------------------+-------+
| 90 | one                 | 1     |
+----+---------------------+-------+
| 90 | two                 | 2     |
+----+---------------------+-------+
| 90 | three               | 3     |
+----+---------------------+-------+
| 91 | abc                 | 1     |
+----+---------------------+-------+
| 91 | def                 | 2     |
+----+---------------------+-------+
| 92 | asdf                | 1     |
+----+---------------------+-------+

As you can see I don't want to get rows with null value in "col1" and "col2".

Many thanks!

Jonxag · Accepted Answer · 2023-01-18 07:59:33Z

2

Assuming col1 and col2 got the same number of elements , you can make a view with the UNNEST function to split it and then query it.

WITH unnested_col1 AS (
  SELECT id, unnest(col1) as col1, ROW_NUMBER() OVER (PARTITION BY id ORDER BY unnest(col1)) as row_num
  FROM app_data.content_cards
), unnested_col2 AS (
  SELECT id, unnest(col2) as col2, ROW_NUMBER() OVER (PARTITION BY id ORDER BY unnest(col2)) as row_num
  FROM app_data.content_cards
)
SELECT unnested_col1.id, unnested_col1.col1, unnested_col2.col2
FROM unnested_col1
JOIN unnested_col2 ON unnested_col1.id = unnested_col2.id AND unnested_col1.row_num = unnested_col2.row_num

answered Jan 18, 2023 at 7:59

Jonxag

7877 silver badges22 bronze badges

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

Comments

Jonas Metzler · Accepted Answer · 2023-01-18 08:02:27Z

2

We can use UNNEST on both columns:

SELECT id,
UNNEST(col1) AS col1,
UNNEST(col2) AS col2
FROM content_cards
ORDER BY id;

See the documentation

Try out here: db<>fiddle

answered Jan 18, 2023 at 8:02

Jonas Metzler

6,8363 gold badges9 silver badges24 bronze badges

Comments

score 2 · Accepted Answer · 2023-01-18 08:13:07Z

2

To make sure that matching array entries wind up on the same row, pass both arrays to a single unnest() call:

SELECT c.id, u.*
FROM content_cards c
    cross join unnest(col1, col2) as u(col1, col2)
ORDER BY id;

This guarantees that elements from matching array positions wind up in the same row even if one array has a different number of elements (the missing ones will be filled with null values).

If it's possible that some arrays have not elements at all but you still want to see the id of the base table, use an outer join:

FROM content_cards c
   left join unnest(col1, col2) as u(col1, col2) on true

Online example

edited Jan 18, 2023 at 8:13

answered Jan 18, 2023 at 8:07

user330315

Collectives™ on Stack Overflow

How to split two columns array into rows in Postgresql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related