MySQL JSON_SEARCH() Function

In MySQL, the JSON_SEARCH() function returns the path of a given string in a JSON document.

`JSON_SEARCH()` Syntax

Here is the syntax of the MySQL JSON_SEARCH() function:

JSON_SEARCH(json, one_or_all, search_str)
JSON_SEARCH(json, one_or_all, search_str, escape_char)
JSON_SEARCH(json, one_or_all, search_str, escape_char, path)

Parameters

json

Required. A JSON document.
one_or_all

Required. Available values: 'one' and 'all'. This parameter effects the search as follows:
- If it is 'one', the JSON_SEARCH() function will return the first matching path.
- If it is 'all', the JSON_SEARCH() function will return all matching paths. All paths will be wrapped in an array and returned.
search_str

Required. The string to search for. You can use % and _ wildcards in the parameter, just like LIKE:
- % matches any number of any characters.
- _ matches an arbitrary character.
escape_char

Optional. If search_str contains % or _, you need to add a escape character before them. The default is \.
path

Optional. Only perform a search in the part specified by the path.

Return value

The JSON_SEARCH() function returns a path string or an array including all matching path.

This parameter one_or_all effects the search as follows:

If it is 'one', the JSON_SEARCH() function will return the first matching path.
If it is 'all', the JSON_SEARCH() function will return all matching paths. All paths will be wrapped in an array and returned.

The JSON_SEARCH() function will return NULL in the following cases:

The specified string was not found
The specified path does not exist in the JSON document
Any parameter is NULL

There will happen an error in the following cases:

MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.
If the parameter path is not a valid path expression, MySQL will give an error.

`JSON_SEARCH()` Examples

First, Let’s create a JSON document for the following examples:

SET @json = '[
  {
    "name": "Tim",
    "age": 20,
    "hobbies": [
      { "name": "Car", "weight": 10 },
      { "name": "Sports", "weight": 20 }
    ]
  },
  {
    "name": "Tom",
    "age": 20,
    "hobbies": [
      { "name": "Reading", "weight": 10 },
      { "name": "Sports", "weight": 20 }
    ]
  }
]';

Here, we create a JSON array that contains two user information.

Seach

SELECT JSON_SEARCH(@json, 'one', 'Tim');

+----------------------------------+
| JSON_SEARCH(@json, 'one', 'Tim') |
+----------------------------------+
| "$[0].name"                      |
+----------------------------------+

`one` vs `all`

SELECT
    JSON_SEARCH(@json, 'one', 'Sports'),
    JSON_SEARCH(@json, 'all', 'Sports');

+-------------------------------------+--------------------------------------------------+
| JSON_SEARCH(@json, 'one', 'Sports') | JSON_SEARCH(@json, 'all', 'Sports')              |
+-------------------------------------+--------------------------------------------------+
| "$[0].hobbies[1].name"              | ["$[0].hobbies[1].name", "$[1].hobbies[1].name"] |
+-------------------------------------+--------------------------------------------------+

Using wildcards

SELECT JSON_SEARCH(@json, 'all', 'S%');

+--------------------------------------------------+
| JSON_SEARCH(@json, 'all', 'S%')                  |
+--------------------------------------------------+
| ["$[0].hobbies[1].name", "$[1].hobbies[1].name"] |
+--------------------------------------------------+

Here, we searched for a string with wildcard S% that means there can be any characters after S.

JSON_SEARCH() Syntax