How to sum of jsonb inner field in postgres?

Question

I want to sum one field PC value from table test group by metal_id.

I tried this Postgres GROUP BY on jsonb inner field but not worked for me as I have different JSON format

tbl_test

id json
1  [
    {
        "pc": "4",
        "metal_id": "1"
    },
    {
        "pc": "4",
        "metal_id": "5"
    }
]

2. [
    {
        "pc": "1",
        "metal_id": "1"
    },
    {
        "pc": "1",
        "metal_id": "2"
    },       
]

output I want is :(group by metal_id and sum of pc).

Thanks in advance!

   [
         "metal_id": 1
        {
          "pc": "5",
        }
      ]

score 0 · Accepted Answer · answered Mar 20 '20 at 10:18

You can use json_array_element() to expand the jsonb array, and then aggregate by metal id:

select obj ->> 'metal_id' metal_id, sum((obj ->> 'pc')::int) cnt
from mytable t
cross join lateral jsonb_array_elements(t.js) j(obj)
group by obj ->> 'metal_id'
order by metal_id

Demo on DB Fiddle:

metal_id | cnt
:------- | --:
1        |   5
2        |   1
5        |   4

How to sum of jsonb inner field in postgres?

1 Answers1