Grisha Ghukasyan Grisha Ghukasyan - 1 month ago 13
JSON Question

PostgreSQL - construct well formated json object from query

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 ! :)

Answer

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;
Comments