Search code examples
jsonpostgresql

Construct json object from query with group by / sum

I have a Postgresql table which looks like this :

ID   CURRENCY    PRICE
1    EUR         100
2    EUR         650
3    USD         90

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

The value is the sum of each row with the same currency.

I tried to do it with json_object_agg :

SELECT
  json_object_agg(currency, SUM(amount)) AS balance
FROM business_balances GROUP BY currency;

but I have an sql error :

ERROR: aggregate function calls cannot be nested

Any idea how can I do it? :)

Solution

  • Smth like should work:

    with balances_by_currency as (
  SELECT distinct currency, SUM(amount) over (partition by currency) AS balance
  FROM business_balances
)
SELECT
  json_object_agg(currency, balance)
FROM balances_by_currency GROUP BY currency;