In MariaDB, JSON_VALUE() is a built-in function that returns a scalar value from a JSON document. More specifically, it returns the scalar specified by the path provided.

Syntax

The syntax goes like this:

JSON_VALUE(json_doc, path)

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

Example

Here’s an example to demonstrate.

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

Result:

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

Non-Existent Path

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_VALUE(@json_document, '$.color');

Result:

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

Arrays

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

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

Result:

+-------------------------------------------+
| JSON_VALUE(@json_document, '$.awards[1]') |
+-------------------------------------------+
| Best Dog                                  |
+-------------------------------------------+

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

Nested Objects

Here’s an example of getting a value 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_VALUE(
    @json_document, 
    '$.details.awards.Florida Dog Awards'
    ) AS Result;

Result:

+---------+
| Result  |
+---------+
| Top Dog |
+---------+

Nonscalar Values

Attempting to return a nonscalar value (e.g. an object or an array) returns NULL.

Example:

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_VALUE(
    @json_document, 
    '$.details.awards'
    ) AS Result;

Result:

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

To return a nonscalar value, use the JSON_QUERY() function or the JSON_EXTRACT() function.

Null Arguments

If any argument is NULL, the result is NULL:

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

Result:

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

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_VALUE();

Result:

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

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

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

Result:

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