How to search JSON data in MySQL?

In MySQL, several methods can be used to search data stored in the JSON data type. Starting from MySQL 5.7 and later versions, MySQL provides native support for the JSON data type, including functions and operators for querying and manipulating JSON documents. Here, I will outline several commonly used methods for searching JSON data.

1. Using JSON Search Functions

MySQL provides multiple functions to facilitate JSON data search, such as JSON_EXTRACT and JSON_SEARCH.

Example:

Assume we have a table named users with a column named attributes of JSON type, storing various user attribute information.

json
{
  "name": "Zhang San",
  "age": 30,
  "skills": ["Java", "MySQL", "Python"]
}

To find users with the name "Zhang San", use the following SQL statement:

sql
SELECT * FROM users WHERE JSON_EXTRACT(attributes, '$.name') = 'Zhang San';

To find users with the "Python" skill, use the following SQL statement:

sql
SELECT * FROM users WHERE JSON_CONTAINS(attributes, '"Python"', '$.skills');

2. Using JSON Path Expressions

When querying JSON data, JSON path expressions can be used to locate specific elements, utilizing syntax similar to XPath.

Example:

Continuing with the above scenario, to retrieve the age of all users, use the following SQL statement:

sql
SELECT JSON_EXTRACT(attributes, '$.age') AS user_age FROM users;

3. Creating Virtual Columns and Indexes

To enhance query efficiency for JSON data, create virtual columns that directly reference values within the JSON column and build indexes on these virtual columns.

Example:

sql
ALTER TABLE users ADD COLUMN user_name VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.name')));
CREATE INDEX idx_user_name ON users(user_name);

By doing this, MySQL can leverage the index for rapid lookups when querying names, eliminating the need to scan the entire JSON column each time.

sql
SELECT * FROM users WHERE user_name = 'Zhang San';

Summary

The methods outlined above enable effective querying and manipulation of JSON data in MySQL. The choice of method depends on the specific data structure and query requirements. In practical applications, appropriately utilizing JSON functions and path expressions, along with virtual columns and indexing techniques, can significantly improve query performance and efficiency.