RE: [h2] Is it possible to flatten arrays when doing nested array aggregation?
https://www.h2database.com/html/functions.html#unnestThis one.Sent from my Galaxy Original message From: Adam R Date: 26/04/2022 05:59 (GMT+01:00) To: H2 Database 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 h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f9308cf3-2679-4652-94e7-ad48795f07c8n%40googlegroups.com. -- 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 h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1MdwRi-1oGdF51uRa-00b4U4%40mrelayeu.kundenserver.de.
RE: [h2] Is it possible to flatten arrays when doing nested array aggregation?
Greetings. Why would you not aggregate on the whole table without the subquery?Anyway. If the subquery must be involved then you needed to unroll its arrays first before aggregating it again.Look for the unnest syntax in h2. I know its supported.Best regards AndreasSent from my Galaxy Original message From: Adam R Date: 26/04/2022 05:59 (GMT+01:00) To: H2 Database 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 h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f9308cf3-2679-4652-94e7-ad48795f07c8n%40googlegroups.com. -- 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 h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1N17pC-1nteEr2ggc-012Xti%40mrelayeu.kundenserver.de.
[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 h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f9308cf3-2679-4652-94e7-ad48795f07c8n%40googlegroups.com.
