PostgreSQL - construct well formated json object from query

Question

I have a Postgresql table which looks like this :

ID   CURRENCY    PRICE
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(prices.currency),
   array_agg(cast(prices.price as text))
) FROM prices;

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

klin · Accepted Answer · 2016-10-10 11:07:31Z

3

Use json_object_agg():

with data (id, currency, price) as (
values
    (1, 'EUR', 100),
    (2, 'USD', 90)
)
select json_object_agg(currency, price)
from data;

answered Oct 10, 2016 at 11:07

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, that's exactly what I was looking for :)