JSON_SEARCH()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns the JSON path to a query or search string within a JSON object.
USAGE
JSON_SEARCH(json_data, cardinality,
search[, escape_char[, path] ...])
Argument Name | Description |
|---|---|
| The JSON data to search |
| This value must be |
| The wildcard string to search for |
| Optional. An alternate escape character for the wildcard string |
| Optional. A path specification that limits the search |
DETAILS
JSON_SEARCH() is a JSON query function that searches in a JSON data argument and returns a JSON Path representing the requested matches.
A NULL is returned if the search string is not found or if a data or path argument is NULL or invalid.
The number of search results returned is controlled by the cardinality argument:
Specifying
onereturns the first match as single JSON string orNULLfor no matches.Specifying
allreturns every matching path location, but the format of the result depends on how many matches occurred:No matches returns
NULLOne match returns a JSON string with the found path
More than one match returns a JSON array with each value being the JSON string with the found path
The search string uses the same wildcard characters as the LIKE operator:
The
%character matches any number of characters, including noneThe
_character matches exactly one characterThe escape character (a backslash by default) transforms the following character into a normal character that must be found
The optional escape_char argument can be used to change the escape character from a backslash into a character of your choosing. Specifying a NULL leaves it set to the default backslash (\) character.
When one or more path arguments are specified the search is limited to the inside the specified path elements of the json_data argument instead of searching everything.
MariaDB Xpand does not support matching numeric values in the JSON data, unlike MariaDB Server.
EXAMPLES
With Literal Values
In the following example, JSON_SEARCH() is called with literal values as key-value pairs. The result demonstrates how to replace JSON data at specified paths:
SET @j = '{"bar": "a string",
"baz": "another string",
"foo": "a third string" }';
SELECT JSON_SEARCH(@j, 'one', 'a %string') AS one,
JSON_SEARCH(@j, 'all', 'a %string') AS all_1,
JSON_SEARCH(@j, 'all', 'another%') AS all_2,
JSON_SEARCH(@j, 'all', 'missing') AS all_3;
+---------+--------------------+---------+-------+
| one | all_1 | all_2 | all_3 |
+---------+--------------------+---------+-------+
| "$.bar" | ["$.bar", "$.foo"] | "$.baz" | NULL |
+---------+--------------------+---------+-------+
SET @j = '[[1, 2, 42, 4, 5], 42, [2, 4, 6, 42]]';
SELECT JSON_SEARCH(@j, 'all', '6', NULL, '$[*]') AS six,
JSON_SEARCH(@j, 'all', '42', NULL, '$[*]') AS forty_two;
+------+-----------+
| six | forty_two |
+------+-----------+
| NULL | NULL |
+------+-----------+
SET @j = '[["1", "2", "42", "4", "5"], "42", ["2", "4", "6", "42"]]';
SELECT JSON_SEARCH(@j, 'all', '6', NULL, '$[*]') AS six,
JSON_SEARCH(@j, 'all', '42', NULL, '$[*]') AS forty_two;
+-----------+--------------------------------+
| six | forty_two |
+-----------+--------------------------------+
| "$[2][2]" | ["$[0][2]", "$[1]", "$[2][3]"] |
+-----------+--------------------------------+
Example Schema and Data
Some of the examples are based on the contacts table:
CREATE TABLE contacts (
contact JSON
);
INSERT INTO contacts(contact) 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"}');
Per-row Values
SELECT JSON_EXTRACT(contact, '$.email') AS email,
JSON_SEARCH(contact, 'all', 'Smith') AS paths
FROM contacts\G
*************************** 1. row ***************************
email: "John_smith@example.com"
paths: "$.name2"
*************************** 2. row ***************************
email: "Jon_smith@example.com"
paths: "$.name2"
*************************** 3. row ***************************
email: "Johnny_smith@example.com"
paths: "$.name2"
With Pattern Matching
Using the % wildcard character in the search string:
SELECT JSON_SEARCH('{"email": "John_smith@example.com",
"name2": "Smith",
"name1": "John"}',
'all', 'Jo%'
) AS paths\G
*************************** 1. row ***************************
paths: ["$.email", "$.name1"]
With 'one' Cardinality
The one cardinality returns just the first result:
SELECT JSON_SEARCH('{"email": "John_smith@example.com",
"name2": "Smith",
"name1": "John"}',
'one', '%m%'
) AS path;
+-----------+
| path |
+-----------+
| "$.email" |
+-----------+
With Search at Specific Path
Limiting the search to just the name1 key's value:
SELECT JSON_SEARCH('{"email": "John_smith@example.com",
"name2": "Smith",
"name1": "John"}',
'all', 'Jo%', NULL, '$.name1'
) AS path;
+-----------+
| path |
+-----------+
| "$.name1" |
+-----------+
With an Alternative Escape Character
The literal % and _ characters can be included in the search string itself by preceding/escaping them with the default escape character \. A different escape character than the default \ may be used by specifying it as the escape_char function argument. When the escape_char function argument is NULL, the default escape character \ is used.
SELECT JSON_SEARCH(
'{"email": "John_smith@example.com",
"name2": "Smith",
"name1": "John"}',
'all', '%/_%', '/'
) AS paths;
+-----------+
| paths |
+-----------+
| "$.email" |
+-----------+