How does mysql match a field in JSON

mysql matches a field in JSON

There is a field in the table, its type is JSON, I want to match the value of a field in this JSON string

In MySQL, if a field is of type JSON and you need to query the JSON datanameequal"Zhang San"All records of

AvailableJSON_EXTRACTFunction or->>Operators to extract values ​​from JSON data and filter in the WHERE clause.

Suppose you have a table users where a field data is of type JSON, and the structure of the JSON data is as follows:

{
    "name": "Zhang San",
    "age": 30
}

You can use the following two methods to query the record whose name is "Zhang San".

Method 1: Use JSON_EXTRACT

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

Method 2: Use the ->> operator

SELECT * 
FROM users 
WHERE data->>'$.name' = 'Zhang San';

explain

JSON_EXTRACT(data, '$.name'): Extract the value of name from the JSON field data.

->> '$.name': This is the simplified syntax of MySQL, which is used to directly extract the value of a key in JSON and return it as a string.

Notice:

• The values ​​returned by these two methods are strings, so you can just compare them directly in the WHERE clause.
• If the name key does not always exist or there are multiple identical keys (such as objects in an array), the query conditions need to be adjusted according to the specific JSON structure.

Sample data and query

• The structure of the assumption table users is as follows:

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

• Insert some sample data:

INSERT INTO users (data) VALUES
('{"name": "Zhang San", "age": 30}'),
('{"name": "Li Si", "age": 25}'),
('{"name": "Wang Wu", "age": 28}');

• Query the record whose name is "Zhang San":

SELECT * 
FROM users 
WHERE data->>'$.name' = 'Zhang San';

This query will return the first record.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.