Something like this:
SELECT Array_Agg( a )
FROM ( SELECT ARRAY [ 1, 2, 3] a
UNION
SELECT ARRAY [ 2, 3, 4] a )
;
Unfortunately, H2 does not seem to support any Flatten or Concat-
Aggregate function for arrays.
And UNNEST() is not deep.
So I am indeed not sure if this can be currently done with H2, sorry.
On Sat, 2024-07-20 at 18:04 +0700, Andreas Reichel wrote:
> Greetings!
>
> Would you not need to Aggregate the Array Columns first e.g. using
> `ARRAY_AGG` (or a similar function) to get 1 huge array and then to
> unnest it?
> Also, there should be an array function making the array itself
> distinct already.
>
> Best regards
> Andreas
>
>
> On Sat, 2024-07-20 at 10:46 +0000, 'Peter Borissow' via H2 Database
> wrote:
> > Hello,
> > I have a simple query in PostgreSQL that I am trying to emulate
> > in H2:
> > SELECT DISTINCT(unnest(tags)) as tags FROM company;
> >
> > The "tags" column is a varchar array. The query returns a unique
> > list of tags from the "tags" column.
> >
> > When I try to run the same query in H2 2.2.224, I get a Function
> > "unnest" not found error
> >
> > I can get a unique list of tags from a single record like this:
> > SELECT distinct(tags) FROM UNNEST(select tags from company where
> > id=1) as t(tags);
> >
> > But I'm struggling to come up with a query to get a unique list of
> > tags from multiple records. Any suggestions?
> >
> > Thanks in advance,
> > Peter
> >
--
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/f94cbaf8e4feb47a9b0f48a59651d23cda0903c2.camel%40manticore-projects.com.
