Purpose
This function returns the path of a specified string in a JSON document.
Syntax
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
Description
The json_doc parameter specifies the JSON document, search_str is the search string parameter, escape_char specifies a constant expression, and path is the path parameter.
If any parameter is NULL, the function returns NULL. It also returns NULL if the specified path does not exist in the document or if the specified string is not found.
The valid values of the one_or_all parameter are as follows:
'one': The search terminates after the first match and returns a path string.'all': Searches for all matching path strings and returns them without duplicates. If multiple strings are found, they are automatically wrapped into an array. The order of the array elements is not guaranteed.
For the search_str parameter, the % and _ characters have the same meaning as the LIKE operator, as follows:
%matches any number of characters, including zero characters._matches exactly one character.
To specify the % or _ character in the search string, precede it with an escape character. If the escape_char parameter is missing or set to NULL, the default escape character is \. Otherwise, escape_char must be a null constant or character.
Regardless of the database's encoding value, search_str and path are always treated as UTF-8MB4 strings.
An error occurs in the following situations:
The
json_docparameter does not specify a valid JSON document.The
pathparameter is not a valid path expression.The value of the
one_or_allparameter is not'one'or'all'.escape_charis not a constant expression.
Examples
obclient> SET @jn = '["abcd", [{"k": "10"}, "efg"], {"x":"abcd"}, {"y":"cdef"}]';
Query OK, 0 rows affected
obclient> SELECT JSON_SEARCH(@jn, 'one', 'abcd');
+---------------------------------+
| JSON_SEARCH(@jn, 'one', 'abcd') |
+---------------------------------+
| "$[0]" |
+---------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', 'abcd');
+---------------------------------+
| JSON_SEARCH(@jn, 'all', 'abcd') |
+---------------------------------+
| ["$[0]", "$[2].x"] |
+---------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', 'ghk');
+--------------------------------+
| JSON_SEARCH(@jn, 'all', 'ghk') |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', '10');
+-------------------------------+
| JSON_SEARCH(@jn, 'all', '10') |
+-------------------------------+
| "$[1][0].k" |
+-------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', '10', NULL, '$');
+------------------------------------------+
| JSON_SEARCH(@jn, 'all', '10', NULL, '$') |
+------------------------------------------+
| "$[1][0].k" |
+------------------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', '10', NULL, '$[*]');
+---------------------------------------------+
| JSON_SEARCH(@jn, 'all', '10', NULL, '$[*]') |
+---------------------------------------------+
| "$[1][0].k" |
+---------------------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', '10', NULL, '$[*][0].k');
+--------------------------------------------------+
| JSON_SEARCH(@jn, 'all', '10', NULL, '$[*][0].k') |
+--------------------------------------------------+
| "$[1][0].k" |
+--------------------------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', '10', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@jn, 'all', '10', NULL, '$[1]') |
+---------------------------------------------+
| "$[1][0].k" |
+---------------------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', '10', NULL, '$[1][0]');
+------------------------------------------------+
| JSON_SEARCH(@jn, 'all', '10', NULL, '$[1][0]') |
+------------------------------------------------+
| "$[1][0].k" |
+------------------------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', 'abc', NULL, '$[2]');
+----------------------------------------------+
| JSON_SEARCH(@jn, 'all', 'abc', NULL, '$[2]') |
+----------------------------------------------+
| NULL |
+----------------------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', '%a%');
+--------------------------------+
| JSON_SEARCH(@jn, 'all', '%a%') |
+--------------------------------+
| ["$[0]", "$[2].x"] |
+--------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', '%b%');
+-------------------------------+
| JSON_SEARCH(@jn, 'all', '%b%') |
+-------------------------------+
| ["$[0]", "$[2].x", "$[3].y"] |
+-------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', '%b%', NULL, '$[0]');
+---------------------------------------------+
| JSON_SEARCH(@jn, 'all', '%b%', NULL, '$[0]') |
+---------------------------------------------+
| "$[0]" |
+---------------------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', '%b%', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@jn, 'all', '%b%', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x" |
+---------------------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', '%b%', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@jn, 'all', '%b%', NULL, '$[1]') |
+---------------------------------------------+
| NULL |
+---------------------------------------------+
1 row in set
obclient> SELECT JSON_SEARCH(@jn, 'all', '%b%', '', '$[1]');
+-------------------------------------------+
| JSON_SEARCH(@jn, 'all', '%b%', '', '$[1]') |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
1 row in set