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
– El Tito Barte, Commented Jun 28, 2021 at 18:04
Look Into the where json contains clause for mysql. stackoverflow.com/questions/36249828/… — Dorvalla
– Dorvalla, Commented Jun 28, 2021 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
– El Tito Barte, Commented Jun 28, 2021 at 18:09

Bill Karwin · Accepted Answer · 2021-06-29 18:06:14Z

1

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;

edited Jun 29, 2021 at 18:06

answered Jun 29, 2021 at 14:31

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

Collectives™ on Stack Overflow

How to extract Array values from JSON Object in MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related