CODEWITHSUNDEEP
mysqljson
Rizky Fathurahman
Rizky Fathurahman

Reputation: 1

how to get the amount of results array sum mysql

I have json data in the attrs field like this. I want the amount target_pencairan but the result is 0, can anyone help?

"attrs": {    
       "tgl_mulai": "null", 
       "tgl_selesai": null,
       "target_pencairan": [1000, 2000]  
 }`

select id,parent_id,level, SUM(attrs->>'$.target_pencairan') as target_pencairan 
from project group by id

Upvotes: 0

Views: 105

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

You can use json_extract function.

select id,
 sum(json_extract(t1.attrs, concat('$.attrs.target_pencairan[', t2.col1, ']')))
from project t1  
cross join ( 
  select  0 as col1 union
  select  1 union
  select  2 union
  select  3 union
  select  4 union
  select  5   
  ) t2
group by id;

for mysql v8.0 you can use json_table function.

select id, parent_id, sum(res)
from project t1
cross join 
       json_table(t1.attrs,
         "$.attrs.target_pencairan[*]"
         columns(
           `res` int path "$"
         )
       ) t2
group by id, parent_id;

see dbfiddle.

Upvotes: 1

Related Questions