JSON_CONTAINS_PATH()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns a boolean to indicate whether a JSON object contains at least one of the specified JSON paths.
USAGE
JSON_CONTAINS_PATH(json_data, one_or_all, path[, path] ...)
Argument Name | Description |
|---|---|
| The JSON data to query |
| A string literal or variable that must have the value "one" or "all" |
| The path in the data where the query is targeted |
DETAILS
JSON_CONTAINS_PATH() is a JSON query function that returns a boolean (1 or 0) to indicate whether a valid JSON document contains the requested number of JSON paths.
JSON data is valid if it conforms to ECMA-404: The JSON Data Interchange Standard.
The one_or_all parameter must be a string with one of two values:
oneindicates that at least one of the paths must exist in the JSON dataallindicates that all of the paths must exist in the JSON data
A NULL is returned if the JSON object or any of the paths are NULL, JSON_CONTAINS_PATH().
EXAMPLES
With Literal Values
In the following example, JSON_CONTAINS_PATH() is called with a JSON literal value, both cardinality options, and some JSON paths. The result demonstrates how to check whether the JSON contains specific paths:
SELECT JSON_CONTAINS_PATH(
'{"foo": [1, 2, 3], "bar": "baz"}',
'all', '$.foo', '$.bar'
) AS result1,
JSON_CONTAINS_PATH(
'{"foo": [1, 2, 3], "bar": "baz"}',
'one', '$.bar', '$.bad', '$.other'
) AS result2;
+---------+---------+
| result1 | result2 |
+---------+---------+
| 1 | 1 |
+---------+---------+
SELECT JSON_CONTAINS_PATH(NULL, 'all', '$') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
Example Schema and Data
Some of the examples are based on the contacts table:
CREATE TABLE contacts (
contact JSON
);
INSERT INTO contacts VALUES
('{"email": "John_smith@example.com", "name1": "John", "name2": "Smith"}'),
('{"email": "Jon_smith@example.com", "name1": "Jon", "name2": "Smith"}'),
('{"email": "Johnny_smith@example.com", "name1": "Johnny", "name2": "Smith"}'),
('{"name1": "Jonny", "name2": "Smith"}');
Per-row Query
This example tests if an entire set of the paths exists within each row's JSON data:
SELECT contact,
JSON_CONTAINS_PATH(
contact,
'all', '$.name1', '$.name2', '$.email'
) AS contains
FROM contacts\G
*************************** 1. row ***************************
contact: {"email": "John_smith@example.com", "name1": "John", "name2": "Smith"}
contains: 1
*************************** 2. row ***************************
contact: {"email": "Jon_smith@example.com", "name1": "Jon", "name2": "Smith"}
contains: 1
*************************** 3. row ***************************
contact: {"email": "Johnny_smith@example.com", "name1": "Johnny", "name2": "Smith"}
contains: 1
*************************** 4. row ***************************
contact: {"name1": "Jonny", "name2": "Smith"}
contains: 0
This example tests if at least one of the paths exists within each row's JSON data:
SELECT contact,
JSON_CONTAINS_PATH(
contact,
'one', '$.name1', '$.email'
) AS contains
FROM contacts\G
*************************** 1. row ***************************
contact: {"email": "John_smith@example.com", "name1": "John", "name2": "Smith"}
contains: 1
*************************** 2. row ***************************
contact: {"email": "Jon_smith@example.com", "name1": "Jon", "name2": "Smith"}
contains: 1
*************************** 3. row ***************************
contact: {"email": "Johnny_smith@example.com", "name1": "Johnny", "name2": "Smith"}
contains: 1
*************************** 4. row ***************************
contact: {"name1": "Jonny", "name2": "Smith"}
contains: 1