Array_agg in postgres selectively quotes

Question

I have a complex database with keys and values stored in different tables. It is useful for me to aggregate them 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;

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

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;

The result will be:

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

This is why my JSON is breaking. I can handle unquoted or quoted strings in the application code, but have a mix in nuts.

Is there any solution to this?

Clodoaldo Neto · Accepted Answer · 2017-04-06 17:53:13Z

13

Can't you use json_agg?

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

answered Apr 6, 2017 at 17:53

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

3 Comments

Frank Conry Over a year ago

I tried this but the thing that its a rather large query that this is a subselect in, so if I json encode on an inner selected I end up double json encoded.

Clodoaldo Neto Over a year ago

@FrankConry So go back to basics and show sample data and expected output.

Frank Conry Over a year ago

I ended up having to refactor the entire query, but in the end, json_agg was the right choice. Thanks!

the Tin Man · Accepted Answer · 2019-11-27 00:42:26Z

0

Unfortunately, this is the inconsistent standard that PostgreSQL uses for formatting arrays. See "Array Input and Output Syntax" for more information.

Clodoaldo's answer is probably what you want, but as an alternative, you could also build your own result:

SELECT '{'||array_to_string(array_agg(txt::text), ',')||'}' FROM test;

edited Nov 27, 2019 at 0:42

the Tin Man

161k44 gold badges222 silver badges308 bronze badges

answered Apr 6, 2017 at 18:00

Brad Parker

1116 bronze badges

Collectives™ on Stack Overflow

Array_agg in postgres selectively quotes

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related