CODEWITHSUNDEEP
phpmysqlsqljson
R.Surya
R.Surya

Reputation: 184

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

Upvotes: 1

Views: 1382

Answers (1)

vixducis
vixducis

Reputation: 1107

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.

Upvotes: 2

Related Questions