27.7.1 JSON Duality View Syntax

To create a JSON duality view, use the CREATE JSON RELATIONAL DUALITY VIEW statement. (The RELATIONAL keyword is optional, and is omitted in examples.) This statement is much like CREATE VIEW, but differs in that the SELECT statement used for creating a JSON duality view must be of the form shown here:

SELECT 
    JSON_DUALITY_OBJECT(
        [table_annotations]
        json_duality_key_value_pairs
    )
FROM [database.]table

(table_annotations is optional, and is discussed later in this section.)

The JSON_DUALITY_OBJECT() function returns a mapping between columns of table and the JSON collection defined by json_duality_key_value_pairs. The value used with each key can be one of three types:

• The name of a column in table. This must be the name of the column only, and cannot be an expression.

• A singleton_descendent_json_object which consists of a SELECT with a FROM clause. The SELECT list and FROM clause follow the same rules as those described for the top-level query in CREATE JSON DUALITY VIEW.

• A set of nested_descendent_json_objects selects an expression using JSON_ARRAYAGG(), which in turn contains JSON_DUALITY_OBJECT().

In this example, we create a database table customers and define a JSON duality view jdv_customers over this table. We create the table using the statement shown here:

CREATE TABLE customers (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street VARCHAR(50),
    city VARCHAR(25),
    state CHAR(2),
    postcode CHAR(5),
    phone CHAR(10),
    email VARCHAR(50)
);

The JSON duality view jdv_customers gives us a hierarchical view of some of the data in the table; we can create it using the following SQL statement:

CREATE JSON DUALITY VIEW jdv_customers
  AS
    SELECT JSON_DUALITY_OBJECT( {
                '_id'   : c.id,
                'name'  : { 'fname' : c.first_name,
                            'lname' : c.last_name },
                'email' : c.email,
                'area'  : { 'city'  : c.city,
                            'state' : c.state }
                }
            )
FROM customers c;