CODEWITHSUNDEEP
jsonpostgresql
Alexandre
Alexandre

Reputation: 3170

PostgreSQL table to json

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?

Upvotes: 3

Views: 2843

Answers (3)

pozs
pozs

Reputation: 36214

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:

Upvotes: 1

Marth
Marth

Reputation: 24802

PostgreSQL ≥ 9.4 :

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

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

Upvotes: 2

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31143

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.

Upvotes: 1

Related Questions