Postgres中的Array_agg选择性引用 [英] Array_agg in postgres selectively quotes

问题描述

我有一个复杂的数据库，其中的键和值存储在不同的表中.提取应用程序的值时，像这样聚集它们对我来说是有用的:

I have a complex database with keys and values stored in different tables. It is usef for me to aggregate them like so when pulling out the values for the application:

   SELECT array_agg(key_name), array_agg(vals)
                    FROM (
                        SELECT
                            id,
                            key_name,
                            array_agg(value)::VARCHAR(255) AS vals
                        FROM factor_key_values
                        WHERE id=20
                        GROUP BY key_name, id
                    ) f;

在我的情况下，此特定查询给出了以下无效的json:

This particular query, in my case gives the following invalid json:

-[ RECORD 1 ]-----------------------------------------------------------------------  
array_agg | {"comparison method","field score","field value"} 
array_agg | {"{\"text category\"}","{100,70,50,0,30}","{A,B,C,F,\"No Experience\"}"}

请注意，只有在字符串包含空格的情况下，才引用varchars数组.我将其范围缩小为ARRAY_AGG的行为.为完整起见，下面是一个示例:

Notice that the array of varchars is only quoted if the string has a space. I have narrowed this down to the behaviour of ARRAY_AGG For completeness here is an example:

BEGIN;
CREATE TABLE test (txt VARCHAR(255));
INSERT INTO test(txt) VALUES ('one'),('two'),('three'), ('four five');
SELECT array_agg(txt) FROM test;

结果将是:

{one,two,three,"four five"}

这就是为什么我的json损坏的原因.我可以处理应用程序代码中未加引号或字符串的字符串，但有一些混淆.有什么解决办法吗?

This is why my json is breaking. I can handle unquoted or quoted string in the application code, but have a mix in nuts. Is there any solution to this?

推荐答案

您不能使用json_agg吗?

select json_agg(txt) from test;
               json_agg               
--------------------------------------
 ["one", "two", "three", "four five"]

这篇关于Postgres中的Array_agg选择性引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！