Is it possible to flatten arrays when doing nested array aggregation?

[Adam R]

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.

[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 

Andreas

Sent from my Galaxy

--
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.

[andreas]

https://www.h2database.com/html/functions.html#unnest

This one.

Sent from my Galaxy

-------- Original message --------

From: Adam R <adam.j...@gmail.com>

Date: 26/04/2022 05:59 (GMT+01:00)

To: H2 Database <h2-da...@googlegroups.com>

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

--

[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