In MariaDB, JSON_EXTRACT() is a built-in function that extracts data from a JSON document, based on a given path or paths.

It can return single values and multiple values. If a single value is matched, a single value is returned. If multiple values are matched, then those values are returned in an array.

Syntax

The syntax goes like this:

JSON_EXTRACT(json_doc, path[, path] ...)

Where json_doc is the JSON document, and each path argument is a path within the document.

Example

Here’s an example to demonstrate.

SET @json_document = '
    { 
        "name": "Wag", 
        "type": "Dog", 
        "weight": 20 
    }
';
SELECT JSON_EXTRACT(@json_document, '$.name');

Result:

+----------------------------------------+
| JSON_EXTRACT(@json_document, '$.name') |
+----------------------------------------+
| "Wag"                                  |
+----------------------------------------+

Multiple Paths

Here’s an example of specifying multiple paths in order to return multiple values from the JSON document.

When you return multiple values, they are returned in an array.

SET @json_document = '
    { 
        "name": "Wag", 
        "type": "Dog", 
        "weight": 20 
    }
';
SELECT JSON_EXTRACT(@json_document, '$.name', '$.weight');

Result:

+----------------------------------------------------+
| JSON_EXTRACT(@json_document, '$.name', '$.weight') |
+----------------------------------------------------+
| ["Wag", 20]                                        |
+----------------------------------------------------+

Non-Existent Paths

Passing a path that doesn’t exist in the JSON document results in NULL.

Example:

SET @json_document = '
    { 
        "name": "Wag", 
        "type": "Dog", 
        "weight": 20 
    }
';
SELECT JSON_EXTRACT(@json_document, '$.color');

Result:

+-----------------------------------------+
| JSON_EXTRACT(@json_document, '$.color') |
+-----------------------------------------+
| NULL                                    |
+-----------------------------------------+

However, if multiple paths are passed, and at least one of them matches, then the matching value is extracted and returned in an array. This is true even if only one value is extracted.

Example:

SET @json_document = '
    { 
        "name": "Wag", 
        "type": "Dog", 
        "weight": 20 
    }
';
SELECT JSON_EXTRACT(@json_document, '$.name', '$.color');

Result:

+---------------------------------------------------+
| JSON_EXTRACT(@json_document, '$.name', '$.color') |
+---------------------------------------------------+
| ["Wag"]                                           |
+---------------------------------------------------+

Arrays

Here’s an example of extracting data from an array:

SET @json_document = '
    { 
        "_id" : 1, 
        "awards" : [ "Top Dog", "Best Dog", "Biggest Dog" ] 
    }
';
SELECT JSON_EXTRACT(@json_document, '$.awards[0]');

Result:

+---------------------------------------------+
| JSON_EXTRACT(@json_document, '$.awards[0]') |
+---------------------------------------------+
| "Top Dog"                                   |
+---------------------------------------------+

Arrays are zero-based, and so $.awards[0] extracts the first element of the awards array.

Nested Objects

Here’s an example of extracting data from an object nested inside another object:

SET @json_document = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog", 
            "weight" : 20,
            "awards" : { 
                "Florida Dog Awards" : "Top Dog", 
                "New York Marathon" : "Fastest Dog", 
                "Sumo 2020" : "Biggest Dog"
            }
        }
    }
';
SELECT JSON_EXTRACT(
    @json_document, 
    '$.details.awards.New York Marathon'
    ) AS Result;

Result:

+---------------+
| Result        |
+---------------+
| "Fastest Dog" |
+---------------+

However, if we wanted to extract all awards, we could shorten the path to $.details.awards:

SET @json_document = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog", 
            "weight" : 20,
            "awards" : { 
                "Florida Dog Awards" : "Top Dog", 
                "New York Marathon" : "Fastest Dog", 
                "Sumo 2020" : "Biggest Dog"
            }
        }
    }
';
SELECT JSON_EXTRACT(
    @json_document, 
    '$.details.awards'
    ) AS Result;

Result:

+---------------------------------------------------------------------------------------------------+
| Result                                                                                            |
+---------------------------------------------------------------------------------------------------+
| {"Florida Dog Awards": "Top Dog", "New York Marathon": "Fastest Dog", "Sumo 2020": "Biggest Dog"} |
+---------------------------------------------------------------------------------------------------+

Prettify the Result

We can make the result easier to read by passing JSON_EXTRACT() to the JSON_DETAILED() function:

SET @json_document = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog", 
            "weight" : 20,
            "awards" : { 
                "Florida Dog Awards" : "Top Dog", 
                "New York Marathon" : "Fastest Dog", 
                "Sumo 2020" : "Biggest Dog"
            }
        }
    }
';
SELECT 
    JSON_DETAILED(
        JSON_EXTRACT(
            @json_document, 
            '$.details.awards'
            ) 
    ) AS Result;

Result:

+------------------------------------------------+
| Result                                         |
+------------------------------------------------+
| {
    "Florida Dog Awards": "Top Dog",
    "New York Marathon": "Fastest Dog",
    "Sumo 2020": "Biggest Dog"
} |
+------------------------------------------------+

Null Arguments

If any argument is NULL, the result is NULL:

SELECT 
    JSON_EXTRACT(null, '$.type'),
    JSON_EXTRACT('{"a":1}', null);

Result:

+------------------------------+-------------------------------+
| JSON_EXTRACT(null, '$.type') | JSON_EXTRACT('{"a":1}', null) |
+------------------------------+-------------------------------+
| NULL                         | NULL                          |
+------------------------------+-------------------------------+

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_EXTRACT();

Result:

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

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

SELECT JSON_EXTRACT('{ "a": 1}');

Result:

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