PostgreSQL select into JSON array

Question

Having a table named Example with a column named Value and some example data in it value1, value2, value3.

How can I select the values in the table into a JSON array?

[ "value1", "value2", "value3" ]

@SamiKuhmonen Using `json_agg` does not work. The result should be a list of strings, not a list of objects containing those strings. — Răzvan Flavius Panda, Mar 26 '19 at 13:02
How did you use it? If you give it strings it will give you an array of strings — Sami Kuhmonen, Mar 26 '19 at 13:02
@RăzvanFlaviusPanda it will work. check a [DB-Fiddle](https://www.db-fiddle.com/f/t7Ltzn2YL9SsLBgMy7tRbg/0) from Christoph answer — Cid, Mar 26 '19 at 13:06
My bad, I was aggregating on the entire table instead of on the column. And that was returning stuff like: `[{"Value":"value1"}, {"Value":"value2"}, {"Value":"valu3"}]` — Răzvan Flavius Panda, Mar 26 '19 at 13:13
Check [this](https://stackoverflow.com/questions/24006291/postgresql-return-result-set-as-json-array/24006432) this seems to be a good question about that topic — Cid, Mar 26 '19 at 13:13

score 4 · Accepted Answer · answered Mar 26 '19 at 13:04

create table example ( value text );
insert into example values ('value1'), ('value2'), ('value3');
select json_agg(value) from example;
            json_agg            
────────────────────────────────
 ["value1", "value2", "value3"]

PostgreSQL select into JSON array

1 Answers1