postgresql agg function that returns an array with single quotes around items

Question

I have this very simple query which it returns an array of municipality identifiers, that separates them with a comma and has double quotes around each item. I want to be able to surround these double quotes with single quotes

select json_agg(ssn) from sde.muns

Doesn't that make it invalid JSON? Or do you want the result as a string? — Laurenz Albe
– Laurenz Albe, Commented Feb 8, 2018 at 19:44
That's not an array. It's a json object that contains a json array. Are you maybe looking for string_agg() instead? — user330315
– user330315, Commented Feb 8, 2018 at 19:59

Vao Tsun · Accepted Answer · 2018-02-08 20:59:15Z

1

lets say you have:

t=# with c(v) as (select * from generate_series(1020,1029))
select json_agg(v::text) from c;
                                     json_agg
----------------------------------------------------------------------------------
 ["1020", "1021", "1022", "1023", "1024", "1025", "1026", "1027", "1028", "1029"]
(1 row)

so you can:

t=# with c(v) as (select * from generate_series(1020,1029))
select translate(json_agg(v::text)::text,'"',$$'$$) from c;
                                    translate
----------------------------------------------------------------------------------
 ['1020', '1021', '1022', '1023', '1024', '1025', '1026', '1027', '1028', '1029']
(1 row)

which of course is not valid json any more. And I assumed you can have only numbers, so no accidental double quote to take care of

answered Feb 8, 2018 at 20:59

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

postgresql agg function that returns an array with single quotes around items

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