MariaDB JSON_SEARCH() Explained

In MariaDB, JSON_SEARCH() is a built-in function that allows you to get the path to a given value in a JSON document.

It accepts the JSON document and a string as arguments, and returns the path to the given string within the document.

Syntax

The syntax goes like this:

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

Where:

json_doc is the JSON document and search_str is the string.
return_arg is the keyword one or all. If you use one, only the first path is returned. Any other occurrences are ignored. Which path is considered “first” is undefined (according to the MariaDB documentation). If all is specified, the paths of all occurrences are returned. If there are multiple paths, they’re auto-wrapped as an array.
The escape_char argument is an optional character to use as an escape character.
The path argument is an optional argument to determine where the “top-level” path starts within the JSON document.

Example

Here’s an example to demonstrate:

SET @json = '
    { 
        "name" : "Wag", 
        "type" : "Dog" 
    }';

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

Result:

+----------------------------------+
| JSON_SEARCH(@json, 'one', 'Wag') |
+----------------------------------+
| "$.name"                         |
+----------------------------------+

Here’s an example of returning the path for an element in an array:

SET @json = '
    { 
        "product" : "Left Handed Screwdriver", 
        "sizes" : [ "Small", "Medium", "Large" ],
    }';

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

Result:

+-------------------------------------+
| JSON_SEARCH(@json, 'one', "Medium") |
+-------------------------------------+
| "$.sizes[1]"                        |
+-------------------------------------+

Arrays are zero-based, and so $.sizes[1] refers to the second element in the array.

Multiple Occurrences

If you want to return all paths that contain the string, use all instead of one for the second argument.

SET @json = '[
    { "name": "Wag", "type": "Dog", "weight": 20 },
    { "name": "Bark", "type": "Dog", "weight": 10 },
    { "name": "Meow", "type": "Cat", "weight": 7 }
]';

SELECT JSON_SEARCH(@json, 'all', 'Dog');

Result:

+----------------------------------+
| JSON_SEARCH(@json, 'all', "Dog") |
+----------------------------------+
| ["$[0].type", "$[1].type"]       |
+----------------------------------+

If we change all to one, here’s what happens:

SET @json = '[
    { "name": "Wag", "type": "Dog", "weight": 20 },
    { "name": "Bark", "type": "Dog", "weight": 10 },
    { "name": "Meow", "type": "Cat", "weight": 7 }
]';

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

Result:

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

Only one path is returned.

Specify a Path

Here’s an example that specifies a path for which to search within the document:

SET @json = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog", 
            "weight" : 20,
            "awards" : { 
                "NZ Dog Award" : "Top Dog", 
                "New York Marathon" : "Fastest Animal", 
                "Sumo 2021" : "Biggest Dog"
            }
        }
    }
';
SELECT JSON_SEARCH(
    @json, 
    'all',
    '%dog%',
    NULL,
    '$.details.awards'
    ) AS Result;

Result:

+-----------------------------------------------------------------+
| Result                                                          |
+-----------------------------------------------------------------+
| ["$.details.awards.NZ Dog Award", "$.details.awards.Sumo 2021"] |
+-----------------------------------------------------------------+

In this case, the string dog actually occurs three times within the document, but only twice below the specified path.

Also, we used NULL for the escape character argument, which results in the default escape character being used, which is the backslash (\).

Default Escape Character

By default, the escape character is a backslash (\).

Example:

SET @json = '[
    { "uid": "Wag", "pwd": "my%pwd" },
    { "uid": "Bark", "pwd": "my%%%pwd" },
    { "uid": "Bark", "pwd": "myBIGpwd" }
]';

SELECT 
    JSON_SEARCH(@json, 'all', 'my%pwd') AS "Not Escaped",
    JSON_SEARCH(@json, 'all', 'my\%pwd') AS "Escaped";

Result:

+--------------------------------------+------------+
| Not Escaped                          | Escaped    |
+--------------------------------------+------------+
| ["$[0].pwd", "$[1].pwd", "$[2].pwd"] | "$[0].pwd" |
+--------------------------------------+------------+

The percentage sign (%) is a wildcard character that matches any number of characters. Therefore, if we don’t escape it, then it will match any number of characters, including characters that are not percentage signs.

But when we escape the percentage sign with the escape character, it will only match when there is exactly one percentage sign in that location.

The above results reflect this.

Specify a Custom Escape Character

You can specify a custom escape character if required. To do this, provide it as the fourth argument.

Example:

SET @json = '[
    { "uid": "Wag", "pwd": "my%pwd" },
    { "uid": "Bark", "pwd": "my%%%pwd" },
    { "uid": "Bark", "pwd": "myBIGpwd" }
]';

SELECT 
    JSON_SEARCH(@json, 'all', 'my%pwd', '!') AS "Not Escaped",
    JSON_SEARCH(@json, 'all', 'my!%pwd', '!') AS "Escaped";

Result:

+--------------------------------------+------------+
| Not Escaped                          | Escaped    |
+--------------------------------------+------------+
| ["$[0].pwd", "$[1].pwd", "$[2].pwd"] | "$[0].pwd" |
+--------------------------------------+------------+

So we get the same result as in the previous example. The only difference is that we specified a different escape character. In this case, we specified that the exclamation mark (!) is the escape character.

Null Arguments

If any of the search string, search string, or path arguments are NULL, the result is NULL:

SELECT 
    JSON_SEARCH(null, 'all', 's', '', '$') AS a,
    JSON_SEARCH('{"a":1}', 'all', null, '', '$') AS b,
    JSON_SEARCH('{"a":1}', 'all', 's', '', null) AS c;

Result:

+------+------+------+
| a    | b    | c    |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_SEARCH();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_SEARCH'

It’s the same when you provide too few arguments:

SELECT JSON_SEARCH('{"a":1}', 'all');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_SEARCH'