How can I Query JSON Data in MySQL Based on Specific Field Values?

Querying JSON Data in MySQL

Problem:

You have a MySQL table with a column that stores JSON objects. You need to run queries that filter results based on specific JSON field values.

Solution: Using json_extract Function

MySQL 5.7 and later introduced the json_extract function, which allows you to extract specific values from JSON objects stored in a column. This allows you to perform queries like the following:

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

Example:

Consider the following table:

<code class="sql">CREATE TABLE articles (
  id int NOT NULL,
  user_id int NOT NULL,
  json_data json NOT NULL
);
INSERT INTO articles (id, user_id, json_data) VALUES
  (1, 1, '{"url":"https://www.cpubenchmark.net/","title": "CPU Benchmarks"}'),
  (2, 1, '{"url":"http://www.ebay.com/sch/CPUs-Processors-/164/i.html","title": "Computer and Processors"}'),
  (3, 2, '{"url":"https://www.youtube.com/watch?v=tntOCGkgt98","title": "Funny Cats Compilation"}');</code>

Running the query shown above will return the following result:

<code class="sql">+---------+--------------------------------------------------------------------------------------------------+
| user_id | json_data                                                                                        |
+---------+--------------------------------------------------------------------------------------------------+
|       1 | {"url":"https://www.cpubenchmark.net/","title": "CPU Benchmarks"}                                |
+---------+--------------------------------------------------------------------------------------------------+</code>

This demonstrates how the json_extract function can be used to filter JSON data in MySQL.

The above is the detailed content of How can I Query JSON Data in MySQL Based on Specific Field Values?. For more information, please follow other related articles on the PHP Chinese website!