Postgresql - return json array in select query instead of comma separated values

Question

I have the following query:

`SELECT * FROM reports
        WHERE id = ${req.params.id}`

the column type has the following value: "item1,item2,item3".

I want to return it as a JSON array: ["item1","item2","item3"]

I tried this:

`SELECT *, string_to_array(type, ',') AS type FROM reports
        WHERE id = ${req.params.id}`

but I still get it as a plain comma separated string.

Is it possible to do this or I have to convert it manually on the server and not on the query itself?

Answer 1

Pass the array as argument to to_jsonb():

SELECT to_jsonb(string_to_array(type, ',')) AS type
FROM reports

JSON support was introduced in Postgres 9.3 (json) and 9.4 (jsonb). In the older versions you can try to build a string representing a json array, e.g.:

with report(type) as (
    values ('item1,item2,item3')
)

select '[' || regexp_replace(type, '([^,]+)', '"\1"', 'g') || ']' as type
from report

           type            
---------------------------
 ["item1","item2","item3"]
(1 row)