PostgreSQL数组（row_to_json（））：如何阻止array（）函数添加“引用字符串和转义\现有”引号

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

尝试将格式正确的JSON对象作为单个字段结果返回。

row_to_json() works very nicely but returns multiple rows.

row_to_json()工作得非常好但返回多行。

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:

生成的行是格式良好的JSON,但每行一个:

{"v":"000"}

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

然后我想将结果集处理成一行/ col结果:

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.

这实现了期望的结果。但是,它在每个JSON对象周围添加双引号,并转义格式正确的JSON对象中的现有引号。

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

1 个解决方案

#1

You can use json_agg() to aggregate the results:

您可以使用json_agg()来聚合结果:

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.

我认为这是你想要的,因为它是JSON数组的正确表示法。

#1