JSON_VALUE

Description

This function extracts a value from the specified path in a JSON document and returns the extracted value, which can be optionally converted to the desired data type.

Syntax

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

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

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

Description

Parameters

The following table describes the parameters:

• json_doc: specifies a valid JSON document.

• path: specifies a JSON path to a location in the document. This parameter is a string.

• type: specifies the data type. Valid values are:

  • FLOAT

  • DOUBLE

  • DECIMAL

  • SIGNED

  • UNSIGNED

  • DATE

  • TIME

  • DATETIME

  • YEAR (does not support YEAR(1) and YEAR(2))

  • CHAR

  • JSON

• If the RETURNING clause is not specified, the return type of this function is VARCHAR(512). If no character set is specified for the return type, JSON_VALUE() uses the utf8mb4 and binary collations, and is case-sensitive. If you specify utf8mb4 as the character set for the result, the server uses the default collation for this character set and is not case-sensitive.

• The on_empty clause specifies the behavior of JSON_VALUE() when no data is found at the specified path. Valid values are:

  • NULL ON EMPTY: JSON_VALUE() returns NULL. This is the default behavior for ON EMPTY.

  • DEFAULT value ON EMPTY: returns the provided value, which must match the return type.

  • ERROR ON EMPTY: the function throws an error.

• The on_error clause specifies the behavior of JSON_VALUE() when an error occurs. Valid values are:

  • NULL ON ERROR: JSON_VALUE() returns NULL. If the ON ERROR clause is not specified, this is the default behavior.

  • DEFAULT value ON ERROR: returns the provided value, which must match the return type.

  • ERROR ON ERROR: the function throws an error.

  tip

  If the ON EMPTY clause is used, it must appear before all ON ERROR clauses. An incorrect order will result in a syntax error.

Error handling

By default, JSON_VALUE() checks the validity of all JSON inputs (document and path). If any input is invalid, it throws an SQL error without triggering the ON ERROR clause.

The ON ERROR clause is triggered in the following cases:

• The input path is resolved to multiple paths in the JSON document when extracting an object or array.

• A conversion error occurs. For example, when trying to convert 'asdf' to an UNSIGNED value.

• Data truncation occurs.

Even if you specify NULL ON ERROR or DEFAULT ... ON ERROR, a conversion error will always trigger a warning.

The ON EMPTY clause is triggered when no data is present at the specified location (path) in the source JSON document (json_doc).

Examples

SELECT JSON_VALUE('{"fname": "Smith", "lname": "Will"}', '$.fname');
+--------------------------------------------------------------+
| JSON_VALUE('{"fname": "Smith", "lname": "Will"}', '$.fname') |
+--------------------------------------------------------------+
| Smith                                                        |
+--------------------------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_VALUE('{"item": "shoes", "price": "69.73"}', '$.price'
     RETURNING DECIMAL(4,2)) AS price;
+-------+
| price |
+-------+
| 69.73 |
+-------+
1 row in set (0.001 sec)