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

Jakub Kania · Accepted Answer · 2015-03-15 14:11:50Z

25

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, 2015 at 14:11

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mukund Over a year ago

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

Jakub Kania Over a year ago

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) (...)

mukund Over a year ago

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

Jakub Kania Over a year ago

@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.

Leschge · Accepted Answer · 2021-03-23 17:05:47Z

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.

Nitesh Malviya · Accepted Answer · 2023-05-09 10:19:26Z

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 9, 2023 at 10:19

Nitesh Malviya

8401 gold badge11 silver badges18 bronze badges

Comments

Sahap Asci · Accepted Answer · 2023-09-15 20:05:01Z

With postgresql 12+ you can use sqlpath language. Suppose that we have a table named my_table and a field named my_json_value, here is a sample query:

WITH my_table 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
    }
    ]
  }'::jsonb AS my_json_value
)
SELECT
    sum(amount::numeric) AS sum_of_amount
  FROM my_table t,
  jsonb_path_query (t.my_json_value, '$.refunds.amount') as amount

Since jsonb_path_query returns table (set of jsonb) we use it as table expression.

Collectives™ on Stack Overflow

Get aggregate sum of json array in Postgres NOSQL json data

4 Answers 4

4 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Linked

Related