Get aggregate sum of json array in Postgres NOSQL json data

Question

How to get aggregate SUM(amount) from "refunds" array in postgres json select Following is my data schema and structure:

Table Name: transactions

Column name: data

{
  "id": "tran_6ac25129951962e99f28fa488993",
  "amount": 1200,
  "origin_amount": 3900,
  "status": "partial_refunded",
  "description": "Subscription#sub_a67d59efb2bcbf73485a ",
  "livemode": false,
  "refunds": [
    {
      "id": "refund_ee4192ffb6d2caa490a1",
      "amount": 1200,
      "status": "refunded",
      "created_at": 1426412340,
      "updated_at": 1426412340,
    },
    {
      "id": "refund_0e4a34e4ee7281d369df",
      "amount": 1500,
      "status": "refunded",
      "created_at": 1426412353,
      "updated_at": 1426412353,
    }
  ]
}

Out put should be: 1200+1500 = 2700

Output
|---------
|total
|---------
|2700

Please provide global solution and not with static data

score 23 · Accepted Answer · answered Mar 15 '15 at 14:11

23

This should work on 9.3+

WITH x AS( SELECT
'{
  "id": "tran_6ac25129951962e99f28fa488993",
  "amount": 1200,
  "origin_amount": 3900,
  "status": "partial_refunded",
  "description": "Subscription#sub_a67d59efb2bcbf73485a ",
  "livemode": false,
  "refunds": [
    {
      "id": "refund_ee4192ffb6d2caa490a1",
      "amount": 1200,
      "status": "refunded",
      "created_at": 1426412340,
      "updated_at": 1426412340
    },
    {
      "id": "refund_0e4a34e4ee7281d369df",
      "amount": 1500,
      "status": "refunded",
      "created_at": 1426412353,
      "updated_at": 1426412353
    }
  ]
}'::json as y),
refunds AS(
SELECT json_array_elements(y->'refunds') as j FROM x)
SELECT sum((j->>'amount')::int) FROM refunds;

answered Mar 15 '15 at 14:11

Jakub Kania

15,665
2
37
47

Here json data is variable so can i use "data" means field name instead of fixed string. I m new with postgres, can you provide exact query with column "data" and dynamic, not with static – mukund Mar 15 '15 at 14:40
1

`refunds` CTE selects from x.y, if you want to select from tbl1.data just do so `WITH refunds AS( SELECT json_array_elements(data->'refunds') as j FROM tbl1) (...)` – Jakub Kania Mar 15 '15 at 15:52
I ran `WITH x AS( SELECT json_array_elements(data->'refunds') as y FROM transactions ), refunds AS( SELECT json_array_elements(y->'refunds') as j FROM x) SELECT sum((j->>'amount')::int) FROM refunds;` and got error `ERROR: cannot call json_array_elements on a scalar` Please let me know if i am wrong – mukund Mar 16 '15 at 00:36
@mukund It seems your data is not always like in the example. If you're on 9.4 try `SELECT json_array_elements(y->'refunds') as j FROM x WHERE son_typeof(y->'refunds') = 'array`. – Jakub Kania Mar 16 '15 at 09:10

score 3 · Answer 2 · answered Mar 23 '21 at 17:05

WITH AllRefunds AS ( SELECT jsonb_array_elements(data->'refunds') AS refund FROM transactions)
SELECT SUM( CAST ( refund ->> 'amount' AS INTEGER )) FROM AllRefunds;

If you need to know how the query is built:

1.

WITH AllRefunds AS ( SELECT jsonb_array_elements(data->'refunds') FROM transactions)
SELECT * FROM AllRefunds;

This selects all elements as JSON objects (done via ->) from the array refunds that were found in transactions table and stores it in a new table AllRefunds. This new table only consists of one unnamed column.

2.

WITH AllRefunds AS ( SELECT jsonb_array_elements(data->'refunds') AS refund FROM transactions)
SELECT * FROM AllRefunds;

Here the added (second) AS renames the currently unnamed column inside AllRefunds to refund

3.

WITH AllRefunds AS ( SELECT jsonb_array_elements(data->'refunds') AS refund FROM transactions)
SELECT SUM( CAST ( refund ->> 'amount' AS INTEGER )) FROM AllRefunds;

Our array entries are JSON objects. So we return the field amount as a simple string with ->> that we then cast to Integers and SUM all entries up.

score 0 · Answer 3 · answered May 09 '23 at 10:19

0

select sum(coalesce(trrefunds.refunds->>'amount','0')::int) as total from(
select  jsonb_array_elements(refunds) as refunds
from  transactions) as trrefunds

answered May 09 '23 at 10:19

Nitesh Malviya

794
1
9
17

Get aggregate sum of json array in Postgres NOSQL json data

3 Answers3

Linked