PostgreSQL: Create array by grouping values of the same id

Question

Given the following input data:

I aim aiming to get the following output table:

using the following query:

SELECT DISTINCT id,
                ARRAY(SELECT DISTINCT category::VARCHAR FROM test) AS categories
FROM my_table

But what I get is the following table:

How can I obtain the desired output?

Note: The GROUP BY clause did not work in this case as I'm not using an aggregation function.

lemon · Accepted Answer · 2022-06-15 13:48:39Z

4

What about using the JSON_AGG aggregation function?

SELECT id,
       JSON_AGG(category) AS category
FROM tab
GROUP BY id
ORDER BY id

Check the demo here.

answered Jun 15, 2022 at 13:48

lemon

15.6k6 gold badges23 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

I would like to keep it as an array as I then need to compare the content between two arrays...

then you can use ARRAY_AGG, which returns an array, does it work for you?

Kadet · Accepted Answer · 2022-06-15 14:00:57Z

1

Assuming table has name test

select distinct id,
     array(select distinct category::varchar from test b where b.id = a.id) as categories
from test a

answered Jun 15, 2022 at 14:00

Kadet

1,4195 silver badges11 bronze badges