MySQL: best way to sum all JSON values in each cell with varying number of keys and nulls

Question

Let there be a table data with two columns

id	parts
1	{"1": 1, "2": 0, "3": 2, "4": 0, "5": 0}
2	NULL
3	{"6": 1, "7": 0}
4	{"3": 1}
5	null

Column id is integer type and column parts can have JSON expressions, empty cells (NULL) as well as JSON nulls.

The question is fairly simple: how does one find the sum of all values in each cell in parts, leaving only not null cells. The final result should be:

id	sum_parts
1	3
3	1
4	1

Eugene Puninskiy · Answer 1 · 2022-09-14T07:20:52.773

1

I managed to get a solution using Alexey's answer from this question mysql - sum of json elements, group by and select all, but I find it a little clunky:

with data_not_null as
(
SELECT * FROM data
WHERE data.parts IS NOT NULL
    AND data.parts != CAST('null' AS JSON)
)

SELECT id, SUM(JSON_EXTRACT(CONCAT('$."', key, '"'))) sum_parts
FROM data_not_null,
    JSON_TABLE(CAST(JSON_KEYS(parts->'$.') AS JSON), '$[*]' COLUMNS(key VARCHAR(2) PATH '$') t
GROUP BY id

edited Sep 14 '22 at 07:20

answered Sep 13 '22 at 17:30

Eugene Puninskiy

59
4

Every use of JSON in MySQL is at least a little bit clunky. Use normal rows and columns if you want it to be elegant. – Bill Karwin Sep 23 '22 at 18:09
@BillKarwin yes, but unfortunately I did not design the database that I am using. So I have to work with what I've got :) – Eugene Puninskiy Oct 14 '22 at 07:21
1

Then you must accept that it will be clunky. – Bill Karwin Oct 14 '22 at 14:19

MySQL: best way to sum all JSON values in each cell with varying number of keys and nulls

1 Answers1