Join the Stack Overflow Community
Stack Overflow is a community of 6.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 3 down vote favorite

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

share|improve this question
up vote 3 down vote accepted

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;
share|improve this answer
    
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
    
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 0: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 9:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.