JSON_SEARCH() – Find the Path to a String in a JSON Document in MySQL

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

You provide the JSON document as an argument to the function. You also provide the argument that determines the actual string to search (including any escape characters), as well as a keyword to indicate whether to return the path of all instances or just one.

Syntax

The syntax goes like this:

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

Explanation of each argument follows.

json_doc is the JSON document to search
one_or_all is the keyword one or all. If you use one, the search is stopped once the first occurrence is found. That is, the function only returns the path of the first instance of the search string. If all is specified, the paths of all occurrences are returned such that no duplicate paths are included. If there are multiple paths, they’re auto-wrapped as an array.
search_str is the actual string to return the path of.
escape_char is an optional character to use as an escape character. It must be a constant that is empty or one character. If you don’t specify this argument (or if it’s NULL), the escape character is the backslash (\).
path is an optional argument to determine where the “top-level” path starts within the JSON document.

Within the search_str argument, the % and _ characters work just as they do when used with the LIKE operator: % matches any number of characters (including zero characters), and _ matches exactly one character.

To specify a literal % or _ character in the search string, precede it by the escape character.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT 
  JSON_SEARCH('{"Name": "Bart", "Age": 10}', 'one', 'Bart') Result;

Result:

+----------+
| Result   |
+----------+
| "$.Name" |
+----------+

Example 2 – Arrays

Here’s an example of finding a string within an array.

SET @doc = '{"Name": "Bart", "Hobbies": ["Skateboarding", "Mischief"]}';
SELECT JSON_SEARCH(@doc, 'one', 'Mischief') Result;

Result:

+----------------+
| Result         |
+----------------+
| "$.Hobbies[1]" |
+----------------+

Arrays use zero-based numbering, so this result indicates the second element.

Example 3 – Non-Existent String

If you specify a string that doesn’t exist a NULL value is returned.

SET @doc = '{"Name": "Bart", "Hobbies": ["Skateboarding", "Mischief"]}';
SELECT JSON_SEARCH(@doc, 'one', 'Homer') Result;

Result:

+--------+
| Result |
+--------+
| NULL   |
+--------+

You’ll also get a NULL value if any of the json_doc, search_str, or path arguments are NULL or if no path exists within the JSON object.

Example 4 – Multiple Occurrences of a String

If the JSON document contains multiple occurrences of the same string, the result will depend on whether you specify one or all as the second argument.

If you use one, only the first occurrence is returned (assuming there’s at least one occurrence):

SET @doc = '{"Name": "Bart", "Friends": ["Bart", "Milhouse"]}';
SELECT JSON_SEARCH(@doc, 'one', 'Bart') Result;

Result:

+----------+
| Result   |
+----------+
| "$.Name" |
+----------+

If you use all, the paths of all occurrences are returned. If there’s more than one path, they’re auto-wrapped as an array.

SET @doc = '{"Name": "Bart", "Friends": ["Bart", "Milhouse"]}';
SELECT JSON_SEARCH(@doc, 'all', 'Bart') Result;

Result:

+----------------------------+
| Result                     |
+----------------------------+
| ["$.Name", "$.Friends[0]"] |
+----------------------------+

You can also specify a path that returns only those results from a specified path. More on that below (under Example 8 – Specify a Path).

Example 5 – Wildcards

You can use wildcard characters as specified in the syntax above. For example, you can use the % to match any number of characters.

SET @doc = '{"Name": "Bart", "Hobbies": ["Skateboarding", "Mischief"]}';
SELECT JSON_SEARCH(@doc, 'one', 'Skate%') Result;

Result:

+----------------+
| Result         |
+----------------+
| "$.Hobbies[0]" |
+----------------+

And you can use _ to match one character only.

SET @doc = '{"Name": "Bart", "Hobbies": ["Skateboarding", "Mischief"]}';
SELECT JSON_SEARCH(@doc, 'one', 'Bar_') Result;

Result:

+----------+
| Result   |
+----------+
| "$.Name" |
+----------+

If we were to use the _ in the previous example, we’d get a NULL result.

SET @doc = '{"Name": "Bart", "Hobbies": ["Skateboarding", "Mischief"]}';
SELECT JSON_SEARCH(@doc, 'one', 'Skate_') Result;

Result:

+--------+
| Result |
+--------+
| NULL   |
+--------+

Example 6 – Default Escape Character

If you need to search for a string that actually contains any of the above wildcard characters, you’ll need to escape the character. That tells MySQL to use it as a string literal (instead of interpreting it as a wildcard character).

SET @doc = '{"userid": "bart_simpson", "pwd": "pass%word"}';
SELECT JSON_SEARCH(@doc, 'one', 'pass\%word') Result;

Result:

+---------+
| Result  |
+---------+
| "$.pwd" |
+---------+

At first glance, you may be thinking that the backslash was unnecessary, because after all, we’d get the same result if we do this:

SET @doc = '{"userid": "bart_simpson", "pwd": "pass%word"}';
SELECT JSON_SEARCH(@doc, 'one', 'pass%word') Result;

Result:

+---------+
| Result  |
+---------+
| "$.pwd" |
+---------+

But the problem with this approach is that we also get the same result if we do this:

SET @doc = '{"userid": "bart_simpson", "pwd": "pass%BLAH-BLAH-BLAH-word"}';
SELECT JSON_SEARCH(@doc, 'one', 'pass%word') 'Result';

Result:

+---------+
| Result  |
+---------+
| "$.pwd" |
+---------+

So the backslash informs MySQL that we’re only looking for a single instance of % as a string literal, and not for any number of other characters.

The same concept holds true for the underscore character.

If we do this:

SET @doc = '{"userid": "bart_simpson", "pwd": "pass%word"}';
SELECT 
  JSON_SEARCH(@doc, 'one', 'bart\_simpson') 'Escaped',
  JSON_SEARCH(@doc, 'one', 'bart_simpson') 'Not Escaped';

We get this:

+------------+-------------+
| Escaped    | Not Escaped |
+------------+-------------+
| "$.userid" | "$.userid"  |
+------------+-------------+

Both approaches return the same result.

But if we do this (replace the _ with J in the userid):

SET @doc = '{"userid": "bartJsimpson", "pwd": "pass%word"}';
SELECT 
  JSON_SEARCH(@doc, 'one', 'bart\_simpson') 'Escaped',
  JSON_SEARCH(@doc, 'one', 'bart_simpson') 'Not Escaped';

We get this:

+---------+-------------+
| Escaped | Not Escaped |
+---------+-------------+
| NULL    | "$.userid"  |
+---------+-------------+

Example 7 – Custom Escape Character

You can specify your own escape character if required. You do this by including it as an optional fourth argument.

Here’s the previous example rewritten to use a different escape character (the userid includes a _ character).

SET @doc = '{"userid": "bart_simpson", "pwd": "pass%word"}';
SELECT 
  JSON_SEARCH(@doc, 'one', 'bart$_simpson', '$') 'Escaped',
  JSON_SEARCH(@doc, 'one', 'bart_simpson') 'Not Escaped';

Result:

+------------+-------------+
| Escaped    | Not Escaped |
+------------+-------------+
| "$.userid" | "$.userid"  |
+------------+-------------+

And if we replace the _ with J in the userid:

SET @doc = '{"userid": "bartJsimpson", "pwd": "pass%word"}';
SELECT 
  JSON_SEARCH(@doc, 'one', 'bart$_simpson', '$') 'Escaped',
  JSON_SEARCH(@doc, 'one', 'bart_simpson') 'Not Escaped';

Result:

+---------+-------------+
| Escaped | Not Escaped |
+---------+-------------+
| NULL    | "$.userid"  |
+---------+-------------+

Example 8 – Specify a Path

You can also specify a path from which to start searching from. Here’s an example.

SET @data = '{  
    "Person": {    
       "Name": "Bart", 
       "Age": 10,
       "Friends": ["Bart", "Milhouse"]  
    }
 }';
SELECT JSON_SEARCH(@data, 'all', 'Bart', NULL, '$.Person.Friends') AS 'Result';

Result:

+-----------------------+
| Result                |
+-----------------------+
| "$.Person.Friends[0]" |
+-----------------------+

If we hadn’t specified a path, we’d get the following result.

SET @data = '{  
    "Person": {    
       "Name": "Bart", 
       "Age": 10,
       "Friends": ["Bart", "Milhouse"]  
    }
 }';
SELECT JSON_SEARCH(@data, 'all', 'Bart') AS 'Result';

Result:

+------------------------------------------+
| Result                                   |
+------------------------------------------+
| ["$.Person.Name", "$.Person.Friends[0]"] |
+------------------------------------------+

Furthermore, if we’d specified one as the second argument (as well as omitting the path argument), we’d end up with the following.

SET @data = '{  
    "Person": {    
       "Name": "Bart", 
       "Age": 10,
       "Friends": ["Bart", "Milhouse"]  
    }
 }';
SELECT JSON_SEARCH(@data, 'one', 'Bart') AS 'Result';

Result:

+-----------------+
| Result          |
+-----------------+
| "$.Person.Name" |
+-----------------+

Example 9 – Empty Document

If the document contains no paths, you’ll get a NULL value.

SELECT 
  JSON_SEARCH('{}', 'all', 'Bart') 'Result';

Result:

+--------+
| Result |
+--------+
| NULL   |
+--------+