How to Group By some id and Sum all row json encode data

I have Faced group by some id and Sum other column json_encode data in select query MySQL.
But how to sum total json data value?
Please help Anyone...

Using this, PHP 7.2, Mysql 5 and Apache 2.

I am try code
    > SELECT `fk_club_id`,`adpoints`,`actpoints`,(JSON_OBJECT('actpoints',
    > actpoints, 'adpoints', adpoints)) FROM (SELECT `fk_club_id`,
    > SUM(JSON_EXTRACT("$.adpoints")) as adpoints,
    > SUM(JSON_EXTRACT("$.actpoints")) as actpoints FROM club_scoresheet
    > where status= 1 GROUP BY `fk_club_id`) as t

MY table data ,

    id  |  JSON  column()

    15    ['5','6','2']
    15    ['5','6','2']
    28    ['5','6','1']
    28    ['5','6','1']
    28    ['5','6','1']

it is my table

My expectation result,

id  | JSON column( total)

15     26
28     36

expected result

Answer

In MySQL version 5.7, JSON functions were introduced that could solve your problem. You could use the following, but it only allows to retrieve the sums when a fixed number of items is included in your JSON. Your example points out that there are 3 items in every JSON array, so you should be fine:

SELECT ID, SUM(CAST(JSON_EXTRACT(JSON,'$[0]') AS UNSIGNED)+CAST(JSON_EXTRACT(JSON,'$[1]') AS UNSIGNED)+CAST(JSON_EXTRACT(JSON,'$[2]') AS UNSIGNED)) AS TOTAL FROM TEST GROUP BY ID;

If you would upgrade to MySQL 8, you could also use the new JSON_TABLE function to process any number of items in the JSON array:

SELECT TEST.ID,SUM(t.VAL) AS TOTAL FROM TEST, JSON_TABLE(JSON, '$[*]' COLUMNS(VAL INT PATH '$')) t GROUP BY ID

You can test this out in this db fiddle as well.