DEV Community

Marcos Henrique
Marcos Henrique

Posted on

11 3

How to search a value on a JSON array column with MySQL?

Let's suppose you need to find how many cars in your car shop had a rebuilt engine, and this column is a JSON with an array of diagnosed failures

How can you do this query?
a man scratching his head



SELECT COUNT(DISTINCT(CARS)) from CARS
where CARS.DIAGNOSES->>"$.diagnoses[*].failure.name" LIKE '%rebuilded engine%'


Enter fullscreen mode Exit fullscreen mode

the ->> operator in addition unquotes the extracted result. In other words, given a JSON column value column and a path expression path (a string literal)
The ->> operator can be used wherever JSON_UNQUOTE(JSON_EXTRACT()) would be allowed. This includes (but is not limited to) SELECT lists, WHERE and HAVING clauses, and ORDER BY and GROUP BY clauses.

the $. indicates how you can access a json and [*] can search in any position of an array.

a man celebrating

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (1)

Collapse
 
thokuest profile image
Thomas Küstermann

Which version of MySQL supports this? It doesn't seem to work with 8.0.39. I am using JSON_TABLE() for such use cases.

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay