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.