Purpose
JSON_SEARCH() returns the path to the specified string in a JSON document.
Syntax
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
Description
json_doc specifies the JSON document. search_str specifies the search string. escape_char specifies the constant expression. path specifies the path.
If any argument is NULL, the return value is NULL. If no path exists in the document or the specified string is not found, the return value is NULL.
You can set one_or_all to one of the following values:
'one': The search stops after the first match, and a path string is returned.'all': The search returns all matching path strings, and no duplicate paths are included. Multiple strings are automatically wrapped into an array. However, the array elements are not in a defined order.
The percent sign (%) and underscore (_) in search_str have the same effect as the LIKE operator:
%matches any number of characters, including zero characters._matches one character.
To specify a % or _ in the search string, add an escape character before it. If the escape_char is missing or NULL, the default escape character is a backslash (\). Otherwise, escape_char must be a constant that is empty or one character.
search_str and path are always used as utf8mb4 strings, regardless of their actual encoding value.
An error occurs in the following cases:
The JSON document specified for
json_docis invalid.The path expression specified for
pathis invalid.one_or_allis set to a value other than'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