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