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!
3
Answers
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.
We can use
UNNEST
on both columns:See the documentation
Try out here: db<>fiddle
To make sure that matching array entries wind up on the same row, pass both arrays to a single unnest() call:
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:Online example