SQL convert array column to rows with old keys in arrays

Question

I have a table in PostgreSQL, with two columns: id and nodes. id is of type bigint, as it is the primary key in the table. nodes is an array of bigint. I would like to create a new table, where the nodes in the node arrays are the keys and the only other column is an array of ids that correspond to that node.

So let's say the original table is like this:

id | nodes
1  | {200, 300, 400}
2  | {200, 300, 400}

then the new table should be like:

node | ids
200  | {1, 2}
300  | {1, 2}
400  | {1, 2}

S-Man · Accepted Answer · 2018-09-28 13:04:01Z

4

demo: db<>fiddle

SELECT node, array_agg(id) as ids
FROM (
    SELECT id, unnest(nodes) as node 
    FROM nodes
)s 
GROUP BY node
ORDER BY node

unnest() expands the array into one row per element.

Then you are able to GROUP by the array elements and aggregate the ids with array_agg

In your case this can be shortened to a query without any subquery or join:

SELECT unnest(nodes) as node, array_agg(id) as ids 
FROM nodes
GROUP BY node
ORDER BY node

edited Sep 28, 2018 at 13:04

answered Sep 28, 2018 at 12:26

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2018-09-28 12:42:46Z

1

You can unnest and re-aggregate. No subquery is necessary:

with t as (
      select 1 as id, array[200, 300, 400] as nodes union all
      select 2 as id, array[200, 300, 400] as nodes 
     )
select node, array_agg(t.id)
from t cross join
     unnest(nodes) node
group by node

answered Sep 28, 2018 at 12:42

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL convert array column to rows with old keys in arrays

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related