I am collecting JSON data in MYSQL column. I want to count numbers with same keywords in this JSON data.
Example JSON column:
| id | column |
|---|---|
| 1 | { "2": { "model": "Test Model" }, "3": { "model": "Test Model" } } |
| 2 | { "4": { "model": "Test Model" }, "2": { "model": "Test Model" } } |
| 3 | { "1": { "model": "Test Model" }, "4": { "model": "Test Model" } } |
| 4 | { "2": { "model": "Test Model" } } |
The output I want:
| key | count |
|---|---|
| 2 | 3 |
| 4 | 2 |
| 3 | 1 |
| 1 | 1 |
Can I do this easily and with shortcodes?
Mysql version: 8.0.27
CodePudding user response：
SELECT jsontable.`key`, COUNT(*) `count`
FROM test
CROSS JOIN JSON_TABLE(JSON_KEYS(test.json_value),
'$[*]' COLUMNS (`key` INT PATH '$')) jsontable
GROUP BY jsontable.`key`
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=717eeeacb3bceb45a2094e46d37c8865