CODEWITHSUNDEEP
arrayspostgresql
pir
pir

Reputation: 5923

Postgres unnest ignores array of arrays structure

These two Postgres function calls return the same, whereas I would expect the latter to retain its nested array structure. How do I do that?

SELECT * FROM unnest('{1, 10, 100, 2, 11, 101}'::integer[]);

SELECT * FROM unnest('{{1, 10, 100}, {2, 11, 101}}'::integer[]);

I need this for manipulating array of arrays.

Upvotes: 0

Views: 520

Answers (2)

S-Man
S-Man

Reputation: 23676

Using this solution: https://stackoverflow.com/a/8142998/3984221 from @LukasEklund and @ErwinBrandstetter

demo:db<>fiddle

SELECT array_agg(t.myarray[d1][d2])
FROM   mytable t,
       generate_subscripts(t.myarray,1) d1,
       generate_subscripts(t.myarray,2) d2
GROUP  BY d1
ORDER  BY d1

generate_subscript() generates a consecutive number list from 1 to the dimension size given by the second parameter.

Upvotes: 1

crvv
crvv

Reputation: 610

There is a wiki page about this. https://wiki.postgresql.org/wiki/Unnest_multidimensional_array

Besides, PostgreSQL has much more functions for JSON than arrays.

SELECT * FROM jsonb_array_elements(to_jsonb('{{1, 10, 100}, {2, 11, 101}}'::integer[]));

Upvotes: 2

Related Questions