How to update sum of array elements in json in MySQL?

Question

I am using mysql 8.0.22
My sample table be like

id	students	total_marks
1	{"students":[{"marks":100},{"marks":100},{"marks":100},{"marks":100}]}

After update it should be

id	students	total_marks
1	{"students":[{"marks":100},{"marks":100},{"marks":100},{"marks":100}]}	400

I ahve to add all the marks in that json. I am able to write a stored procedure for that.
I am trying to do it in one query.
How can I do it? Thanks.

I had miss the incremental id column. I have updated in the question. — Manonandan S K
– Manonandan S K, Commented Sep 20, 2021 at 14:24

Manonandan S K · Accepted Answer · 2021-09-20 14:34:18Z

1

Finally answering my own question.

Please refer JSON_TABLE.
JSON_TABLE() takes json, path to array and columns with path to required field and converts it to a table format. we can use that table same as other table in that query.

update sample s
set s.total_marks = (
                 select sum(t.marks)
                 from JSON_TABLE(s.students, '$.students[*]' columns(marks int path '$.marks'))
                 as temp_table t 
                 )
where s.id = 1;

edited Sep 20, 2021 at 14:34

answered Sep 20, 2021 at 14:22

Manonandan S K

6621 gold badge8 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update sum of array elements in json in MySQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related