PostgreSQL: Create array by grouping values of the same id-CodePudding

Imagine the data:

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

I aim aiming for the output:

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

But when using the code:

select distinct id,
     array(select distinct category::varchar from test) as categories
from my_table

I get:

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? Group by did not work in this case as I'm not using an aggregation function.

CodePudding user response：

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.

CodePudding user response：

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