In MySQL, the JSON_CONTAINS_PATH() function tests whether or not a specified path is found in the given JSON document.
If the path is found, JSON_CONTAINS_PATH() returns 1, otherwise it returns 0.
Syntax
The syntax goes like this:
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
Where json_doc is the JSON document, one_or_all is the keyword one or all (to indicate whether only one path need exist or all paths must exist before returning 1), and path is the actual path.
Example 1 – Basic Usage
Here’s an example to demonstrate basic usage.
SET @doc = '{"a": 1, "b": 2, "c": {"d": 3}}';
SELECT JSON_CONTAINS_PATH(@doc, 'one', '$.a') AS Result;
Result:
+--------+ | Result | +--------+ | 1 | +--------+
The path $.a exists in the JSON document, and therefore, the result is 1. We used one as the second argument but in this case we’d get the same result if we’d specified all.
SET @doc = '{"a": 1, "b": 2, "c": {"d": 3}}';
SELECT JSON_CONTAINS_PATH(@doc, 'all', '$.a') AS Result;
Result:
+--------+ | Result | +--------+ | 1 | +--------+
If we specify a path that doesn’t exist we get 0. Example:
SET @doc = '{"a": 1, "b": 2, "c": {"d": 3}}';
SELECT JSON_CONTAINS_PATH(@doc, 'one', '$.z') AS Result;
Result:
+--------+ | Result | +--------+ | 0 | +--------+
Example 2 – Multiple Paths
In the following examples we specify two paths.
In the first example, one path exists but the other doesn’t. But because I use one as the second argument, the result is positive (because at least one of the paths exist).
SET @doc = '{"a": 1, "b": 2, "c": {"d": 3}}';
SELECT JSON_CONTAINS_PATH(@doc, 'one', '$.a', '$.z') AS Result;
Result:
+--------+ | Result | +--------+ | 1 | +--------+
If we switch one to all we get a negative result.
SET @doc = '{"a": 1, "b": 2, "c": {"d": 3}}';
SELECT JSON_CONTAINS_PATH(@doc, 'all', '$.a', '$.z') AS Result;
Result:
+--------+ | Result | +--------+ | 0 | +--------+
This is because the all keyword requires that all paths must exist in the JSON document before it returns a 1.
Example 3 – Dot Notation
You can use dot-notation to indicate specific paths within the JSON document.
SET @doc = '{"a": 1, "b": 2, "c": {"d": 3}}';
SELECT JSON_CONTAINS_PATH(@doc, 'all', '$.c.d') AS Result;
Result:
+--------+ | Result | +--------+ | 1 | +--------+
Example 4 – Arrays
Here are a couple of array examples.
The first example returns a positive result.
SET @doc = '{"a": 1, "b": 2, "c": [3, 4, 5]}';
SELECT JSON_CONTAINS_PATH(@doc, 'all', '$.c[0]') AS Result;
Result:
+--------+ | Result | +--------+ | 1 | +--------+
The next example returns a negative result.
SET @doc = '{"a": 1, "b": 2, "c": [3, 4, 5]}';
SELECT JSON_CONTAINS_PATH(@doc, 'all', '$.c[3]') AS Result;
Result:
+--------+ | Result | +--------+ | 0 | +--------+
This returns negative because arrays use zero based numbering. Therefore, the path $.c[3] represents the fourth element, but the array only contains three elements.