SoFunction
Updated on 2025-03-06

Detailed explanation of JSON_CONTAINS function in MySQL

Preface

When processing JSON data in MySQL, we often need to check whether a JSON document contains a specific value. At this time,JSON_CONTAINSFunctions appear very useful.

Introduction to JSON_CONTAINS function

JSON_CONTAINSis a JSON function provided by MySQL to test whether a JSON document contains a specific value. Returns 1 if included, otherwise returns 0. This function accepts three parameters:

  • target: The target JSON document to be searched.
  • candidate: The value to search for in the target JSON document.
  • path(Optional): Path expression indicating where to search for candidate values.

The general syntax is:

JSON_CONTAINS(target, candidate[, path])

JSON_CONTAINS function instance demonstration

Suppose we have a name calledproductsThe table contains some product information:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON
);

INSERT INTO products (details)
VALUES 
('{"name": "Product 1", "tags": ["tag1", "tag2", "tag3"]}'),
('{"name": "Product 2", "tags": ["tag1", "tag4"]}');

Now, we want to find out all the included"tag1"Tag products. We can useJSON_CONTAINSFunctions to implement this requirement:

SELECT * FROM products WHERE JSON_CONTAINS(details->'$.tags', '"tag1"');

Path parameters of JSON_CONTAINS function

JSON_CONTAINSThe function provides an optionalpathParameters that specify which part of the JSON document should search for candidate values. The value of this parameter should be a JSON path expression.

SELECT * FROM products WHERE JSON_CONTAINS(details, '"red"', '$.');

In this query,$.It is a path expression, which means we want todetailsJSON documentationSearch in paragraph"red"Value.

Comparison of two usage methods of JSON_CONTAINS function

AlthoughJSON_CONTAINS(details, '"red"', '$.')andJSON_CONTAINS(details->'$.', '"red"')The results are the same in most cases, but their processing strategies are different in some special scenarios.

  • details->'$.'This way of writing begins withdetailsFieldsPartially take out the value and then do it based on thisJSON_CONTAINSoperate. This method is more convenient if you want to do further operations on the fetched JSON fragment.

  • JSON_CONTAINS(details, '"red"', '$.')Directly indetailsExecute in the fieldJSON_CONTAINS, a path is specified during the search process. It is a separate expression and cannot be used directly to further extract or manipulate JSON data. But if you simply check if a specific value is included in the specified path, this method is sufficient.

Which method is actually used depends on your specific needs and scenario.

Overall,JSON_CONTAINSis a powerful function that allows us to process JSON data in MySQL in a flexible and efficient way. Hope this article helps you better understand and use this function.

Summarize

This is the article about the detailed explanation of JSON_CONTAINS function in MySQL. For more related contents of JSON_CONTAINS function in MySQL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!