JSON_VALUE

Purpose

You can call this function to extract a value in a JSON document from the specified path and return the extracted value. You can also set the function to convert the extracted value 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

This section describes the related parameters:

• json_doc specifies the JSON document.

• path is a JSON path that points to a location in the document, and must be a string.

• type supports the following data types:

  • FLOAT

  • DOUBLE

  • DECIMAL

  • SIGNED

  • UNSIGNED

  • DATE

  • TIME

  • DATETIME

  • YEAR. Note that YEAR(1) and YEAR(2) are not supported.

  • CHAR

  • JSON

• If you do not use a RETURNING clause, the data type of the return value of the function is VARCHAR(512). If you do not specify the character set for the return type, JSON_VALUE() uses the UTF8MB4 character set and a binary collation, and the return value is case-sensitive. If you specify the UTF8MB4 character set for the result, the server uses the default collation for the character set, and the return value is case-insensitive.

• You can specify the on_empty clause to determine the behavior of JSON_VALUE() when no data is found at the specified path. Supported values of this clause:

  • NULL ON EMPTY: specifies that JSON_VALUE() returns NULL. This is the default value of on_empty.

  • DEFAULT value ON EMPTY': returns the specified value whose type must match the value of the return type.

  • ERROR ON EMPTY: returns an error.

• When an error occurs, on_error takes one of the following values:

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

  • DEFAULT value ON ERROR: returns the specified value whose type must match the value of the return type.

  • ERROR ON ERROR: returns an error.

Notice

When you specify the on_empty clause, you must put it before all ON ERROR clauses.

Error handling

JSON_VALUE() checks the validity of all JSON inputs of documents and paths. If an input is invalid, an SQL error is returned without triggering the ON ERROR clause.

The ON ERROR clause is triggered if one of the following events occurs:

• When you try to extract an object or array, the specified path is parsed into multiple paths in the JSON document.

• A conversion error occurs when you, for example, try to convert 'asdf' to an UNSIGNED value.

• Data is truncated.

Even if you specify NULL ON ERROR or DEFAULT ... ON ERROR, a conversion error always triggers an alert.

When the source JSON document specified for json_doc contains no data at the specified path, the ON EMPTY clause is triggered.

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