JSON_CONTAINS usage, syntax, examples and application scenarios in MySQL

Preface

MySQL 5.7 and later introduce support for JSON data types, making it possible to store and query JSON data in a database. Among these new features,JSON_CONTAINSFunctions are a very useful tool that allows us to check whether a JSON document contains a specific value or object. This article will discuss in depthJSON_CONTAINSThe usage, syntax, examples and application scenarios of .

1. Overview of JSON_CONTAINS function

JSON_CONTAINSFunctions are used to check whether one JSON document contains another JSON document. Its syntax is as follows:

JSON_CONTAINS(target, candidate[, path])

target: Target JSON document, that is, the document we want to check.
candidate: Candidate JSON document, i.e. the value or object we are looking for.
path: Optional parameter, specifying a JSON path to find a specific node.

2. Basic usage

2.1 Check simple values

Suppose we have a table that stores user informationusers, there is a JSON columnpreferences, the structure is as follows:

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

Insert some sample data:

INSERT INTO users (name, preferences) VALUES 
('Alice', '{"theme": "dark", "notifications": true}'),
('Bob', '{"theme": "light", "notifications": false}'),
('Charlie', '{"theme": "dark"}');

We can useJSON_CONTAINSTo check which users' preferences contain a specific value. For example, find all users with the "dark" topic in their preferences:

SELECT name
FROM users
WHERE JSON_CONTAINS(preferences, '"dark"', '$.theme');

In this query, we checkpreferencesInthemeWhether the field contains a value"dark"。

2.2 Check nested objects

If the JSON document contains nested structures,JSON_CONTAINSStill can be used effectively. Assume we updatepreferencesColumns, add more complex structures:

UPDATE users SET preferences = '{"ui": {"theme": "dark", "font": "Arial"}, "notifications": true} WHERE name = "Alice";

We now want to check if Alice's preferences include{"theme": "dark"}This object:

SELECT name
FROM users
WHERE JSON_CONTAINS(preferences, '{"theme": "dark"}', '$.ui');

3. Practical application scenarios

3.1 Filter user data

In practical applications,JSON_CONTAINSIt can be used to filter users based on user preferences. For example, display all notification enabled users:

SELECT name
FROM users
WHERE JSON_CONTAINS(preferences, 'true', '$.notifications');

3.2 Multi-condition query

If we want to find all users who use both the "dark" topic and have notifications enabled, we can use it in conjunctionJSON_CONTAINSandANDcondition:

SELECT name
FROM users
WHERE JSON_CONTAINS(preferences, '"dark"', '$.')
AND JSON_CONTAINS(preferences, 'true', '$.notifications');

3.3 Use with other JSON functions

JSON_CONTAINSIt can also be used in conjunction with other JSON functions, such asJSON_ARRAY, JSON_OBJECTetc. to create more complex queries. For example, we can check multiple topics in user preferences:

SELECT name
FROM users
WHERE JSON_CONTAINS(preferences, '["dark", "light"]', '$.');

4. Performance considerations

Performance is a key factor to consider when using JSON data types and functions. While JSON provides advantages for flexible data storage, excessive nesting and complex structures can lead to degraded query performance. Therefore, when designing JSON data structures, possible query methods and data access patterns should be taken into account.

5. Summary

JSON_CONTAINSis a powerful tool provided by MySQL, which can quickly find and match specific values or objects in JSON data. By using this feature flexibly, data processing and flexibility of applications can be greatly enhanced. As application scenarios continue to expand, understanding and utilizing JSON functions in MySQL will become increasingly important.

I hope this article can help you better understand and apply the MySQLJSON_CONTAINSFunction!

Refer to the official document: /doc/refman/5.7/en/#function_json-contains

This is the article about JSON_CONTAINS usage, syntax, examples and application scenarios in MySQL. For more related JSON_CONTAINS content in MySQL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!