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?

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.