MySQL 9.4 Reference Manual Including MySQL NDB Cluster 9.4
The functions listed in this section compose JSON values from component elements.
Evaluates a (possibly empty) list of values and returns a JSON array containing those values.
mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
+---------------------------------------------+
| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
+---------------------------------------------+
| [1, "abc", null, true, "11:30:24.000000"] |
+---------------------------------------------+
JSON_DUALITY_OBJECT([
table_annotations
]
json_duality_key_value_pairs
)
This function returns a JSON duality object for use in
CREATE JSON DUALITY VIEW
or
ALTER JSON DUALITY VIEW
.
Attempting to invoke it in any other context results in an
error.
JSON_DUALITY_OBJECT()
takes one or two
arguments: an optional table annotations expression, and a set
of key-value pairs in JSON object format, which must include a
key named _id
in the root object
representing the primary key of the root table. Absence of
this key results in an error. No sub-key may be named
_id
.
The complete syntax for the arguments to this function is shown here, with additional notes following:
table_annotations
: WITH (table_annotation
[,table_annotation
]...)table_annotation
: INSERT | UPDATE | DELETEjson_duality_key_value_pairs
:json_duality_key_value_pair
[,json_duality_key_value_pair
]...json_duality_key_value_pair
:key_name
:value_expression
value_expression
:column_name
| (singleton_descendent_json_object
) | (nested_descendent_json_objects
)singleton_descendent_json_object
: SELECTjson_duality_object_expression
FROMchild_table_name
[AStable_alias
] WHEREjson_duality_join_condition
nested_descendent_json_objects
: SELECT JSON_ARRAYAGG(json_duality_object_expression
[json_constructor_null_clause
]) FROMchild_table_name
[AStable_alias
] WHEREjson_duality_join_condition
json_constructor_null_clause
: NULL ON NULL | ABSENT ON NULLjson_duality_join_condition
: [schema_name
.]child_table_name
.column_name
= [schema_name
.]parent_table_name
.column_name
json_duality_object_expression
: JSON_DUALITY_OBJECT( [table_annotations_expression
]json_duality_key_value_pairs
)
json_duality_key_value_pairs
is a
set of key-value pairs in
key_name
:value_expression
format. There must be a key named _id
in
the root object, and it must correspond to a primary key
column of the table being projected; sub-keys named
_id
are not allowed.
value_expression
must be one of: a
column name; an object returned by
JSON_DUALITY_OBJECT()
(singleton descendant); an object returned by
JSON_ARRAYAGG()
(nested
descendant).
column_name
must reference a valid
column in the table that is being projected
(root_table_name
or
current_table_name
). The same
column_name
cannot be used more
than once in a single invocation of
JSON_DUALITY_OBJECT()
. Functions and
operators cannot be used with
column_name
. Columns of types
JSON
,
VECTOR
, and
GEOMETRY
(including all
derivatives such as POINT
,
LINESTRING
, and
POLYGON
) are not supported, nor
are generated columns. The column having the key
_id
in the root table for
json_duality_key_value_pairs
must
be a primary key of that table.
The
singleton_descendent_json_object
consists of a SELECT
statement with a
FROM
clause. The
SELECT
list and
FROM
clause follow the same rules as those
described for the top-level query in a
CREATE JSON DUALITY VIEW
statement.
nested_descendent_json_objects
selects a single expression
(json_duality_object_expression
)
using JSON_ARRAYAGG()
, which
must contain a non-empty
JSON_DUALITY_OBJECT()
. The select list and
FROM
clause follow the same rules as those
described for
singleton_descendent_json_object
.
The optional
json_constructor_null_clause
specifies the behavior of this function when
json_duality_object_expression
evaluates to null. It takes either of the values
ABSENT ON NULL
or NULL ON
NULL
(the default). NULL ON NULL
returns the JSON null
value;
ABSENT ON NULL
causes the value to be
omitted from the output JSON array.
singleton_descendent_json_object
and nested_descendent_json_objects
also support a WHERE
clause. This must
contain one expression only, having the form shown here:
[schema_name
.]child_table_name
.column_name
= [schema_name
.]parent_table_name
.column_name
No types of conditions other than equality are supported in
this WHERE
clause. Multiple conditions
using AND
or
OR
operators are also not
supported.
JSON_DUALITY_OBJECT()
takes an optional
table_annotations_expression
. This
expression consists of a comma-separated list of one or more
of the annotation values INSERT
,
UPDATE
, and DELETE
, in
any order. No annotation value may be listed more than once.
If the table is projected multiple times, the set of columns projected must be consistent across all instances of the table projection.
See Section 27.7, “JSON Duality Views”, for more examples,
as well as the descriptions of the CREATE
JSON DUALITY VIEW
and ALTER
JSON DUALITY VIEW
statements.
This function was added in MySQL 9.4.0.
JSON_OBJECT([
key
,
val
[,
key
,
val
] ...])
Evaluates a (possibly empty) list of key-value pairs and
returns a JSON object containing those pairs. An error occurs
if any key name is NULL
or the number of
arguments is odd.
mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+-----------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"} |
+-----------------------------------------+
Quotes a string as a JSON value by wrapping it with double
quote characters and escaping interior quote and other
characters, then returning the result as a
utf8mb4
string. Returns
NULL
if the argument is
NULL
.
This function is typically used to produce a valid JSON string literal for inclusion within a JSON document.
Certain special characters are escaped with backslashes per the escape sequences shown in Table 14.23, “JSON_UNQUOTE() Special Character Escape Sequences”.
mysql>SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
+--------------------+----------------------+ | JSON_QUOTE('null') | JSON_QUOTE('"null"') | +--------------------+----------------------+ | "null" | "\"null\"" | +--------------------+----------------------+ mysql>SELECT JSON_QUOTE('[1, 2, 3]');
+-------------------------+ | JSON_QUOTE('[1, 2, 3]') | +-------------------------+ | "[1, 2, 3]" | +-------------------------+
You can also obtain JSON values by casting values of other types
to the JSON
type using
CAST(
; see
Converting between JSON and non-JSON values, for more
information.
value
AS
JSON)
Two aggregate functions generating JSON values are available.
JSON_ARRAYAGG()
returns a result
set as a single JSON array, and
JSON_OBJECTAGG()
returns a result
set as a single JSON object. For more information, see
Section 14.19, “Aggregate Functions”.