Description
This function extracts values from a JSON document at the specified path and returns the extracted values. You can also choose to convert them into 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 related parameters are described as follows:
The
json_docparameter specifies a valid JSON document.The
pathparameter is a JSON path that points to a location in the document and must be a string.The
typeparameter supports the following data types:FLOATDOUBLEDECIMALSIGNEDUNSIGNEDDATETIMEDATETIMEYEAR(not supported forYEAR(1)andYEAR(2)))CHARJSON
If the
RETURNINGclause is not used, the return type of the function isVARCHAR(512). If no character set is specified for the return type,JSON_VALUE()uses the utf8mb4 binary collation and is case-sensitive; If utf8mb4 is specified as the character set for the result, the server uses the default collation of this character set, which is case-insensitive.The
on_emptyclause specifies the behavior ofJSON_VALUE()when no data is found in the specified path. Valid values are as follows:NULL ON EMPTY:JSON_VALUE()returnsNULL. This is the default behavior ofON EMPTY.DEFAULT value ON EMPTY': Returns the providedvalue, which must match the return type.ERROR ON EMPTY: The function throws an error.
When an error occurs, the
on_errorvalue is as follows:NULL ON ERROR:JSON_VALUE()returnsNULL. This is the default behavior if theON ERRORclause is not used.DEFAULT value ON ERROR: Returns the providedvalue, which must match the type of the return value.ERROR ON ERROR: Throws an error.
Notice
The position of
ON EMPTY(if used) must be before allON ERRORclauses. An incorrect order will result in a syntax error.
Error handling
Normally, JSON_VALUE() checks the validity of all JSON inputs (documents and paths). If any one of them is invalid, it throws an SQL error without triggering the ON ERROR clause.
The ON ERROR clause is triggered when any of the following events occurs:
When trying to extract an object or array, the input path is parsed into multiple paths in the JSON document.
Conversion errors. For example, attempting to convert
'asdf'to anUNSIGNEDvalue.Data truncation.
Even if NULL ON ERROR or DEFAULT ... ON ERROR is specified, conversion errors will always trigger an alert.
The ON EMPTY clause is triggered when the source JSON document (json_doc) does not contain any data at the specified position (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