| title | categories | |
|---|---|---|
Unnest arrays from multiple rows |
|
Once you start moving off of the classic relational model in a sql database and start using arrays you keep needing to put an array back into a table when further dealing with the values.
A basic case is when using an array of values from your programming language into a sql query.
That's how I usually do it:
with users as (select id::uuid from unnest(?) as id)
select id from users;Another common usecase for arrays is when aggregating values in a group by. There you got to different groups bucketed in different arrays, and you may want to get a subset of them in table form:
with users as (select status, count(*), array_agg(id) as ids from users group by status)
select unnest(ids) from (select ids from users where status in ('active', 'inactive')) as t;It's a nice pattern to keep in mind, while reminding that functions can usually run in the "from" side, but also in the "select" side.