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?
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)
