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:

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;