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
SELECT COUNT(DISTINCT(CARS)) from CARS where CARS.DIAGNOSES->>"$.diagnoses[*].failure.name" LIKE '%rebuilded engine%'
->> operator in addition unquotes the extracted result. In other words, given a
JSON column value
column and a path expression
path (a string literal)
->> operator can be used wherever
JSON_UNQUOTE(JSON_EXTRACT()) would be allowed. This includes (but is not limited to)
HAVING clauses, and
ORDER BY and
GROUP BY clauses.
$. indicates how you can access a json and
[*] can search in any position of an array.