Grisha Ghukasyan Grisha Ghukasyan - 1 year ago 106
JSON Question

PostgreSQL - construct well formated json object from query

I have a Postgresql table which looks like this :

1 EUR 100
2 USD 90

I want to do a query that returns a JSON object formatted like this:
"EUR": 100,
"USD": 90

I succeeded by using json_object but I have to cast my integer values into TEXT.

SELECT json_object(
array_agg(cast(prices.price as text))
) FROM prices;

So if you have a better idea, I'm taking it ! :)

Answer Source

Use json_object_agg():

with data (id, currency, price) as (
    (1, 'EUR', 100),
    (2, 'USD', 90)
select json_object_agg(currency, price)
from data;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download