How does MySQL query and filter stored JSON data?

How does MySQL query and filter stored JSON data?

JSON (JavaScript Object Notation) is a lightweight data exchange format that is widely used in modern applications. MySQL has added support for JSON starting from version 5.7, allowing us to store and query data in JSON format in the MySQL database. This article will introduce how to use MySQL to query and filter stored JSON data, and provide corresponding code examples.

1. Create a test table and insert data

First, we need to create a test table and insert some sample data containing JSON data. The following is the definition of a table named "users":

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    info JSON
);

INSERT INTO users (name, info) VALUES
    ('John', '{"age": 30, "city": "New York", "email": "john@example.com"}'),
    ('Alice', '{"age": 25, "city": "Los Angeles", "email": "alice@example.com"}'),
    ('Bob', '{"age": 35, "city": "Chicago", "email": "bob@example.com"}');

In the above example, the "users" table contains three fields: id, name and info. Among them, the data type of the info field is JSON.

2. Query the attribute value in the JSON field

To query the attribute value in the JSON field, you can use the JSON_EXTRACT function provided by MySQL. Here is an example:

SELECT name, JSON_EXTRACT(info, '$.age') AS age FROM users;

The above query will return the name and age information for each user. Among them, the first parameter of the JSON_EXTRACT function is the JSON field to be queried, and the second parameter is the attribute path to be extracted. In this example, we extracted the age attribute from the info field.

3. Use WHERE conditions to filter JSON data

In MySQL, we can use WHERE conditions to filter JSON data. Here is an example:

SELECT name, info
FROM users
WHERE JSON_EXTRACT(info, '$.age') > 30;

The above query will return the names and JSON data of all users older than 30 years old. As you can see, we can use the JSON_EXTRACT function to get the attribute values ​​in the JSON field and use these attribute values ​​in the WHERE condition to filter.

4. Update attribute values ​​in JSON fields

To update attribute values ​​in JSON fields, you can use the JSON_SET function provided by MySQL. The following is an example:

UPDATE users
SET info = JSON_SET(info, '$.city', 'Seattle')
WHERE name = 'Alice';

The above update operation will modify the city information of the user named "Alice" to "Seattle". The first parameter of the JSON_SET function is the JSON field to be updated, the second parameter is the attribute path to be modified, and the third parameter is the new attribute value.

5. Delete attribute values ​​in JSON fields

To delete attribute values ​​in JSON fields, you can use the JSON_REMOVE function provided by MySQL. Here is an example:

UPDATE users
SET info = JSON_REMOVE(info, '$.email')
WHERE name = 'Bob';

The above update operation will delete the email address attribute of the user named "Bob". The first parameter of the JSON_REMOVE function is the JSON field of the attribute to be deleted, and the second parameter is the path of the attribute to be deleted.

Conclusion

This article describes how to use MySQL to query and filter stored JSON data, and provides corresponding code examples. By using the JSON_EXTRACT function, we can easily obtain the attribute values ​​in the JSON field; and using the JSON_SET and JSON_REMOVE functions, we can update and delete attribute values. Using MySQL to store and manipulate JSON data better meets the needs of modern applications.

The above is the detailed content of How does MySQL query and filter stored JSON data?. For more information, please follow other related articles on the PHP Chinese website!