I guess I oversimplified the toy example. In the real query I need grouping/aggregation for some things, but global aggregation for others. Thank you I will look into unnest. -Adam
On Monday, April 25, 2022 at 11:38:18 PM UTC-6 [email protected] wrote: > > https://www.h2database.com/html/functions.html#unnest > > This one. > > > Sent from my Galaxy > > > -------- Original message -------- > From: Adam R <[email protected]> > Date: 26/04/2022 05:59 (GMT+01:00) > To: H2 Database <[email protected]> > Subject: [h2] Is it possible to flatten arrays when doing nested array > aggregation? > > Here's a toy example: > > CREATE TABLE MY_TABLE (ID INT, NAME VARCHAR); > INSERT INTO MY_TABLE VALUES(1, 'a'); > INSERT INTO MY_TABLE VALUES(2, 'a'); > INSERT INTO MY_TABLE VALUES(3, 'b'); > INSERT INTO MY_TABLE VALUES(4, 'b'); > > SELECT ARRAY_AGG(IDS) AS IDS FROM > (SELECT ARRAY_AGG(ID) AS IDS FROM MY_TABLE GROUP BY NAME) > > This will return [[1, 2], [3, 4]]. What I'd like is a way to flatten the > inner arrays so that I end up with a single array, like [1, 2, 3, 4]. Is > this possible in H2? (In a way that would would work for an arbitrary > number of array elements). > > Thank you. > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/f9308cf3-2679-4652-94e7-ad48795f07c8n%40googlegroups.com > > <https://groups.google.com/d/msgid/h2-database/f9308cf3-2679-4652-94e7-ad48795f07c8n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/b4bdefdf-8e3c-4ae2-bfbc-c66b46c30b3en%40googlegroups.com.
