Re: [h2] Is it possible to flatten arrays when doing nested array aggregation?

2022-04-26 Thread Adam R 
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 
and...@manticore-projects.com wrote:

>
> https://www.h2database.com/html/functions.html#unnest
>
> This 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...@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/b4bdefdf-8e3c-4ae2-bfbc-c66b46c30b3en%40googlegroups.com.

RE: [h2] Is it possible to flatten arrays when doing nested array aggregation?

2022-04-25 Thread andreas 
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?

2022-04-25 Thread andreas 
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.