How to Create Views on JSON with DBMS_JSON

JSON columns are flexible, but reports and tools want rows and columns. With a JSON search index that has a data guide, Oracle tracks every field that appears in the documents. DBMS_JSON.CREATE_VIEW_ON_PATH uses that data guide to create a relational view with one column per field, without writing JSON_TABLE by hand.

Code for This Guide

The main examples are in the examples/pkg-json folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create search index index_name on table_name (json_column) for json
  parameters ('dataguide on');

dbms_json.create_view_on_path(viewname => 'VIEW', tablename => 'TABLE',
                              jcolname => 'COLUMN', path => '$');

PATH '$' includes every field of the documents; a deeper path limits the view to one part.

A View over Loyalty Documents

Example:

create search index customers_loyalty_sx on customers (loyalty) for json
  parameters ('dataguide on');

begin
  dbms_json.create_view_on_path(viewname  => 'LOYALTY_V',
                                tablename => 'CUSTOMERS',
                                jcolname  => 'LOYALTY',
                                path      => '$');
end;
/
select column_name, data_type from user_tab_columns
where  table_name = 'LOYALTY_V' and column_name like 'LOYALTY$%' order by column_id;

select customer_id, "LOYALTY$tier" as tier, "LOYALTY$points" as points,
       "LOYALTY$string" as favourite
from   loyalty_v
where  customer_id <= 2;

Output:

Index CUSTOMERS_LOYALTY_SX created.

PL/SQL procedure successfully completed.

COLUMN_NAME            DATA_TYPE
______________________ ____________
LOYALTY$tier           VARCHAR2
LOYALTY$points         NUMBER
LOYALTY$memberId       VARCHAR2
LOYALTY$memberSince    VARCHAR2
LOYALTY$meal           VARCHAR2
LOYALTY$seat           VARCHAR2
LOYALTY$newsletter     VARCHAR2
LOYALTY$string         VARCHAR2

8 rows selected.

   CUSTOMER_ID TIER       POINTS FAVOURITE
______________ _______ _________ ____________
             1 Blue         3296 CDG
             1 Blue         3296 JNB
             1 Blue         3296 NBO
             2 Blue         7823 SYD

The search index builds the data guide for CUSTOMERS.LOYALTY, and LOYALTY_V gets a column for each field, named LOYALTY$ plus the field name, with a data type from the data guide. The favourite airports array is unnested, so customer 1 appears once per favourite. The example drops the view and the index afterward.

Things to Know

• The view is fixed at creation; if new fields appear in documents, create it again.
• Column names include the case of the JSON field names, so quote them in queries.
• The data guide alone, without a view, is available through DBMS_JSON.GET_INDEX_DATAGUIDE and JSON_DATAGUIDE.

Related Guides

• Oracle JSON_DATAGUIDE Function
• Oracle JSON_TABLE Function

Conclusion

DBMS_JSON.CREATE_VIEW_ON_PATH turns the data guide of a JSON search index into a relational view of the documents' fields. Use it to give reporting tools rows and columns over JSON data, and recreate the view when the documents gain new fields.