MySQL JSON_CONTAINS_PATH() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_CONTAINS_PATH() function to check whether a JSON document contains specific paths.

Introduction to MySQL JSON_CONTAINS_PATH() function

The JSON_CONTAINS_PATH function is used to check whether a JSON document contains specific paths.

Here’s the syntax for the JSON_CONTAINS_PATH() function:

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_doc: This is the JSON document you want to search for the paths.
• one_or_all: A keyword that specifies whether you want to find at least one or all of the specified paths. If it is 'one', the function returns 1 if the JSON document contains at least one, 0 otherwise. If the parameter is 'all', the function returns 1 if the document contains all the specified paths and 0 otherwise.
• path: This is one or more path expressions you want to check within the JSON document.

The function is useful when you want to check if certain paths exist within the JSON document.

MySQL JSON_CONTAINS_PATH() function examples

Let’s take some examples of using the JSON_CONTAINS_PATH function.

1) Checking for the Existence of a Single Path

The following example uses the JSON_CONTAINS_PATH() function to check if the JSON document contains the path $.a:

SELECT 
  JSON_CONTAINS_PATH(
    '{"a": 1, "b": 2, "c": {"d": 4}}', 
    'one', '$.a'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The result is 1 because the JSON document contains the path '$.a'.

2) Checking for Multiple Paths with ‘one’ Mode

The following example uses the JSON_CONTAINS_PATH function to check if the JSON document contains at least one of two specified paths, $.a and $.e:

SELECT 
  JSON_CONTAINS_PATH(
    '{"a": 1, "b": 2, "c": {"d": 4}}', 
    'one', '$.a', '$.e'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The result is 1 because the JSON document contains at least one of the specified paths ($.a).

3) Checking for Multiple Paths with ‘all’ Mode

The following example uses the JSON_CONTAINS_PATH() function to check if the JSON document contains all of the specified paths, $.a and $.e:

SELECT 
  JSON_CONTAINS_PATH(
    '{"a": 1, "b": 2, "c": {"d": 4}}', 
    'all', '$.a', '$.e'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the function returns 0 because not all of the specified paths exist in the JSON document.

4) Nested paths

The following example uses the JSON_CONTAINS_PATH() function to check if the JSON document contains the nested path $.c.d:

SELECT 
  JSON_CONTAINS_PATH(
    '{"a": 1, "b": 2, "c": {"d": 4}}', 
    'one', '$.c.d'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the result is 1 because the path $.c.d exists in the JSON document.

5) Non-Existent Path example

The following example uses the JSON_CONTAINS_PATH() function to check if the JSON document contains the path $.a.d.

SELECT 
  JSON_CONTAINS_PATH(
    '{"a": 1, "b": 2, "c": {"d": 4}}', 
    'one', '$.a.d'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The function returns 0 because the JSON document does not contain the path $.a.d.

Summary

• Use the JSON_CONTAINS_PATH() function to check whether a JSON document contains specific paths.