How to use JSON format in MySQL to store and query data?

How to use JSON format to store and query data in MySQL?

In modern application development, using JSON format to store and query data has become a common practice. JSON (JavaScript Object Notation) is a lightweight data exchange format that is widely used for front-end and back-end data transmission, configuration files, logging, etc. MySQL has introduced support for JSON fields starting from version 5.7, allowing us to store, query and operate JSON data directly in MySQL.

This article will introduce how to use JSON format to store and query data in MySQL, and provide some practical code examples.

1. Create a table and insert data

First, we need to create a table containing JSON fields. Suppose we want to store the user's details, which contains information such as name, age, and contact details. We can create a table named "users" using the following SQL statement:

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  info JSON
);

Next, we can use the INSERT statement to insert some user data into the table. In the INSERT statement, we can use the JSON_OBJECT function to construct a JSON object and then store it into a JSON field. The following is an example:

INSERT INTO users (info) VALUES (
  JSON_OBJECT('name', 'John', 'age', 25, 'contact', JSON_OBJECT('email', 'john@example.com', 'phone', '1234567890'))
);

2. Query JSON data

In MySQL, we can use some functions to query JSON data. The following are some commonly used functions and their usage:

1. JSON_EXTRACT function: used to extract specific attributes or values ​​from JSON fields. It accepts two parameters, the first parameter is the JSON field, and the second parameter is the extraction path. Here is an example:

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

2. JSON_SEARCH function: used to search for matching keys or values ​​in a JSON field and return its path. It accepts three parameters, the first parameter is the JSON field, the second parameter is the value to be searched, and the third parameter is the path to start the search. Here is an example:

SELECT JSON_SEARCH(info, 'one', 'John', NULL, '$.name') AS name_path FROM users;

3. JSON_CONTAINS function: used to check whether a JSON field contains a specific key or value. It accepts two parameters, the first parameter is the JSON field and the second parameter is the key or value to check. The following is an example:

SELECT * FROM users WHERE JSON_CONTAINS(info, 'John', '$.name');

3. Update JSON data

We can use the JSON_SET function to update the value in the JSON field. The JSON_SET function accepts three or more parameters. The first two parameters are the JSON field to be updated and the path to be updated. The following parameters are the key to be set and the corresponding value. Here is an example:

UPDATE users SET info = JSON_SET(info, '$.age', 26) WHERE id = 1;

4. Delete JSON data

If we want to delete a specific key or value from a JSON field, we can use the JSON_REMOVE function. The JSON_REMOVE function accepts two or more parameters. The first parameter is the JSON field to be deleted, and the subsequent parameters are the key or path to be deleted. Here is an example:

UPDATE users SET info = JSON_REMOVE(info, '$.name') WHERE id = 1;

Summary:

This article introduces the basic operations of how to store and query JSON data in MySQL. By using JSON format to store data, we can more easily manipulate and query complex structured data. In addition to the introduced functions, MySQL also provides more JSON functions and operators to meet more complex needs. In actual development, we can choose to use JSON format to store data according to specific business needs to improve application performance and flexibility.

(Note: The above examples are based on MySQL 5.7 version. If you are using other versions, please consult the MySQL documentation of the corresponding version for more accurate information and examples.)

The above is the detailed content of How to use JSON format in MySQL to store and query data?. For more information, please follow other related articles on the PHP Chinese website!