CODEWITHSUNDEEP
jsonpostgresql
Grisha Ghukasyan
Grisha Ghukasyan

Reputation: 100

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

Upvotes: 4

Views: 2931

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51466

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;

Upvotes: 6

Robin Koch
Robin Koch

Reputation: 726

Do the first aggregation (sum) in a subquery:

SELECT json_object_agg(currency, sum) AS balance
FROM (SELECT currency, sum(amount)
      FROM business_balances GROUP BY currency
     ) as sums;

Upvotes: 2

Related Questions