CODEWITHSUNDEEP
mysqlsqlmysql-json
Eugene Puninskiy
Eugene Puninskiy

Reputation: 69

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

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

Upvotes: 0

Views: 459

Answers (1)

Eugene Puninskiy
Eugene Puninskiy

Reputation: 69

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

Upvotes: 1

Related Questions