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

Answer 1

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;

Answer 2

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.