JSON_SEARCH

Description

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, 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 specified string is not found in the document or if the path does not exist.

The one_or_all parameter can be set to the following values:

• 'one': The search stops after the first match and returns a single path string.

• 'all': The search returns all matching path strings 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.

For the search_str parameter, the % and _ characters function similarly to the LIKE operator, with the following meanings:

• % matches any number of characters (including zero characters).

• _ matches exactly one character.

To specify % or _ in the search string, you must use an escape character before them. If the escape_char parameter is not provided or is NULL, the default escape character is \. Otherwise, escape_char must be a constant or a single character.

Regardless of the database's encoding value, search_str and path are always treated as utf8mb4 strings.

The following situations will result in an error:

• The json_doc parameter specifies an invalid JSON document.

• The path parameter is not a valid path expression.

• The one_or_all parameter is not 'one' or 'all'.

• The escape_char parameter is not a constant expression.

Examples

SET @jn = '["abcd", [{"k": "10"}, "efg"], {"x":"abcd"}, {"y":"cdef"}]';
Query OK, 0 rows affected (0.000 sec)

SELECT JSON_SEARCH(@jn, 'one', 'abcd');
+---------------------------------+
| JSON_SEARCH(@jn, 'one', 'abcd') |
+---------------------------------+
| "$[0]"                          |
+---------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', 'abcd');
+---------------------------------+
| JSON_SEARCH(@jn, 'all', 'abcd') |
+---------------------------------+
| ["$[0]", "$[2].x"]              |
+---------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', 'ghk');
+--------------------------------+
| JSON_SEARCH(@jn, 'all', 'ghk') |
+--------------------------------+
| NULL                           |
+--------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', '10');
+-------------------------------+
| JSON_SEARCH(@jn, 'all', '10') |
+-------------------------------+
| "$[1][0].k"                   |
+-------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', '10', NULL, '$');
+------------------------------------------+
| JSON_SEARCH(@jn, 'all', '10', NULL, '$') |
+------------------------------------------+
| "$[1][0].k"                              |
+------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', '10', NULL, '$[*]');
+---------------------------------------------+
| JSON_SEARCH(@jn, 'all', '10', NULL, '$[*]') |
+---------------------------------------------+
| "$[1][0].k"                                 |
+---------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', '10', NULL, '$[*][0].k');
+--------------------------------------------------+
| JSON_SEARCH(@jn, 'all', '10', NULL, '$[*][0].k') |
+--------------------------------------------------+
| "$[1][0].k"                                      |
+--------------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', '10', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@jn, 'all', '10', NULL, '$[1]') |
+---------------------------------------------+
| "$[1][0].k"                                 |
+---------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', '10', NULL, '$[1][0]');
+------------------------------------------------+
| JSON_SEARCH(@jn, 'all', '10', NULL, '$[1][0]') |
+------------------------------------------------+
| "$[1][0].k"                                    |
+------------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', 'abc', NULL, '$[2]');
+----------------------------------------------+
| JSON_SEARCH(@jn, 'all', 'abc', NULL, '$[2]') |
+----------------------------------------------+
| NULL                                         |
+----------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', '%a%');
+--------------------------------+
| JSON_SEARCH(@jn, 'all', '%a%') |
+--------------------------------+
| ["$[0]", "$[2].x"]             |
+--------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', '%b%');
+-------------------------------+
| JSON_SEARCH(@jn, 'all', '%b%') |
+-------------------------------+
| ["$[0]", "$[2].x", "$[3].y"]  |
+-------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', '%b%', NULL, '$[0]');
+---------------------------------------------+
| JSON_SEARCH(@jn, 'all', '%b%', NULL, '$[0]') |
+---------------------------------------------+
| "$[0]"                                      |
+---------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', '%b%', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@jn, 'all', '%b%', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', '%b%', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@jn, 'all', '%b%', NULL, '$[1]') |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_SEARCH(@jn, 'all', '%b%', '', '$[1]');
+-------------------------------------------+
| JSON_SEARCH(@jn, 'all', '%b%', '', '$[1]') |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+
1 row in set (0.001 sec)