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.