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

#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?



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

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.

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.