MariaDB JSON_SEARCH() Function

In MariaDB, JSON_SEARCH() is a built-in function that returns the path of a given value within a given JSON document.

MariaDB JSON_SEARCH() Syntax

Here is the syntax for the MariaDB 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', 'all'. Whether JSON_SEARCH() returns all matching paths depends on the one_or_all parameter:

  • 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.

• search_str

  Required. The string to be searched for. You can use % or _ wildcards in the search_str parameter, just like LIKE:

  • % matches any characters with any length.
  • _ match any character.

• escape_char

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

• path

  Optional. Searches can only be performed under this path.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_SEARCH'.

Return value

MariaDB JSON_SEARCH() function returns the path of a given string within a JSON document. It returns a path string or an array of paths.

The MariaDB JSON_SEARCH() function wills return when NULL:

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

MariaDB JSON_SEARCH() Examples

Let’s first create a JSON document used in 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.

Example: search string

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

Output:

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

Example: one vs all

SELECT
    JSON_SEARCH(@json, 'one', 'Sports'),
    JSON_SEARCH(@json, 'all', 'Sports')\G

Output:

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

Example: Using wildcards

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

Output:

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

Conclusion

In MariaDB, JSON_SEARCH() is a built-in function that returns the path of a given value within a given JSON document.