Split array(bigint) to multiple rows of bigint

I’m querying some data (SQL, presto), each item can be the parent or child of another. The parent IDs and the child IDs are stored in the row for that primary ID as an array(bigint). Each task could be the child of multiple parents.

It looks something like:

id | parent_ids | child_ids
1 | [3] | []
2 | [3] | []
3 | [] | [2,1]
4 | [] | [5]
5 | [4, 6] | []
6 | [] | [5]

I want a list of all of the parent Ids and each of the children as additional rows for that parent:

id | child
3 | 1
3 | 2
4 | 5
6 | 5

Any idea how I can achieve this?

Answer

I think you want:

select p.parent_id as id, t.id as child_id
from t cross join 
     unnest(t.parent_ids) p(parent_id)

Advertisement

Answer