CREATE [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY {DEFINER | INVOKER}]
    JSON [RELATIONAL] DUALITY VIEW 
    [IF NOT EXISTS] [schema_name.]view_name 
    AS json_duality_select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

json_duality_select_statement: 
    SELECT json_duality_object_expression
    FROM [schema_name.]root_table_name [AS table_alias]

json_duality_object_expression: 
    JSON_DUALITY_OBJECT(...)

This statement creates a JSON relational duality view, often referred to simply as a JSON duality view or JDV, named view_name. Using OR REPLACE causes any existing JSON duality view of that name to be replaced by a new JSON duality view having the same name. Specifying IF 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 and SQL views share the same namespace. 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.

ALGORITHM, DEFINER, and SQL SECURITY work with this statement as they do for CREATE VIEW.

The RELATIONAL keyword is supported by MySQL for compatibility with other database systems. It 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 database. If the schema name is omitted, the JSON duality view is created in the current database; if no database is currently selected and none is specified, the statement is rejected with an error. schema_name and view_name must conform to the rules for MySQL identifiers; see Section 11.2, “Schema Object Names”, as well as Section 11.2.1, “Identifier Length Limits”, for information about these rules.

The WITH ... CHECK OPTION clause works with CREATE JSON DUALITY VIEW as it does with CREATE 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 table root_table_name in database schema_name. If schema_name is omitted, MySQL assumes that the table is in the current database; if no database is specified and none is currently selected, the statement is rejected with an error. Both schema_name and root_table_name must follow the usual rules for MySQL identifiers.

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

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

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

This statement was added in MySQL 9.4.0.