获取Postgres NOSQL json数据中json数组的总和

发布时间：2020-12-13 13:28:38 所属栏目：百科来源：网络整理

导读：如何从postgres json select中的“refunds”数组中获取聚合SUM(金额) 以下是我的数据架构和结构：表名：交易列名：数据 { "id": "tran_6ac25129951962e99f28fa488993","amount": 1200,"origin_amount": 3900,"status": "partial_refunded","description":

如何从postgres json select中的“refunds”数组中获取聚合SUM(金额)
以下是我的数据架构和结构：

表名：交易

列名：数据

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

输出应该是：1200 1500 = 2700

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

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

解决方法

这应该适用于9.3

WITH x AS( SELECT
'{
  "id": "tran_6ac25129951962e99f28fa488993","updated_at": 1426412340
    },"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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!