top of page
CerebroSQL

MySQL: 

JSON_VALUE

Syntax:
JSON_VALUE(json_doc, path)

Extracts a value from a JSON document at the path given in the
specified document, and returns the extracted value, optionally
converting it to a desired type. The complete syntax is shown here:

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

json_doc is a valid JSON document.

path is a JSON path pointing to a location in the document.

type is one of the following data types:

o FLOAT

o DOUBLE

o DECIMAL

o SIGNED

o UNSIGNED

o DATE

o TIME

o DATETIME

o YEAR (MySQL 8.0.22 and later)

YEAR values of one or two digits are not supported.

o CHAR

o JSON

The types just listed are the same as the (non-array) types supported
by the CAST() function.

If not specified by a RETURNING clause, the JSON_VALUE() function's
return type is VARCHAR(512). When no character set is specified for the
return type, JSON_VALUE() uses utf8mb4 with the binary collation, which
is case-sensitive; if utf8mb4 is specified as the character set for the
result, the server uses the default collation for this character set,
which is not case-sensitive.

When the data at the specified path consists of or resolves to a JSON
null literal, the function returns SQL NULL.

on_empty, if specified, determines how JSON_VALUE() behaves when no
data is found at the path given; this clause takes one of the following
values:

o NULL ON EMPTY: The function returns NULL; this is the default ON
EMPTY behavior.

o DEFAULT value ON EMPTY: the provided value is returned. The value's
type must match that of the return type.

o ERROR ON EMPTY: The function throws an error.

If used, on_error takes one of the following values with the
corresponding outcome when an error occurs, as listed here:

o NULL ON ERROR: JSON_VALUE() returns NULL; this is the default
behavior if no ON ERROR clause is used.

o DEFAULT value ON ERROR: This is the value returned; its value must
match that of the return type.

o ERROR ON ERROR: An error is thrown.

ON EMPTY, if used, must precede any ON ERROR clause. Specifying them in
the wrong order results in a syntax error.

Error handling In general, errors are handled by JSON_VALUE() as
follows:

o All JSON input (document and path) is checked for validity. If any of
it is not valid, an SQL error is thrown without triggering the ON
ERROR clause.

o ON ERROR is triggered whenever any of the following events occur:

o Attempting to extract an object or an array, such as that resulting
from a path that resolves to multiple locations within the JSON
document

o Conversion errors, such as attempting to convert 'asdf' to an
UNSIGNED value

o Truncation of values

o A conversion error always triggers a warning even if NULL ON ERROR or
DEFAULT ... ON ERROR is specified.

o The ON EMPTY clause is triggered when the source JSON document (expr)
contains no data at the specified location (path).

URL: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

Example

mysql> SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
+--------------------------------------------------------------+
| JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname') |
+--------------------------------------------------------------+
| Joe |
+--------------------------------------------------------------+

mysql> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price'
-> RETURNING DECIMAL(4,2)) AS price;
+-------+
| price |
+-------+
| 49.95 |
+-------+

SELECT CAST(
JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) )
AS type
);

CREATE TABLE t1(
j JSON,
INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) )
);

mysql> EXPLAIN SELECT * FROM t1
-> WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: i1
key: i1
key_len: 9
ref: const
rows: 1
filtered: 100.00
Extra: NULL

CREATE TABLE t2 (
j JSON,
g INT GENERATED ALWAYS AS (j->"$.id"),
INDEX i1 (j)
);

mysql> EXPLAIN SELECT * FROM t2 WHERE g = 123\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ref
possible_keys: i1
key: i1
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL

bottom of page