JSON_VALUE

Purpose

This function extracts a value from a JSON document at the specified path and returns the extracted value, which can be optionally converted to the required 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

Considerations

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 a data type. Valid values are as follows:

  • 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). When no character set is specified for the return type, JSON_VALUE() uses the utf8mb4 character set and binary collation, 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 as follows:

  • 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 be of the same type as 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 as follows:

  • NULL ON ERROR: JSON_VALUE() returns NULL. This is the default behavior when the ON ERROR clause is not specified.

  • DEFAULT value ON ERROR: returns the provided value, which must be of the same type as the return type.

  • ERROR ON ERROR: throws an error.

  Notice

  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 (the 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 resolves to multiple paths in the JSON document when extracting an object or array.

• A conversion error occurs. For example, when you try 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 the source JSON document (json_doc) does not contain any data at the specified location (path).

Examples

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

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