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 [pick_type] [RETURNING type] [on_empty] [on_error])

pick_type:
    PICK JSON_NUMBER | PICK JSON_STRING

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.

• pick_type: Optional. Specifies the type of the extracted value. Valid values are as follows:

  • PICK JSON_NUMBER: Extracts only values of the JSON number type. If the target value is not of the number type, NULL is returned.

  • PICK JSON_STRING: Extracts only values of the JSON string type. If the target value is not of the string type, NULL is returned.

  If you do not specify pick_type, no filtering by value type is performed.

• 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

The following examples demonstrate the usage of PICK JSON_NUMBER and PICK JSON_STRING: PICK JSON_NUMBER returns the target value only if it is a JSON number; otherwise, it returns NULL. PICK JSON_STRING behaves in the opposite way.

obclient> CREATE TABLE t1(c1 JSON);

obclient> INSERT INTO t1 VALUES ('{"a":1}'), ('{"a":"1"}');

obclient> SELECT JSON_VALUE(c1, '$.a' PICK JSON_NUMBER) AS num FROM t1;
+------+
| num  |
+------+
| 1    |
| NULL |
+------+
2 rows in set

obclient> SELECT JSON_VALUE(c1, '$.a' PICK JSON_STRING) AS str FROM t1;
+------+
| str  |
+------+
| NULL |
| 1    |
+------+
2 rows in set