PostgreSQL table to json

Question

I have a table

 key | value
-----+-------
 a   | "foo"
 b   | "bar"

How can I get the result

{"a": "foo", "b": "bar"}

It doesn't work with

select array_to_json(array_agg(t)) from table t; -- I get  [{"key": "a", "value": "foo"}, {"key": "b", "value": "bar"}]

Can you help me?

http://dba.stackexchange.com/questions/90482/export-postgres-table-as-json — svenhornberg, Apr 07 '15 at 09:51
@fuubah Not the same question. In that question the table data was to be output with the column names. Here the column names don't matter, but the data in the table. — Sami Kuhmonen, Apr 07 '15 at 09:52
Possibly related: http://stackoverflow.com/questions/20069143/postgres-inverse-of-json-each — dhke, Apr 07 '15 at 10:02

Marth · Accepted Answer · 2015-04-07T10:11:10.040

PostgreSQL ≥ 9.4 :

SELECT json_object(array_agg(key), array_agg(value))
FROM  t;     

┌────────────────────────────┐
│        json_object         │
├────────────────────────────┤
│ {"a" : "foo", "b" : "bar"} │
└────────────────────────────┘
(1 row)

score 1 · Answer 2 · answered Apr 07 '15 at 10:02

If you are using PostgreSQL 9.4 you can use the following code to take key from one column and value from another and create a single JSON object:

 select json_object(array_agg(key), array_agg(value)) from table;

For earlier versions I don't know of a simple method at this time.

score 1 · Answer 3 · edited May 23 '17 at 12:06

For PostgreSQL 9.4+, there is an aggregate function specially for this:

SELECT json_object_agg(key, value)
FROM   t

This avoids the cost of creating 2 PostgreSQL arrays.

For older PostgreSQL versions, you need to concatenate your result yourself:

SELECT concat('{', string_agg(to_json(key) || ':' || to_json(value), ','), '}')::json
FROM   t

More details:

How do I modify fields inside the new PostgreSQL JSON datatype?

PostgreSQL table to json

3 Answers3