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 |
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