22.5 Creating a View Over JSON Data Using JSON_TABLE
To improve query performance you can create a view over JSON data that you
project to columns using SQL/JSON function json_table. To further improve query
performance you can create a materialized view and place the JSON data in
memory.
Example 22-10 defines a view over JSON data. It uses a
NESTED path clause to project the elements of array
LineItems.
Example 22-11 defines a materialized view that has the same data and structure as Example 22-10.
In general, you cannot update a view directly (whether materialized or not).
But if a materialized view is created using keywords REFRESH and ON
STATEMENT, as in Example 22-11, then the view is updated automatically whenever you update
the base table.
You can use json_table to project any fields as view columns,
and the view creation (materialized or not) can involve joining any tables and any number of
invocations of json_table.
The only differences between Example 22-10 and Example 22-11 are these:
-
The use of keyword
MATERIALIZED. -
The use of
BUILD IMMEDIATE. -
The use of
REFRESH FAST ON STATEMENT WITH PRIMARY KEY.
The use of REFRESH FAST means that the materialized view will
be refreshed incrementally. For this to occur, you must use either WITH PRIMARY
KEY or WITH ROWID (if there is no primary key). Oracle
recommends that you specify a primary key for a table that has a JSON column and that you
use WITH PRIMARY KEY when creating a materialized view based on it. You can
use REFRESH FAST with a multiple-table materialized-join view and (single
or multiple-table) materialized-aggregate views.
You could use ON COMMIT in place of ON
STATEMENT for the view creation. The former synchronizes the view with the base
table only when your table-updating transaction is committed. Until then the table changes
are not reflected in the view. If you use ON STATEMENT then the view is
immediately synchronized after each DML statement. This also means that a view created using
ON STATEMENT reflects any rollbacks that you might perform. (A subsequent
COMMIT statement ends the transaction, preventing a rollback.)
See Also:
Refreshing Materialized Views in Oracle Database Data Warehousing Guide
Example 22-10 Creating a View Over JSON Data
CREATE VIEW j_purchaseorder_detail_view
AS SELECT po.id, jt.*
FROM j_purchaseorder po,
json_table(po.data, '$'
COLUMNS (
po_number NUMBER(10) PATH '$.PONumber',
reference VARCHAR2(30 CHAR) PATH '$.Reference',
requestor VARCHAR2(128 CHAR) PATH '$.Requestor',
userid VARCHAR2(10 CHAR) PATH '$.User',
costcenter VARCHAR2(16) PATH '$.CostCenter',
ship_to_name VARCHAR2(20 CHAR)
PATH '$.ShippingInstructions.name',
ship_to_street VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.street',
ship_to_city VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.city',
ship_to_county VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.county',
ship_to_postcode VARCHAR2(10 CHAR)
PATH '$.ShippingInstructions.Address.postcode',
ship_to_state VARCHAR2(2 CHAR)
PATH '$.ShippingInstructions.Address.state',
ship_to_zip VARCHAR2(8 CHAR)
PATH '$.ShippingInstructions.Address.zipCode',
ship_to_country VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.country',
ship_to_phone VARCHAR2(24 CHAR)
PATH '$.ShippingInstructions.Phone[0].number',
NESTED PATH '$.LineItems[*]'
COLUMNS (
itemno NUMBER(38) PATH '$.ItemNumber',
description VARCHAR2(256 CHAR) PATH '$.Part.Description',
upc_code NUMBER PATH '$.Part.UPCCode',
quantity NUMBER(12,4) PATH '$.Quantity',
unitprice NUMBER(14,2) PATH '$.Part.UnitPrice'))) jt;
Example 22-11 Creating a Materialized View Over JSON Data
CREATE MATERIALIZED VIEW j_purchaseorder_materialized_view
BUILD IMMEDIATE
REFRESH FAST ON STATEMENT WITH PRIMARY KEY
AS SELECT po.id, jt.*
FROM j_purchaseorder po,
json_table(po.data, '$'
COLUMNS (
po_number NUMBER(10) PATH '$.PONumber',
reference VARCHAR2(30 CHAR) PATH '$.Reference',
requestor VARCHAR2(128 CHAR) PATH '$.Requestor',
userid VARCHAR2(10 CHAR) PATH '$.User',
costcenter VARCHAR2(16) PATH '$.CostCenter',
ship_to_name VARCHAR2(20 CHAR)
PATH '$.ShippingInstructions.name',
ship_to_street VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.street',
ship_to_city VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.city',
ship_to_county VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.county',
ship_to_postcode VARCHAR2(10 CHAR)
PATH '$.ShippingInstructions.Address.postcode',
ship_to_state VARCHAR2(2 CHAR)
PATH '$.ShippingInstructions.Address.state',
ship_to_zip VARCHAR2(8 CHAR)
PATH '$.ShippingInstructions.Address.zipCode',
ship_to_country VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.country',
ship_to_phone VARCHAR2(24 CHAR)
PATH '$.ShippingInstructions.Phone[0].number',
NESTED PATH '$.LineItems[*]'
COLUMNS (
itemno NUMBER(38) PATH '$.ItemNumber',
description VARCHAR2(256 CHAR) PATH '$.Part.Description',
upc_code NUMBER PATH '$.Part.UPCCode',
quantity NUMBER(12,4) PATH '$.Quantity',
unitprice NUMBER(14,2) PATH '$.Part.UnitPrice'))) jt;