Skip to content
Advertisement

PostgreSQL: Create array by grouping values of the same id

Given the following input data:

id category
1 A
1 B
2 A
2 R
2 C
3 Z

I aim aiming to get the following output table:

id categories
1 {“A”,”B”}
2 {“A”,”R”,”C”}
3 {“Z”}

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:

id categories
1 {“A”,”B”,”R”,”C”,”Z”}
2 {“A”,”B”,”R”,”C”,”Z”}
3 {“A”,”B”,”R”,”C”,”Z”}

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.

Advertisement

Answer

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.

Advertisement