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?

Answer 1

PostgreSQL ≥ 9.4 :

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

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

Answer 2

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.