Postgres unnest ignores array of arrays structure

Question

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.

For anyone curious, solving this subproblem is related to stackoverflow.com/questions/57263338/… — pir
– pir, Commented Jul 30, 2019 at 1:57

crvv · Accepted Answer · 2019-07-30 09:16:27Z

2

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[]));

answered Jul 30, 2019 at 9:16

crvv

6105 silver badges11 bronze badges

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

1 Comment

pir Over a year ago

Thanks! This looks great. How does it compare in speed with @S-Man's answer?

S-Man · Accepted Answer · 2019-07-30 12:01:35Z

1

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.

answered Jul 30, 2019 at 12:01

S-Man

24k9 gold badges51 silver badges78 bronze badges

2 Comments

pir Over a year ago

Thanks! This looks great. How does it compare in speed with @crvv's answer?

S-Man Over a year ago

The solution of crvv seems to be much faster (in this case, I am curious about real data) but does not give out an simple array but a json array. Comparison of speed (have a look at the costs) and output: dbfiddle.uk/…

Collectives™ on Stack Overflow

Postgres unnest ignores array of arrays structure

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related