How to Query JSON Data Stored in MySQL Columns?

Querying JSON Data in MySQL Columns

In MySQL, you can store JSON objects in columns to store complex data structures. To efficiently query these JSON fields, you may face challenges incorporating them into WHERE clauses. Here's a solution:

Solution using json_extract

MySQL 5.7 and later support the json_extract function, which allows you to extract specific values from JSON data. This function accepts two arguments: the JSON data and a JSON path expression.

Example:

Let's say you have a "articles" table with a "json_data" column that stores JSON objects. To find articles with titles containing "CPU":

<code class="sql">SELECT user_id, json_data
FROM articles
WHERE json_extract(json_data, '$.title') LIKE '%CPU%';</code>

This query will return:

+---------+--------------------------------------------------------------------------------------------------+
| user_id | json_data                                                                                        |
+---------+--------------------------------------------------------------------------------------------------+
|       1 | {"url":"https://www.cpubenchmark.net/","title": "CPU Benchmarks"}                                |
+---------+--------------------------------------------------------------------------------------------------+

Explanation:

• The json_extract function extracts the "title" field from the JSON data in the "json_data" column.
• The LIKE operator is used to compare the extracted value with "%CPU%".

By incorporating json_extract into your queries, you can easily search and filter JSON data in MySQL, making it a powerful tool for working with complex data.

The above is the detailed content of How to Query JSON Data Stored in MySQL Columns?. For more information, please follow other related articles on the PHP Chinese website!