PostgreSQL: Create array by grouping values of the same id

Question

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.

Answer 1

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.

Answer 2

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