How to Query JSON Data in MySQL

In MySQL databases, JSON objects can be stored within table columns. However, running queries that utilize these JSON fields can be challenging without the proper techniques. This guide provides methods for easily querying JSON data using the WHERE clause, enabling developers to filter and retrieve specific records based on JSON object properties.

Utilizing JSON_EXTRACT Function

For MySQL versions 5.7 and above, the JSON_EXTRACT function is an effective tool for extracting specific values from JSON objects within a WHERE clause. This function takes two parameters:

• json_document: The JSON object from which the value is being extracted.
• path: A JSON path expression that specifies the location of the target value within the object.

Consider the example table provided in the original inquiry with the column "json_data" containing JSON objects. To query the table for rows where the "title" property contains the string "CPU," the following query can be used:

SELECT user_id, json_data
FROM articles
WHERE JSON_EXTRACT(json_data, '$.title') LIKE '%CPU%';

This query leverages the JSON_EXTRACT function to retrieve the value of the "title" property from each JSON object in the "json_data" column. It then filters the results to include only those rows where the extracted title value contains the specified substring.