在Postgres NOSQL json数据中获取json数组的总和 [英] Get aggregate sum of json array in Postgres NOSQL json data

问题描述

如何从postgres json select中的"refunds"数组中获取聚合SUM(amount) 以下是我的数据模式和结构:

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

表名称:transactions

列名: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,
    }
  ]
}

输出应该是:1200 + 1500 = 2700

Out put should be: 1200+1500 = 2700

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

请提供全局解决方案，而不提供静态数据

Please provide global solution and not with static data

推荐答案

这应该适用于9.3 +

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;

这篇关于在Postgres NOSQL json数据中获取json数组的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！