How to get distinct values from an array column?

[Peter Borissow]

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

[Andreas Reichel]

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

--
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/943944094.2430433.1721472409881%40mail.yahoo.com.

[Andreas Reichel]

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.

[Peter Borissow]

Yes, I tried that but ARRAY_AGG is creating an array of arrays rather than a single array. Maybe I'm calling it wrong?

select ARRAY_AGG(tags) from company

When I unnest the aggregation I'm back to square one :-(

In other words this:

select * from unnest((select ARRAY_AGG(tags) from company));

Is the same as this:

select tags from company;

[Evgenij Ryazanov]

Hello!

That syntax with UNNEST is PostgreSQL-specific.

You can use a recursive query (this query assumes presence of primary key id column):

with recursive cte(i, s) as (

(select id, tags from company order by id fetch first row only)

union all

(select company.id, s || tags from cte, company where company.id > i order by company.id fetch first row only)

)

select distinct v from unnest(select s from cte order by i desc fetch first row only) q(v);

If you need a more efficient implementation you can create an own specialized user-defined function:
https://h2database.com/html/commands.html#create_aggregate

[Peter Borissow]

That cte is a cool idea! In the end, I wrote my own function to get the tags.

Thanks for everyone's input.

Best,

Peter