By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test
SELECT 'PPP' col1, '{"A": 0, "B": 5, "C": 10}' col2 UNION ALL
SELECT 'SSS' , '{"A": 5}' UNION ALL
SELECT 'KKK' , '{"A": 5, "B": 5, "C": 10}' UNION ALL
SELECT 'KKK' , '{"C": 20, "B": 5}';
SELECT * FROM test;
Records: 4 Duplicates: 0 Warnings: 0
| col1 | col2 |
|---|---|
| PPP | {"A": 0, "B": 5, "C": 10} |
| SSS | {"A": 5} |
| KKK | {"A": 5, "B": 5, "C": 10} |
| KKK | {"C": 20, "B": 5} |
SELECT *, JSON_KEYS(col2)
FROM test;
| col1 | col2 | JSON_KEYS(col2) |
|---|---|---|
| PPP | {"A": 0, "B": 5, "C": 10} | ["A", "B", "C"] |
| SSS | {"A": 5} | ["A"] |
| KKK | {"A": 5, "B": 5, "C": 10} | ["A", "B", "C"] |
| KKK | {"C": 20, "B": 5} | ["B", "C"] |
SELECT *,
JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(col2), CONCAT('$[', numbers.num, ']'))),
JSON_EXTRACT(col2, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(col2), CONCAT('$[', numbers.num, ']')))))
FROM test
CROSS JOIN ( SELECT 0 num
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4 ) numbers;
| col1 | col2 | num | JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(col2), CONCAT('$[', numbers.num, ']'))) | JSON_EXTRACT(col2, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(col2), CONCAT('$[', numbers.num, ']'))))) |
|---|---|---|---|---|
| PPP | {"A": 0, "B": 5, "C": 10} | 0 | A | 0 |
| SSS | {"A": 5} | 0 | A | 5 |
| KKK | {"A": 5, "B": 5, "C": 10} | 0 | A | 5 |
| KKK | {"C": 20, "B": 5} | 0 | B | 5 |
| PPP | {"A": 0, "B": 5, "C": 10} | 1 | B | 5 |
| SSS | {"A": 5} | 1 | null | null |
| KKK | {"A": 5, "B": 5, "C": 10} | 1 | B | 5 |
| KKK | {"C": 20, "B": 5} | 1 | C | 20 |
| PPP | {"A": 0, "B": 5, "C": 10} | 2 | C | 10 |
| SSS | {"A": 5} | 2 | null | null |
| KKK | {"A": 5, "B": 5, "C": 10} | 2 | C | 10 |
| KKK | {"C": 20, "B": 5} | 2 | null | null |
| PPP | {"A": 0, "B": 5, "C": 10} | 3 | null | null |
| SSS | {"A": 5} | 3 | null | null |
| KKK | {"A": 5, "B": 5, "C": 10} | 3 | null | null |
| KKK | {"C": 20, "B": 5} | 3 | null | null |
| PPP | {"A": 0, "B": 5, "C": 10} | 4 | null | null |
| SSS | {"A": 5} | 4 | null | null |
| KKK | {"A": 5, "B": 5, "C": 10} | 4 | null | null |
| KKK | {"C": 20, "B": 5} | 4 | null | null |
SELECT col1,
JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(col2), CONCAT('$[', numbers.num, ']'))) `key`,
SUM(JSON_EXTRACT(col2, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(col2), CONCAT('$[', numbers.num, ']')))))) `value`
FROM test
CROSS JOIN ( SELECT 0 num
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4 ) numbers
GROUP BY 1,2
HAVING `key` IS NOT NULL;
| col1 | key | value |
|---|---|---|
| KKK | 41 | 5 |
| KKK | 42 | 10 |
| KKK | 43 | 30 |
| PPP | 41 | 0 |
| PPP | 42 | 5 |
| PPP | 43 | 10 |
| SSS | 41 | 5 |
SELECT col1, JSON_OBJECTAGG(`key`, `value`) col2
FROM (
SELECT col1,
JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(col2), CONCAT('$[', numbers.num, ']'))) `key`,
SUM(JSON_EXTRACT(col2, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(col2), CONCAT('$[', numbers.num, ']')))))) `value`
FROM test
CROSS JOIN ( SELECT 0 num
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4 ) numbers
GROUP BY 1,2
HAVING `key` IS NOT NULL
) subquery
GROUP BY col1;
| col1 | col2 |
|---|---|
| KKK | {"A": 5, "B": 10, "C": 30} |
| PPP | {"A": 0, "B": 5, "C": 10} |
| SSS | {"A": 5} |