CREATE JSON RELATIONAL DUALITY VIEW
Purpose
JSON-relational duality views expose data in relational tables as JSON documents. The documents are materialized on demand, not stored as such. Duality views give your data a conceptual and an operational duality as it is organized both relationally and hierarchically. You can base different duality views on data stored in one or more of the same tables, providing different JSON hierarchies over the same, shared data. This means that applications can access (create, query, modify) the same data as a collection of JSON documents or as a set of related tables and columns, and both approaches can be employed at the same time.
A flex column in a table underlying a JSON-relational duality view lets you add and redefine fields of the document object produced by that table. This provides a certain kind of schema flexibility to a duality view, and to the documents it supports. For more information on flex columns in a table underlying a JSON-relational duality view see JSON Data Stored in JSON-Relational Duality Views of the JSON-Relational Duality Developer's Guide
You define a duality view against a set of tables related by primary key (PK), foreign key (FK) or unique key constraints (UK). The following rules apply:
-
The constraints must be declared in the database but need not be enforced (can be
RELYDISABLEconstraints). -
The relationships type can be 1-to-1, 1-to-N and N-to-M (using a mapping table with two FKs). The N-to-M relationship can be thought of as the combination of 1-to-N and 1-to-1 relationship
-
Columns of two or more tables with 1-to-1 or N-to-1 relationships can be merged into the same JSON object via
UNNEST. Otherwise a nested JSON object is created. -
Tables with a 1-to-N relationship create a nested JSON array.
-
A duality view has only one column of type
JSON. -
Each row in the duality view is one JSON object, which is typically a hierarchy of nested objects and arrays.
-
Each application object is built from values originating from one or multiple rows from the underlying tables of that view. Typically, each table contributes to one (nested) JSON object.
Note:
The SQL data types allowed for a column in a table underlying a duality view are BINARY_DOUBLE, BINARY_FLOAT, BLOB, BOOLEAN, CHAR, CLOB, DATE, JSON, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, NCHAR, NCLOB, NUMBER, NVARCHAR2, VARCHAR2, RAW, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and VECTOR. An error is raised if you specify any other column data type.
Syntax
create_json_relational_duality_view::=
object_gen_clause::=
key_value_clause::=
flex_clause::=
column_tags_clause::=
duality_view_subquery::=
table_tags_clause::=
graphql_query_for_DV::=
root_query_field::=
root_query_field_name::=
directives::=
directive::=
arguments::=
argument::=
name_start::=
name_continue::=
upper_letter::=
lower_letter::=
selection_set::=
selection_list::=
selection::=
query_field_name::=
query_scalar_field_name::=
query_object_field_name::=
fragment_spread::=
fragment_name
quoted_name::=
lower_case_name::=
name_continue_lower::=
field_name::=
scalar_field_name::=
object_field_name::=
object_field_name_lower::=
schema_name_lower::=
simple_object_type_name_lower::=
Semantics
The JSON realtional duality view (DV) has only one column of data type JSON. The column contains the JSON object which is a representation of a application object. The column name is always DATA.
The duality view is read-only by default. Read-only means that the following annotations are in effect: NOINSERT, NODELETE, NOUPDATE.
OR REPLACE
Specify OR REPLACE to re-create the view if it already exists. You can use this clause to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it.
IF NOT EXISTS
Specifying IF NOT EXISTS has the following effects:
-
If the view does not exist, a new view is created at the end of the statement.
-
If the view exists, this is the view you have at the end of the statement. A new one is not created because the older one is detected.
You can have only one of OR REPLACE or IF NOT EXISTS in a statement at a time. Using both in the same statement results in the following error:
ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement.
Using IF EXISTS with CREATE results in ORA-11543: Incorrect IF NOT EXISTS clause for CREATE statement
table_tags_clause
You can mark the view as updatable using the following keyword inside a WITH clause:
-
WITH INSERT -
WITH UPDATE -
WITH DELETE
You can combine keywords together by separting them with a comma, for example: WITH INSERT, UPDATE
column_tags_clause
You can mark individual columns with WITH UPDATE or WITH NOUPDATE. This supercedes table-level annotation.
Column Properties for Updatability
If the FROM clause is marked with such keywords, then this sets the default for all columns of the table in the FROM clause. You can change the default setting on an individual column. If a the FROM clause is specified as WITH (INSERT, UPDATE, DELETE) and a column overrides this default with NOUPDATE, then updates are not allowed.
Column Properties for ETAGs
Individual columns as well as a FROM clause can be specified to take part in the CHECK ETAG calculation or not. An ETAG is a hash value for all the columns' values in one JSON object and is used to detect changes. A column without ETAG can be changed without this change impacting other operations. By default all columns participate in ETAG calculation. Using NOCHECK ETAG a column can be excluded from ETAG calculation.
graphql_query_for DV
graphql_query_for_DV is a special kind of shorthand query operation definition in GraphQL.
-
The
root_query_fieldis the single top-level selection field of this shorthand query.For brevity,
graphql_query_for_dvomits the pair of curly brackets of the top-levelselection_setof a general shorthand query operation. selection_setsyntax augments the selection set defined in the GraphQL specification with the option of optional square brackets around the selection list.-
selectioninselection_listcan be onlyfieldorfragment_spread. -
fielddirectives: conform to the GraphQL specification. Only supported custom directives are allowed. @skip and @include are NOT supported. -
argumentconforms to the GraphQL specification. -
root_query_field_namecorresponds to the root table. -
namehas the same syntax as the GraphQL specification. -
quoted_name: The field names in a GraphQL query for DV allow quoted and un-quoted names. As a convention, un-quoted field names are in lower case only.any_charis any character allowed in a quoted identifier in SQL . -
scalar_field_namecorresponds to a column name of a table. -
object_field_namecorresponds to a related table name. In addition you can use quoted names, and fully qualified table names with dot-concatenation.
Examples
See Also:
Introduction To Car-Racing Duality Views Example of the JSON-Relational Duality Developer's Guide.
Example 1: Create a Duality View of Orders
The following example is a view of an orders view object ORDERS_OV with the following information:
-
Order information such as order status from the
Orderstable -
A
CustomerInfosingleton descendant consisting of customer details from theCustomertable -
An
OrderItemsarray descendant consisting of a list of order items from theOrderItemstable. -
Each order item, in turn, consists of
ItemInfoandShipmentInfosingletons from theProductsandShipmenttables respectively.
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW ORDERS_OV AS
SELECT JSON { 'OrderId' : ord.order_id,
'OrderTime' : ord.order_datetime,
'OrderStatus' : ord.order_status,
'CustomerInfo' :
(SELECT JSON{'CustomerId' : cust.customer_id,
'CustomerName' : cust.full_name,
'CustomerEmail' : cust.email_address }
FROM CUSTOMERS cust
WHERE cust.customer_id = ord.customer_id),
'OrderItems' : (SELECT JSON_ARRAYAGG(
JSON { 'OrderItemId' : oi.line_item_id,
'Quantity' : oi.quantity,
'ProductInfo' : <subquery from product>
'ShipmentInfo' : <subquery from shipments>)
})
FROM ORDER_ITEMS oi
WHERE ord.order_id = oi.order_id)
}
FROM ORDERS ord;Example 2: Create an Updatable View
To make the view updatable, one has to add INSERT or UPDATE or DELETE or any combination of these to either the FROM clause or individual column. The following allows to update the order, only read the customer and insert and update (not delete) order items.
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW ORDERS_OV AS
SELECT JSON { 'OrderId' : ord.order_id,
'OrderTime'. : ord.order_datetime,
'OrderStatus' : ord.order_status,
'CustomerInfo' :
(SELECT JSON{'CustomerId' : cust.customer_id,
'CustomerName' : cust.full_name,
'CustomerEmail' : cust.email_address WITH NOCHECK}
FROM CUSTOMERS c WITH CHECK
WHERE cust.customer_id = ord.customer_id),
'OrderItems' : (SELECT JSON_ARRAYAGG(
JSON { 'OrderItemId' : oi.line_item_id,
'Quantity' : oi.quantity,
'ProductInfo' : <subquery from product>
'ShipmentInfo' : <subquery from shipments>)
})
FROM ORDER_ITEMS oi WITH INSERT, UPDATE
WHERE ord.order_id = oi.order_id)
}
FROM ORDERS ord WITH INSERT, UPDATE, DELETE;