The MySQL JSON_CONTAINS function is used to check whether a specified JSON value or path exists within a JSON document or array. The JSON_CONTAINS function is useful for querying JSON data stored in MySQL databases.

Syntax

The general syntax for the JSON_CONTAINS function is as follows:

JSON_CONTAINS(json_doc, val[, path])

json_doc: The JSON document or array to search.
val: The JSON value to search for.
path (optional): A path expression to search for the specified value. If omitted, the function checks if the entire document or array contains the specified value.

Example

Suppose you have a table named products with a column named attributes storing JSON data:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    attributes JSON
);

INSERT INTO products (id, name, attributes) VALUES
(1, 'Laptop', '{"brand": "Dell", "price": 1200, "specs": ["SSD", "8GB RAM"]}'),
(2, 'Smartphone', '{"brand": "Samsung", "price": 800, "specs": ["6.5 inch display", "128GB storage"]}'),
(3, 'Tablet', '{"brand": "Apple", "price": 1000, "specs": ["Retina display", "256GB storage"]}');

You can use JSON_CONTAINS to find products with specific attributes. For instance:

-- Find products where the brand is "Samsung"
SELECT * FROM products
WHERE JSON_CONTAINS(attributes, '{"brand": "Samsung"}');

This query would return the “Smartphone” record.

-- Find products where the specs include "8GB RAM"
SELECT * FROM products
WHERE JSON_CONTAINS(attributes, '"8GB RAM"', '$.specs');

This query would return the “Laptop” record.

The JSON_CONTAINS function is a powerful tool for working with JSON data in MySQL. It can be used to perform a variety of search operations on JSON arrays, and it can be used in conjunction with other JSON functions to extract and manipulate data from JSON documents.