How do I Extract JSON Data from MySQL Columns Using Queries?

Extracting JSON Data in MySQL with Queries

JSON is a popular data format used to store structured data, and it's becoming increasingly common to store JSON objects in database columns. MySQL provides several methods for extracting data from JSON columns, making it easier to query and filter data based on specific fields.

Using json_extract() (MySQL 5.7 and above)

For MySQL versions 5.7 and above, the json_extract() function offers a convenient way to extract specific values from JSON objects. Here's an example:

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

This query retrieves rows where the "title" field in the JSON column matches the pattern "%CPU%". It would return only the first row from the sample table provided.

Using json_unquote() and json_contains() (MySQL 8.0 and above)

In MySQL 8.0 and later, the json_unquote() function can be used to remove double quotes from JSON values, and the json_contains() function can be used to check if a specific value exists in a JSON object. Here's an alternative query:

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

The above is the detailed content of How do I Extract JSON Data from MySQL Columns Using Queries?. For more information, please follow other related articles on the PHP Chinese website!