How to flat a subarray in Postgres?

select array[[1,2],[2,3]]

Output: -[ RECORD 1 ]-------- array | {{1,2},{2,3}}

How do I flat the array, so I can then unnest?

Expected {1, 2, 2, 3}

Answer

unnest() completely flattens the array. If you need the flattened array, then unnest() and follow with an array_agg()

select array_agg(el order by rn)
  from unnest(array[array[1,2],array[2,3]]) 
         with ordinality as a(el, rn);

 array_agg 
-----------
 {1,2,2,3}
(1 row)