The JSON_CONTAINS_PATH function in MySQL is used to check if a JSON document contains a specified path expression. This function returns 1 if the path expression is found in the JSON document; otherwise, it returns 0.

Syntax

Here is the syntax for the JSON_CONTAINS_PATH function:

JSON_CONTAINS_PATH(json_doc, path[, one_or_all])

json_doc: The JSON document to be searched.
path: The path expression to search for in the JSON document.
one_or_all (optional): This parameter can be set to either ‘one’ or ‘all’. If set to ‘one’, the function returns true if at least one match is found; if set to ‘all’, all elements in the path must be present for the function to return true.

Example

Consider a JSON document stored in a table called example_table:

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

INSERT INTO example_table (id, data) VALUES
(1, '{"name": "John", "age": 30, "address": {"city": "New York", "zipcode": "10001"}}'),
(2, '{"name": "Jane", "age": 25, "address": {"city": "Los Angeles", "zipcode": "90001"}}');

Now, let’s use the JSON_CONTAINS_PATH function to check if the JSON documents in the data column contain a specific path:

-- Check if the path 'name' exists in the JSON document.
SELECT id, JSON_CONTAINS_PATH(data, 'one', '$.name') AS name_exists
FROM example_table;

-- Check if the path 'address.city' exists in the JSON document.
SELECT id, JSON_CONTAINS_PATH(data, 'one', '$.address.city') AS city_exists
FROM example_table;

In the first example, we check if the path ‘name’ exists in the JSON document, and in the second example, we check if the path ‘address.city’ exists. The ‘one’ parameter ensures that the function returns true if at least one match is found. The results will indicate whether the specified paths exist in each JSON document.

The JSON_CONTAINS_PATH function plays a significant role in manipulating and navigating JSON data within MySQL databases. It enables developers to effectively manage and analyze JSON structures, enhancing the flexibility and functionality of their applications.