0

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.

pir
  • 5,513
  • 12
  • 63
  • 101
  • For anyone curious, solving this subproblem is related to https://stackoverflow.com/questions/57263338/continuously-insert-all-unique-combinations-encountered-of-three-ids – pir Jul 30 '19 at 01:57

2 Answers2

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[]));
crvv
  • 580
  • 4
  • 9
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.

S-Man
  • 22,521
  • 7
  • 40
  • 63
  • Thanks! This looks great. How does it compare in speed with @crvv's answer? – pir Jul 30 '19 at 18:33
  • 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: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=f9cd965d71ffac4944aa26aef2d5af85 – S-Man Jul 31 '19 at 05:41