Example 24-5 Creating a View Using a Hierarchical Data Guide Obtained With JSON_DATAGUIDE

This example creates a view that projects all of the fields present in the hierarchical data guide that is obtained by invoking SQL function json_dataguide on data of table j_purchaseorder. The second and third arguments passed to json_dataguide are used, respectively, to specify that the data guide is to be hierarchical and pretty-printed.

The view column names come from the values of field o:preferred_column_name of the data guide that you pass to DBMS_JSON.create_view. By default, the view columns are thus named the same as the projected fields.

Because the columns must be uniquely named in the view, you must ensure that the field names themselves are unique. Optional parameter RESOLVENAMECONFLICTS does this by default (value true), but if you specify it as false then the names are not guaranteed to be unique. In this case (false), an alternative is to edit the data guide returned by json_dataguide to make the value of o:preferred_column_name unique. If parameter RESOLVENAMECONFLICTS is false, then an error is raised by DBMS_JSON.create_view if the names for the columns are not unique.

Although this example does not do so, you can provide a column-name prefix using DBMS_JSON.create_view with parameter colNamePrefix. For example, to get the same effect as that provided when you use a data guide obtained from the information in a data guide-enabled JSON search index, you could specify parameter colNamePrefix as 'DATA$', that is, the JSON column name, DATA followed by $. See Example 24-8.

DECLARE
  dg CLOB;
  BEGIN
    SELECT json_dataguide(data,
                          FORMAT DBMS_JSON.FORMAT_HIERARCHICAL,
                          DBMS_JSON.PRETTY)
      INTO dg
      FROM j_purchaseorder
      WHERE extract(YEAR FROM date_loaded) = 2014;
    DBMS_JSON.create_view('MYVIEW',
                          'J_PURCHASEORDER',
                          'DATA',
                          dg);
  END;
/

DESCRIBE myview
 Name                 Null?    Type
 -------------------- -------- ---------------------------
 DATE_LOADED                   TIMESTAMP(6) WITH TIME ZONE
 ID                   NOT NULL RAW(16)
 User                          VARCHAR2(8)
 PONumber                      NUMBER
 UPCCode                       NUMBER
 UnitPrice                     NUMBER
 Description                   VARCHAR2(32)
 Quantity                      NUMBER
 ItemNumber                    NUMBER
 Reference                     VARCHAR2(16)
 Requestor                     VARCHAR2(16)
 CostCenter                    VARCHAR2(4)
 AllowPartialShipment          VARCHAR2(4)
 name                          VARCHAR2(16)
 Phone                         VARCHAR2(16)
 type                          VARCHAR2(8)
 number                        VARCHAR2(16)
 city                          VARCHAR2(32)
 state                         VARCHAR2(2)
 street                        VARCHAR2(32)
 country                       VARCHAR2(32)
 zipCode                       NUMBER
 Special Instructions          VARCHAR2(8)

Example 24-6 Creating a View That Projects All Scalar Fields

All scalar fields are represented in the view, because the specified path is $.

(Columns whose names are italic in the describe command output are those that have been renamed using PL/SQL procedure DBMS_JSON.rename_column. Underlined rows are missing from Example 24-8.)

EXEC DBMS_JSON.create_view_on_path('VIEW2',
                                   'J_PURCHASEORDER',
                                   'DATA',
                                   '$');

DESCRIBE view2;
 Name                              Null?    Type
 --------------------------------  -------- ------------------
 ID                                NOT NULL RAW(16)
 DATE_LOADED                       TIMESTAMP(6) WITH TIME ZONE
 DATA$User                         VARCHAR2(8)
 PONumber                          NUMBER
 DATA$Reference                    VARCHAR2(16)
 DATA$Requestor                    VARCHAR2(16)
 DATA$CostCenter                   VARCHAR2(4)
 DATA$AllowPartialShipment         VARCHAR2(4)
 DATA$name                         VARCHAR2(16)
 Phone                             VARCHAR2(16)
 DATA$city                         VARCHAR2(32)
 DATA$state                        VARCHAR2(2)
 DATA$street                       VARCHAR2(32)
 DATA$country                      VARCHAR2(32)
 DATA$zipCode                      NUMBER
 DATA$SpecialInstructions          VARCHAR2(8)
 DATA$UPCCode                      NUMBER
 DATA$UnitPrice                    NUMBER
 PartDescription                   VARCHAR2(32)
 DATA$Quantity                     NUMBER
 ItemNumber                        NUMBER
 PhoneType                         VARCHAR2(8)
 PhoneNumber                       VARCHAR2(16)

Example 24-7 Creating a View That Projects Scalar Fields Targeted By a Path Expression

Fields Itemnumber, PhoneType, and PhoneNumber are not represented in the view. The only fields that are projected are those scalar fields that are not under an array plus those that are present (at any level) in the data that is targeted by $.LineItems.Part (that is, the scalar fields whose paths start with $.LineItems.Part). (Columns whose names are italic in the describe command output are those that have been renamed using PL/SQL procedure DBMS_JSON.rename_column.)

SQL> EXEC DBMS_JSON.create_view_on_path('VIEW4',
                                        'J_PURCHASEORDER',
                                        'DATA',
                                        '$.LineItems.Part');


SQL> DESCRIBE view4;
 Name                             Null?    Type
 -------------------------------- -------- ------------------
 ID                               NOT NULL RAW(16)
 DATE_LOADED                      TIMESTAMP(6) WITH TIME ZONE
 DATA$User                        VARCHAR2(8)
 PONumber                         NUMBER
 DATA$Reference                   VARCHAR2(16)
 DATA$Requestor                   VARCHAR2(16)
 DATA$CostCenter                  VARCHAR2(4)
 DATA$AllowPartialShipment        VARCHAR2(4)
 DATA$name                        VARCHAR2(16)
 Phone                            VARCHAR2(16)
 DATA$city                        VARCHAR2(32)
 DATA$state                       VARCHAR2(2)
 DATA$street                      VARCHAR2(32)
 DATA$country                     VARCHAR2(32)
 DATA$zipCode                     NUMBER
 DATA$SpecialInstructions         VARCHAR2(8)
 DATA$UPCCode                     NUMBER
 DATA$UnitPrice                   NUMBER
 PartDescription                  VARCHAR2(32)

Example 24-8 Creating a View That Projects Scalar Fields Having a Given Frequency

All scalar fields that occur in all (100%) of the documents are represented in the view. Field AllowPartialShipment does not occur in all of the documents, so there is no column DATA$AllowPartialShipment in the view. Similarly for fields Phone, PhoneType, and PhoneNumber.

(Columns whose names are italic in the describe command output are those that have been renamed using PL/SQL procedure DBMS_JSON.rename_column.)

SQL> EXEC DBMS_JSON.create_view_on_path('VIEW3',
                                        'J_PURCHASEORDER',
                                        'DATA',
                                        '$',
                                        100);


SQL> DESCRIBE view3;
 Name                             Null?    Type
 -------------------------------- -------- ------------------
 ID                               NOT NULL RAW(16)
 DATE_LOADED                      TIMESTAMP(6) WITH TIME ZONE
 DATA$User                        VARCHAR2(8)
 PONumber                         NUMBER
 DATA$Reference                   VARCHAR2(16)
 DATA$Requestor                   VARCHAR2(16)
 DATA$CostCenter                  VARCHAR2(4)
 DATA$name                        VARCHAR2(16)
 DATA$city                        VARCHAR2(32)
 DATA$state                       VARCHAR2(2)
 DATA$street                      VARCHAR2(32)
 DATA$country                     VARCHAR2(32)
 DATA$zipCode                     NUMBER
 DATA$SpecialInstructions         VARCHAR2(8)
 DATA$UPCCode                     NUMBER
 DATA$UnitPrice                   NUMBER
 PartDescription                  VARCHAR2(32)
 DATA$Quantity                    NUMBER
 ItemNumber                       NUMBER