CODEWITHSUNDEEP
jsonpostgresql
Grisha Ghukasyan
Grisha Ghukasyan

Reputation: 100

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

Upvotes: 1

Views: 121

Answers (1)

klin
klin

Reputation: 121474

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;

Upvotes: 3

Related Questions