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] ...])
Considerations
The json_doc parameter specifies the JSON document, the search_str parameter specifies the search string, the escape_char parameter specifies a constant expression, and the path parameter specifies the path.
If any parameter is NULL, the function returns NULL. The function also returns NULL if the path does not exist in the document or the specified string is not found.
The one_or_all parameter can be set to the following values:
'one': The search terminates after the first match and returns a path string.'all': All matching path strings are returned, without duplicates. If multiple strings are found, they are automatically wrapped in an array. The order of the array elements is not guaranteed to be sorted.
The % and _ characters in the search_str parameter function the same as in the LIKE operator. The meanings are as follows:
%matches any number of characters (including zero characters)._matches exactly one character.
If you want to specify % or _ in the search string, you must escape them with the escape character. If the escape_char parameter is not specified or is NULL, the default escape character is \. Otherwise, escape_char must be a constant or a single character.
Regardless of the database encoding, search_str and path are always treated as utf8mb4 strings.
The following conditions will result in an error:
The
json_docparameter does not specify a valid JSON document.The
pathparameter is not a valid path expression.The
one_or_allparameter is not set to'one'or'all'.The
escape_charparameter is 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