CODEWITHSUNDEEP
arraysjsonpostgresql
Threadid
Threadid

Reputation: 750

PostgreSQL array(row_to_json()): How to stop array() functions from adding " quotes to strings and escape \ existing " quotes

Trying to return a properly formatted JSON object as a single field result.

row_to_json() works very nicely but returns multiple rows.

SELECT row_to_json(ages) FROM (SELECT DISTINCT RIGHT('000' || TRIM(age),3) AS v FROM uspopulation GROUP BY v ORDER BY v) AS ages

The resulting rows are nicely formatted JSON but one per row:

{"v":"000"}

Then I want to process that result set into an array in a single row/col result:

SELECT array(
SELECT row_to_json(ages) FROM (SELECT DISTINCT RIGHT('000' || TRIM(age),3) AS v FROM uspopulation GROUP BY v ORDER BY v) AS ages
);

This achieves the desired result. However, it adds double quotes around each JSON object and escapes the existing quotes in the properly formatted JSON object.

{"{\"v\":\"000\"}","{\"v\":\"001\"}","{\"v\":\"002\"}"}

So how do I turn off this behavior so I get a result like this:

{{"v":"000"},{"v":"001"},{"v":"002"}}

Upvotes: 1

Views: 1380

Answers (1)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31143

You can use json_agg() to aggregate the results:

SELECT json_agg(ages) FROM
  (SELECT DISTINCT RIGHT('000' || TRIM(age),3) AS v
   FROM uspopulation GROUP BY v ORDER BY v) AS ages;

This will give the correct output. Do note, that the output is not as you described, since it's an array:

[{"v":"000"},{"v":"001"},{"v":"002"}]

I assume this is what you want, since it is the correct notation for a JSON array.

Upvotes: 2

Related Questions