Movatterモバイル変換

CREATE [OR REPLACE]    [ALGORITHM = {UNDEFINED | MERGE}]    [DEFINER =user]    [SQL SECURITY {DEFINER | INVOKER}]    JSON [RELATIONAL] DUALITY VIEW     [IF NOT EXISTS] [schema_name.]view_name     ASjson_duality_select_statementjson_duality_select_statement:     SELECTjson_duality_object_expression    FROM [schema_name.]root_table_name [AStable_alias]json_duality_object_expression:     JSON_DUALITY_OBJECT(...)

This statement creates a JSON duality view namedview_name. UsingOR REPLACE causes any existing JSON duality view of that name to be replaced by a new JSON duality view having the same name. SpecifyingIF NOT EXISTS causes view creation to be attempted only if there is no existing JSON duality view with the same name, rather than returning an error.

JSON duality views use the same namespace as SQL views. This means that you cannot create a JSON duality view having the same name as an existing SQL view; it also means that you cannot create an SQL view with the same name as an existing JSON duality view.CREATE OR REPLACE does not work to replace an SQL view with a JSON duality view, or a JSON duality view with an SQL view.

DEFINER andSQL SECURITY work with this statement as they do forCREATE VIEW. ForALGORITHM, usingTEMPTABLE returns an error.

TheRELATIONAL keyword is optional, has no effect, and is omitted from our examples.

schema_name, if used with the view name, must be the name of an existing schema. If the schema name is omitted, the JSON duality view is created in the current schema; if no schema is currently selected and none is specified, the statement is rejected with an error.schema_name andview_name must conform to the rules for MySQL identifiers; seeSection 11.2, “Schema Object Names”, as well asSection 11.2.1, “Identifier Length Limits”, for information about these rules.

TheWITH ... CHECK OPTION clause works withCREATE JSON DUALITY VIEW as it does withCREATE VIEW. See the description of that statement for more information.

json_duality_select_statement selects a JSON object expression (json_duality_object_expression) constructed using columns from the tableroot_table_name in schemaschema_name. Ifschema_name is omitted, MySQL assumes that the table is in the current schema; if no schema is specified and none is currently selected, the statement is rejected with an error. Bothschema_name androot_table_name must follow the usual rules for MySQL identifiers.

json_duality_select_statement must contain one and only oneJSON_DUALITY_OBJECT() expression and oneFROM clause. Set operations (UNION,INTERSECT,EXCEPT) and common table expressions (WITH) are not supported. TheFROM clause must reference a single table.WHERE,JOIN,GROUP BY,ORDER BY,HAVING,WINDOW, andLIMIT clauses are not supported.

json_duality_object_expression is a value returned byJSON_DUALITY_OBJECT(). See the description of that function for information about its arguments.

TheJSON_DUALITY_OBJECT() function returns a JSON duality object for use inCREATE JSON DUALITY VIEW orALTER 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.

Requirements:

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_expressionvalue_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_conditionnested_descendent_json_objects:    SELECT JSON_ARRAYAGG(json_duality_object_expression [json_constructor_null_clause])     FROMchild_table_name [AStable_alias]     WHEREjson_duality_join_conditionjson_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_namejson_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 inkey_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 byJSON_DUALITY_OBJECT() (singleton descendant); an object returned byJSON_ARRAYAGG() (nested descendant).

column_name must reference a valid column in the table that is being projected (root_table_name orcurrent_table_name). The samecolumn_name cannot be used more than once in a single invocation ofJSON_DUALITY_OBJECT(). Functions and operators cannot be used withcolumn_name. Columns of typesJSON,VECTOR, andGEOMETRY (including all derivatives such asPOINT,LINESTRING, andPOLYGON) are not supported, nor are generated columns. The column having the key_id in the root table forjson_duality_key_value_pairs must be a primary key of that table.

Thesingleton_descendent_json_object consists of aSELECT statement with aFROM clause. TheSELECT list andFROM clause follow the same rules as those described for the top-level query in aCREATE JSON DUALITY VIEW statement.

nested_descendent_json_objects selects a single expression (json_duality_object_expression) usingJSON_ARRAYAGG(), which must contain a non-emptyJSON_DUALITY_OBJECT(). The select list andFROM clause follow the same rules as those described forsingleton_descendent_json_object. The optionaljson_constructor_null_clause specifies the behavior of this function whenjson_duality_object_expression evaluates to null. It takes either of the valuesABSENT ON NULL orNULL ON NULL (the default).NULL ON NULL returns the JSONnull value;ABSENT ON NULL causes the value to be omitted from the output JSON array.

singleton_descendent_json_object andnested_descendent_json_objects also support aWHERE 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 thisWHERE clause. Multiple conditions usingAND orOR operators are also not supported.

JSON_DUALITY_OBJECT() takes an optionaltable_annotations_expression. This expression consists of a comma-separated list that must include the annotation valuesINSERT,UPDATE, andDELETE, in any order. No annotation value may be listed more than once. The function returns a mapping between columns oftable and the JSON collection defined byjson_duality_key_value_pairs. The value used with each key can be one of three types:

If the table is projected multiple times, the set of columns projected must be consistent across all instances of the table projection.

SeeSection 27.7, “JSON Duality Views”, for more information and examples.