How to Query MySQL Columns with JSON Data Using JSON Functions?

Querying MySQL Columns with JSON Data Using JSON Functions

In MySQL, you can store JSON objects as values in database columns. To efficiently retrieve data from these columns using specific JSON fields in your queries, consider utilizing JSON functions such as json_extract().

Using json_extract() for Query Filtering

For instance, let's consider the following table named articles containing a column named json_data with JSON objects:

Column	Data
id	1, 2, 3
user_id	1, 1, 2
json_data	'{"url":"https://www.cpubenchmark.net/","title": "CPU Benchmarks"}', '{"url":"http://www.ebay.com/sch/CPUs-Processors-/164/i.html","title": "Computer and Processors"}', '{"url":"https://www.youtube.com/watch?v=tntOCGkgt98","title": "Funny Cats Compilation"}'

To filter records based on a specific JSON field, you can use the json_extract() function. For example, to retrieve articles containing the term "CPU" in the title field of the JSON objects:

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

This query will return the first row, as it matches the criteria.

Additional Notes

• json_extract() allows you to extract specific values from JSON objects.
• For older versions of MySQL (prior to 5.7), you can use alternative methods such as stored routines or third-party extensions.
• Consider using indexes on JSON columns for improved query performance.

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