Example 24-9 Adding Virtual Columns That Project JSON Fields Using a Data Guide Obtained With JSON_DATAGUIDE

This example uses a hierarchical data guide obtained using function json_dataguide with JSON column data.

The added virtual columns are all of the columns in table j_purchaseorder except for ID, DATE_LOADED, and PODOCUMENT.

• Parameter resolveNameConflicts is TRUE, to ensure that any name conflicts get resolved. (Optional, for clarity; this is anyway the default value.)

• Parameter colNamePrefix is 'DATA$', to use that as the default prefix for column names.

• Parameter mixedCaseColumns is TRUE, to make column names be case-sensitive, that is, to distinguish uppercase and lowercase letters.

DECLARE
  dg CLOB;
BEGIN
  SELECT json_dataguide(data, DBMS_JSON.FORMAT_HIERARCHICAL) INTO dg
    FROM j_purchaseorder;
  DBMS_JSON.add_virtual_columns('J_PURCHASEORDER',
                                'DATA',
                                dg,
                                resolveNameConflicts=>TRUE,
                                colNamePrefix=>'DATA$',
                                mixedCaseColumns=>TRUE);
END;
/

DESCRIBE j_purchaseorder;
 Name                             Null?    Type
 -------------------------------- -------- ---------------------------
 ID                               NOT NULL RAW(16)
 DATE_LOADED                               TIMESTAMP(6) WITH TIME ZONE
 DATA                                      CLOB
 DATA$User                                 VARCHAR2(8)
 DATA$PONumber                             NUMBER
 DATA$Reference                            VARCHAR2(16)
 DATA$Requestor                            VARCHAR2(16)
 DATA$CostCenter                           VARCHAR2(4)
 DATA$AllowPartialShipment                 VARCHAR2(4)
 DATA$name                                 VARCHAR2(16)
 DATA$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)

Example 24-10 Adding Virtual Columns, Hidden and Visible

In this example only two fields are projected as virtual columns: PO_Number and PO_Reference. The data guide is defined locally as a literal string. Data-guide field o:hidden is used here to hide the virtual column for PO_Reference. (For PO_Number the o:hidden: false entry is not needed, as false is the default value.)

DECLARE
  dg CLOB;
BEGIN
  dg := '{"type" : "object",
          "properties" :
            {"PO_Number"    : {"type" : "number",
                               "o:length" : 4,
                               "o:preferred_column_name" : "PO_Number",
                               "o:hidden" : false},
             "PO_Reference" : {"type" : "string",
                               "o:length" : 16,
                               "o:preferred_column_name" : "PO_Reference",
                               "o:hidden" : true}}}';
  DBMS_JSON.add_virtual_columns('J_PURCHASEORDER', 'DATA', dg);
END;
/

DESCRIBE j_purchaseorder;
 Name        Null?    Type
 ----------- -------- ---------------------------
 ID          NOT NULL RAW(16)
 DATE_LOADED          TIMESTAMP(6) WITH TIME ZONE
 DATA                 CLOB
 PO_Number            NUMBER

SELECT column_name FROM user_tab_columns
  WHERE table_name = 'J_PURCHASEORDER' ORDER BY 1;
COLUMN_NAME
-----------
DATE_LOADED
ID
DATA
PO_Number
PO_Reference

5 rows selected.

Example 24-11 Projecting All Scalar Fields Not Under an Array as Virtual Columns

The added virtual columns are all of the columns in table j_purchaseorder except for ID, DATE_LOADED, and PODOCUMENT. This is because no FREQUENCY argument is passed to add_virtual_columns, so all scalar fields (that are not under an array) are projected.

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

EXEC DBMS_JSON.add_virtual_columns('J_PURCHASEORDER', 'DATA');

DESCRIBE j_purchaseorder;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL RAW(16)
 DATE_LOADED                                        TIMESTAMP(6) WITH TIME ZONE
 DATA                                               CLOB
 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)

Example 24-12 Projecting Scalar Fields With a Minimum Frequency as Virtual Columns

All scalar fields that occur in all (100%) of the documents are projected as virtual columns. The result is the same as that for Example 24-11, except that fields AllowPartialShipment and Phone are not projected, because they do not occur in 100% of the documents.

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

EXEC DBMS_JSON.add_virtual_columns('J_PURCHASEORDER', 'DATA', 100);

DESCRIBE j_purchaseorder;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL RAW(16)
 DATE_LOADED                                        TIMESTAMP(6) WITH TIME ZONE
 DATA                                               CLOB
 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)

Example 24-13 Projecting Scalar Fields With a Minimum Frequency as Hidden Virtual Columns

The result is the same as that for Example 24-12, except that all of the added virtual columns are hidden. (The query of view USER_TAB_COLUMNS shows that the virtual columns were in fact added.)

EXEC DBMS_JSON.add_virtual_columns('J_PURCHASEORDER', 'DATA', 100, TRUE);

DESCRIBE j_purchaseorder;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL RAW(16)
 DATE_LOADED                                        TIMESTAMP(6) WITH TIME ZONE
 DATA                                               CLOB

SELECT column_name FROM user_tab_columns
  WHERE table_name = 'J_PURCHASEORDER'
  ORDER BY 1;

COLUMN_NAME
-----------
DATE_LOADED
ID
PONumber
DATA
DATA$CostCenter
DATA$Reference
DATA$Requestor
DATA$SpecialInstructions
DATA$User
DATA$city
DATA$country
DATA$name
DATA$state
DATA$street
DATA$zipCode

Example 24-14 Dropping Virtual Columns Projected From JSON Fields

EXEC DBMS_JSON.drop_virtual_columns('J_PURCHASEORDER', 'DATA');