How to extract Array values from JSON Object in MySQL

Question

I'm trying to extract values from Array in JSON object, get the most repeated values and order it.

My column tags have next object: {"en":["polytics","friendly"]}

For example: I have many rows with this data, the object "en" have an array with many values, and I'm trying to extract from all rows the array values and order it by most repeated.

How is the sql query? Any help? Thanks.

@Dorvalla thanks for the answer, json_decode() is a PHP function, and I need MySQL query to get only the most repeated values. — El Tito Barte, Jun 28 '21 at 18:04
Look Into the `where json contains ` clause for mysql. https://stackoverflow.com/questions/36249828/how-to-search-json-array-in-mysql/48614004 — Dorvalla, Jun 28 '21 at 18:05
@Dorvalla for json_contains I need a value to find, and is not what I'm looking for, I'm looking for most repeated value in array. — El Tito Barte, Jun 28 '21 at 18:09
What have you tried so far? Where are you stuck? Why not normalize the database, to make this easier? Also, how is this related to PHP? — Nico Haase, Jun 29 '21 at 14:33

Bill Karwin · Accepted Answer · 2021-06-29T18:06:14.430

Since you are using MySQL 5.7, you don't have support for the JSON_TABLE() function, which would be the solution for the query you describe.

SELECT j.tag, COUNT(*) AS count
FROM mytable CROSS JOIN 
JSON_TABLE(tags, '$.en[*]' COLUMNS(tag VARCHAR(10) PATH '$.') AS j
GROUP BY j.tag
ORDER BY count DESC;

I would strongly recommend that you store your multi-valued data in normal columns and rows, not in JSON. Then the query would become more straightforward:

SELECT `tag`, COUNT(*) AS count
FROM en_tags
GROUP BY `tag`
ORDER BY count DESC;

How to extract Array values from JSON Object in MySQL

1 Answers1