How to flat a subarray in Postgres?

Question

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}

@a_horse_with_no_name i can't change dataset, this is just a simplication — md123
– md123, Commented Sep 4, 2020 at 5:16
Then please show us the real data and what you really want to do (probably doing an UPDATE to change existing rows). Simplifying your question is a good thing, but simplifying it to a point where you won't get a helpful answer not so much — user330315
– user330315, Commented Sep 4, 2020 at 5:17

Mike Organek · Accepted Answer · 2020-09-04 11:53:27Z

3

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)

answered Sep 4, 2020 at 11:53

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

How to flat a subarray in Postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related