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

Question

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"}}

Sami Kuhmonen · Accepted Answer · 2015-05-10 06:24:37Z

2

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.

answered May 10, 2015 at 6:24

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related