7 JSON Data Stored in JSON-Relational Duality Views
Columns of JSON
data type stored in tables underlying a
duality view can produce JSON values of any kind (scalar, object, array) in the documents
supported by the view. This stored JSON data can be schemaless or JSON Schema-based (to
enforce particular shapes and types of field values).
Whether to store some of the data underlying a duality view as
JSON
type and, if so, whether to enforce its structure and
typing, are design choices to consider when defining the view.
By storing some JSON data that contributes to the JSON documents supported by (generated by) a duality view, you can choose the granularity and complexity of the building blocks that define the view. Put differently, you can choose the degree of normalization you want for the underlying data. Different choices involve different tradeoffs.
A JSON-relational duality view supports a set of JSON documents based on underlying table data. The JSON documents are automatically generated from this table data as needed.
A document-centric application accesses, updates, and otherwise uses the JSON
documents supported by a duality view as if they were stored in its single,
JSON
-type column — applications see only a column of JSON
documents.
At the same time, a relational/table-centric application can access, update, and otherwise use the same underlying table data directly. Duality: changes to either documents or table data are automatically reflected in the other (and this is the case across multiple documents and tables that share data).
Typically, the table data underlying a duality view is completely normalized, and thus the table columns contain only values of scalar SQL data types.
Complete normalization gives you the most flexibility in terms of combining data from multiple tables to support different kinds of duality view (more generally, in terms of combining some table data with other table data, outside of any use for duality views).
And in an important particular use case, it lets you access the data in existing relational tables from a document-centric application, as JSON documents.
On the other hand, the greater the degree of normalization, the more tables you have, which means more decomposition when inserting JSON data and more joining when querying it. If an application typically accesses complex objects as a whole then greater normalization can thus negatively impact performance.
You can also think of the building-block columns in the tables that underlie a duality view as providing the ingredients, and think of the duality-view definition as providing the recipe, for producing JSON documents of a particular kind (structure and typing).
A cooking recipe need not be "from scratch", using only simple, basic ingredients. On the other hand, nor does a recipe need to be as simple as just adding water to a preassembled/prepackaged "mix". There's a range of possibilities for each ingredient, from basic (an egg) to complex (a cake mix).
For example, if a recipe calls for "salad dressing" as one of its ingredients, that can come ready-made from a bottle or you can create it by combining more-basic ingredients such as olive oil and vinegar. And an ingredient for composing the salad-dressing ingredient might itself be complex (prepackaged), such as mayonnaise, mustard, or a prepared mix of spices.
The same is true for the ingredients used to define/implement a duality view and the JSON documents that it supports.
When a table underlying a duality view is completely normalized as SQL scalar values, the recipe ingredients are as simple and basic as possible (scalar values are atomic: indivisible).
But some (or even all) of the table columns can instead store
JSON
-type data, which can be scalar or complex. Sometimes it makes
sense to include whole (small) JSON documents, stored in the duality view, as part of
the larger, generated documents. This amounts to using some complex ingredients
in your duality-view recipe.
With Oracle Database you can store JSON data (documents) in a column of
JSON
data type, and you can selectively update any parts of those
documents (any fields), or replace whole documents at a time.
This is also true of the data in a stored JSON
-type column
that's used to define part of a duality view. And like any other column in an underlying
table, a JSON
-type column can be shared among different duality
views, and thus be shared in their different resulting (generated) JSON documents.
By default, a JSON document is free-form: its structure and typing are not defined by, or forced to conform to, any given pattern/schema. In this case, applications can easily change the shape and types of the documents as needed.
On the other hand, you can impose typing and structure on the data in a
JSON
-type column, using JSON Schema. JSON Schema gives you a
full spectrum of control:
-
From fields whose values are completely undefined to fields whose values are strictly defined.
-
From scalar JSON values to large, complex JSON objects and arrays.
-
From simple type definitions to combinations of JSON-language types. For example:
-
A value that satisfies
anyOf
,allOf
, oroneOf
a set of JSON schemas -
A value that does
not
satisfy a given JSON schema
-
As an example at one end of the type spectrum, a tiny JSON schema can be
applied to a JSON
-type column to require its data to be of a particular
JSON-language scalar type.
A corresponding Oracle JSON-language scalar type exists for each SQL scalar type that can be used in a duality-view definition. Consequently, JSON-schema typing can be just as fine-grained as SQL typing.
For example, if applied to a JSON
-type column as a check
constraint, this JSON schema allows only values that are JSON strings: {"type":
"string"}
. The effect is similar to that of using a column of
SQL type VARCHAR2
.
This JSON schema allows only values that are JSON dates (an Oracle
JSON-language scalar type): {"extendedType": "date"}
.Foot 1 The
effect is similar to that of using a column of SQL type DATE
.
And this JSON schema allows only values that are JSON strings or numbers:
{"type": [ "string", "number" ]}
. SQL has no type
that corresponds to this.
Note:
Using, in a duality-view definition, a JSON
-type column
that's constrained by a JSON schema to hold only data of a particular JSON scalar
type (date, string, etc.) that corresponds to a SQL scalar type has the same
effect on the JSON documents supported by the view as using a column of the
corresponding SQL scalar type (DATE
, VARCHAR2
,
etc.).
However, code that acts directly on such stored
JSON
-type data won't necessarily recognize and take into
account this correspondence. The SQL type of the data is, after all,
JSON
, not DATE
, VARCHAR2
,
etc. To extract a JSON scalar value as a value of a SQL scalar data type, code needs
to use SQL/JSON function json_value
. See SQL/JSON Function JSON_VALUE in Oracle Database JSON
Developer’s Guide.
Let's summarize some of the tradeoffs between using basic ingredients
(SQL scalar columns) and possibly complex ingredients (JSON
-type
columns) in a table underlying a duality view:
-
Flexibility of combination. For the finest-grain combination, use completely normalized tables, whose columns are all SQL scalars.
-
Flexibility of document type and structure. For maximum flexibility of JSON field values at any given time, and thus also for changes over time (evolution), use
JSON
-type columns with no JSON-schema constraints. -
Granularity of field definition. The finest granularity requires a column for each JSON field, regardless of where the field is located in documents supported by the duality view. (The field value could nevertheless be a JSON object or array, if the column is
JSON
-type.)
If it makes sense for your application to share some complex JSON data among
different kinds of documents, and if you expect to have no need for combining only
parts of that complex data with other documents or, as SQL scalars, with
relational data, then consider using JSON
data type for the columns
underlying that complex data.
In other words, in such a use case consider sharing JSON documents, instead of sharing the scalar values that constitute them. In still other words, consider using more complex ingredients in your duality-view recipe.
Note that the granularity of column data — how complex the data in it can be
— also determines the granularity of updating operations and ETAG-checking (for
optimistic concurrency control). The smallest unit for such operations is an individual
column underlying a duality view; it's impossible to annotate
individual fields inside a JSON
-type column.
Update operations can selectively apply to particular fields
contained in the data of a given JSON
-type column, but control of
which update operations can be used with a given view is defined at the level of
an underlying column or whole table — nothing smaller. So if you need finer grain
updating or ETAG-checking then you need to break out the relevant parts of the
JSON data into their own JSON
-type columns.
- Flex Columns: Duality-View Schema Flexibility and Evolution
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.
Related Topics
See Also:
-
Validating JSON Documents with a JSON Schema for information about using JSON schemas to constrain or validate JSON data
-
json-schema.org for information about JSON Schema
7.1 Flex Columns: Duality-View Schema Flexibility and Evolution
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.
Any tables underlying a duality view can have any number of
JSON
-type columns. At most one JSON column per table can be
designated as a flex column at each position where that table is used in the view
definition. If a given table is used only at one place in a view definition (a typical
case) then only one flex column for the table can be used. If the same table is used in
N different places in a view definition, then up to N different flex
columns for the table can be designated at those
places.
You can designate the same flex column to provide the fields for different places of the same document. Those different places share all of the fields stored in that flex column. Updates to any of the places must concord, by not providing different new fields or different values for the same field.
Note:
The same general behavior holds for a nonflex column: if used to support fields in multiple places of a document then all of those places share the same data. In the nonflex case only the field values must be the same; the field names can be different in different places.
In a given duality-view definition, you can't use the same JSON column as a flex column in one document place and as a nonflex column in another place. An error is raised if you try to do this.
In any table, a JSON column generally provides for flexible data: by default, its typing and structure are not constrained/specified in any way (for example, by a JSON schema).
The particularity of a JSON column that's designated as a flex column for a duality view is this:
-
The column value must be a JSON object or SQL
NULL
.This means that it must be declared as type
JSON (OBJECT)
, not justJSON
. Otherwise, an error is raised when you try to use that column in a duality-view definition.(This restriction doesn't apply to a nonflex
JSON
-type column; its value can be any JSON value: scalar, array, or object.) -
On read, the object stored in a flex column is unnested: its fields are unpacked into the resulting document object.
That is, the stored object is not included as such, as the value of some field in the object produced by the flex column's table. Instead, each of the stored object's fields is included in that document object.
(Any value — object, array, or scalar — in a nonflex
JSON
-type column is just included as is; an object is not unnested.)For example, if the object in a given row of the flex column for table
tab1
has fieldsfoo
andbar
then, in the duality-view document that corresponds to that row, the object produced fromtab1
also contains those fields,foo
andbar
. -
On write, the fields from the document object are packed back into the stored object, and any fields not supported by other columns are automatically added to the flex column. That is, an unrecognized field "overflows" into the object in the JSON flex column.
For example, if a new field
toto
is added to a document object corresponding to a table that has a flex column, then on insertion of the document if fieldtoto
isn't already supported by the table then fieldtoto
is added to the flex-column's object.
Note:
To require a nonflex JSON
-type column to hold
only object values (or SQL NULL
) you can define it using the
modified data type JSON (OBJECT)
, or you can use a
JSON-Schema VALIDATE
check constraint of
{"type":"object"}
. See Validating JSON Data with a JSON Schema in
Oracle Database JSON
Developer’s Guide.
More generally, you can require a nonflex
JSON
-type column to hold only scalar, object, or array JSON values,
or any combination of those. And you can restrict scalar values to be of a specific
type, such as a string or a date. For example, if the column type is JSON
(OBJECT, SCALAR DATE)
then it allows only values that are objects or
dates.
A column designated as flex for a duality view is such (is flex) only for
the view. For the table that it belongs to, it's just an ordinary
JSON
-type column, except that the value in each row must be a
single JSON object or SQL NULL
.
Different duality views can thus define different flex columns (that is, with different names) for the same table, each view's flex column suiting that view's own purposes, providing fields for the documents that only it supports.
Note:
If for some reason you actually want two or more duality views to share a flex column, then just give the flex column the same name when defining each view. This might sometimes be what you want, but be aware of the consequence.
Unlike nonflex columns, which are dedicated to individual fields that are specified explicitly in a view's definition, a flex column holds the data for multiple fields that are unknown to the view definition. A flex column is essentially a free pass for unrecognized incoming fields at certain locations in a document (that's its purpose: provide flexibility).
On write, an unrecognized field is stored in a flex column (of the table relevant to the field's location in the document). If two views with the same underlying table share a flex column there, then incoming fields unrecognized by either view get stored in that column, and on read those fields are exposed in the documents for both views.
Because a flex column's object is unnested on read, adding its fields to those produced by the other columns in the table, and because a JSON column is by default schemaless, changes to flex-column data can change the structure of the resulting document object, as well as the types of some of its fields.
In effect, the typing and structure of a duality view's supported documents can change/evolve at any level, by providing a flex column for the table supporting the JSON object at that level.
You can change the typing and structure of a duality view's documents by modifying flex-column data directly, through the column's table. More importantly, you can do so simply by inserting or updating documents with fields that don't correspond to underlying relational columns. Any such fields are automatically added to the corresponding flex columns. Applications are thus free to create documents with any fields they like, in any objects whose underlying tables have a flex column.
However, be aware that unnesting the object from a flex column can lead to name conflicts between its fields and those derived from the other columns of the same table. Such conflicts cannot arise for JSON columns that don't serve as flex columns.
For this reason, if you don't need to unnest a stored JSON object — if it's sufficient to just include the whole object as the value of a field — then don't designate its column as flex. Use a flex column where you need to be able to add fields to a document object that's otherwise supported by relational columns.
The value of any row of a flex column must be a JSON object or the SQL
value NULL
.
SQL NULL
and an empty object ({}
) behave
the same, except that they typically represent different contributions to the document
ETAG value. (You can annotate a flex column with NOCHECK
to remove its
data from ETAG calculation. You can also use column annotation
[NO]UPDATE
, [NO]CHECK
on a flex column.)
In a duality-view definition you designate a JSON
-type
column as being a flex column for the view by following the column name in the view
definition with keywords AS FLEX
in SQL or with annotation
@flex
in GraphQL.
For example, in this GraphQL definition of duality view dv1
,
column t1_json_col
of table table1
is designated as a
flex column. The fields of its object value are included in the resulting document as
siblings of field1
and field2
. (JSON objects have
undefined field order, so the order in which a table's columns are specified in a
duality-view definition doesn't matter.)
CREATE JSON RELATIONAL DUALITY VIEW dv1 AS
table1 @insert @update @delete
{_id : id_col,
t1_field1 : col_1,
t1_json_col @flex,
t1_field2 : col_2};
When a table underlies multiple duality views, those views can of course use some or all of the same columns from the table. A given column from such a shared table can be designated as flex, or not, for any number of those views.
The fact that a column is used in a duality view as a flex column means that
if any change is made directly to the column value by updating its table then the column
value must still be a JSON object (or SQL NULL
).
It also means that if the same column is used in a table that underlies another duality view, and it's not designated as a flex column for that view, then for that view the JSON fields produced by the column are not unpacked in the resulting documents; in that view the JSON object with those fields is included as such. In other words, designation as a flex column is view-specific.
You can tell whether a given table underlying a duality view has a flex column by
checking BOOLEAN
column HAS_FLEX_COL
in static
dictionary views *_JSON_DUALITY_VIEW_TABS
. You can tell whether a given
column in an underlying table is a flex column by checking BOOLEAN
column IS_FLEX_COL
in static dictionary views
*_JSON_DUALITY_VIEW_TAB_COLS
. See ALL_JSON_DUALITY_VIEW_TABS and ALL_JSON_DUALITY_VIEW_TAB_COLS in Oracle Database
Reference.
The data in both flex and nonflex JSON columns in a table underlying a duality view can be schemaless, and it is so by default.
But you can apply JSON schemas to any JSON
-type columns used
anywhere in a duality-view definition, to remove their flexibility ("lock" them). You
can also impose a JSON schema on the documents generated/supported by a duality
view.
Because the fields of an object in a flex column are unpacked into the resulting document, if you apply a JSON schema to a flex column the effect is similar to having added a separate column for each of that object's fields to the flex column's table using DML.
In effect, by applying a JSON schema you change the logical structure of the data, and thus the structure of the documents supported by the view. You remove schema flexibility, but you don't change the storage structure (tables).
See Also:
Using JSON to Implement Flexfields (video, 24 minutes)
Field Naming Conflicts Produced By Flex Columns
Because fields in a flex column are unpacked into an object that also has fields provided otherwise, field name conflicts can arise. There are multiple ways this can happen, including these:
-
A table underlying a duality view gets redefined, adding a new column. The duality view gets redefined, giving the JSON field that corresponds to the new column the same name as a field already present in the flex column for the same table.
Problem: The field name associated with a nonflex column would be the same as a field in the flex-column data.
-
A flex column is updated directly (that is, not by updating documents supported by the view), adding a field that has the same name as a field that corresponds in the view definition to another column of the same underlying table.
Problem: The field name associated with a nonflex column is also used in the flex-column data.
-
Two duality views,
dv1
anddv2
, share an underlying table, using the same column,jcol
, as flex. Onlydv1
uses nonflex column,foocol
from the table, naming its associated fieldfoo
.Data is inserted into
dv1
, populating columnfoocol
. This can happen by inserting a row into the table or by inserting a document with fieldfoo
intodv1
.A JSON row with field
foo
is added to the flex column, by inserting a document intodv2
.Problem: View
dv2
has no problem. But for viewdv1
field-namefoo
is associated with a nonflex column and is also used in the flex-column data.
It's not feasible for the database to prevent such conflicts from arising, but you can specify the behavior you prefer for handling them when they detected during a read (select, get, JSON generation) operation. (All such conflicts are detected during a read.)
You do this using the following keywords at the end of a flex-column declaration. Note that in all cases that don't raise an error, any field names in conflict are read from nonflex columns — that is, priority is always given to nonflex columns.
GraphQL | SQL | Behavior |
---|---|---|
|
(Keywords |
Any field names in conflict are read from
nonflex columns. Field
This is the default behavior. For example, if for a given document nonflex field
|
|
(Keyword |
Any field names in conflict are read from nonflex columns. The value of each name that has a conflict is changed in its nonflex column to be an array whose elements are the values: one from the nonflex column and one from the flex-column data, in that order. For example, if for a given document nonflex field
|
|
(Keyword |
Any field names in conflict are read from nonflex columns. The same names are ignored from the flex column. |
|
(Keyword |
An error is raised. |
For example, this GraphQL flex declaration defines column
extras
as a flex column, and it specifies that any conflicts
that might arise from its field names are handled by simply ignoring the problematic
fields from the flex column data:
extras: JSON @flex (conflict: IGNORE)
Note:
IGNORE ON CONFLICT
and ARRAY ON
CONFLICT
are incompatible with ETAG-checking. An error is raised if
you try to create a duality view with a flex column that is ETAG-checked and has
either of these on-conflict declarations.
Related Topics
Parent topic: JSON Data Stored in JSON-Relational Duality Views
Footnote Legend
Footnote 1: The field name isextendedType
rather than type
because date is not a standard JSON-language scalar type.