How can I count same values in JSON found in MYSQL column?

Question

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

Side note: The schema of the JSON looks pretty static to me. You should consider not to abuse JSON but use relational means like (lookup and/or linking) tables and columns instead. Then this would be a simple aggregation. — sticky bit
– sticky bit, Commented Jan 8, 2022 at 20:32
While it's perfectly fine to store JSON records in a database, if you also find yourself needed to use SQL to look inside those records you will do SO MUCH BETTER to also define real relational schema around the fields that matter. It is so much more efficient to parse the JSON once, at insert/update time, rather than for every single query. You can also then use indexes on the fields, which is key for SQL performance. Again, you need don't need to extract and define relational fields for ALL of the JSON attributes... just what you will need to use with your queries later. — Joel Coehoorn
– Joel Coehoorn, Commented Jan 8, 2022 at 21:02

Akina · Accepted Answer · 2022-01-08 20:40:15Z

4

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

answered Jan 8, 2022 at 20:40

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I count same values in JSON found in MYSQL column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related