In MySQL, the JSON_VALUE() function extracts a value from a JSON document at the specified path.

The function was introduced in MySQL 8.0.21.

Syntax

The syntax goes like this:

JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

Where:

on_empty:
    {NULL | ERROR | DEFAULT value} ON EMPTY

on_error:
    {NULL | ERROR | DEFAULT value} ON ERROR

Example

Here’s a simple example to demonstrate:

SELECT JSON_VALUE( '{ "name" : "Wag", "type" : "Dog" }', '$.type' );

Result:

Dog

Arrays

Here’s an example of getting a value from an array:

SELECT JSON_VALUE( '{ "name" : "Wag", "scores" : [ 25, 36, 48 ] }', '$.scores[1]' );

Result:

36

Arrays are zero based, so 1 returns the second element (0 would return the first).

Embedded Documents

Here’s an example of getting a value from an embedded object:

SELECT JSON_VALUE(
    '{ 
        "_id" : 1, 
        "details" : { 
            "name" : "Wag",
            "type" : "Dog"
            } 
    }', 
    '$.details.type' 
    );

Result:

Dog

Return Type

Here’s an example of specifying the return type:

SELECT JSON_VALUE(
    '{ 
        "_id" : 1, 
        "details" : { 
            "name" : "Wag",
            "type" : "Dog"
            } 
    }', 
    '$.details' RETURNING json
    );

Result:

{"name": "Wag", "type": "Dog"}

If you don’t specify the return type, the return type is VARCHAR(512).

Empty Results

By default, if no data is found at the specified path, NULL is returned:

SELECT JSON_VALUE(
    '{ 
        "name" : "Wag",
        "type" : "Dog"
    }', 
    '$.score'
    );

Result:

NULL

However, the following options can be used to explicitly specify what should happen when no data is found at the given path:

• NULL ON EMPTY: The function returns NULL; this is the default behaviour.
• DEFAULT value ON EMPTY: the provided value is returned. The value’s type must match that of the return type.
• ERROR ON EMPTY: The function throws an error.

Example:

SELECT JSON_VALUE(
    '{ 
        "name" : "Wag",
        "type" : "Dog"
    }', 
    '$.score' 
    DEFAULT 'Nothing found' ON EMPTY
    );

Result:

Nothing found

Errors

The following can be used to specify what happens when an error occurs:

• NULL ON ERROR: JSON_VALUE() returns NULL; this is the default behaviour.
• DEFAULT value ON ERROR: This is the value returned; its value must match that of the return type.
• ERROR ON ERROR: An error is thrown.

If used, ON EMPTY must precede any ON ERROR clause. Specifying them in the wrong order results in a syntax error.

Indexes

As mentioned in the MySQL 8.0.21 release notes, the JSON_VALUE() function simplifies the creation of indexes on JSON columns. A call to JSON_VALUE(json_doc, path RETURNING type) is equivalent to calling CAST( JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) ) AS type).

Here’s the example used in the release notes:

CREATE TABLE inventory(
    items JSON,
    INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING CHAR(50))) ),
    INDEX i2 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2))) ),
    INDEX i3 ( (JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED)) )
);