In this example, you can see how to create a ubiquitous search index, define and add a JSON duality view to it, and then query against the index using the CONTAINS, JSON_TEXTCONTAINS, and JSON_EXISTS operators.

1. Connect to Oracle Database as a local user.
   1. Log in to SQL*Plus as the SYS user, connecting as SYSDBA:

      conn sys/password as sysdba

      CREATE TABLESPACE tbs1
      DATAFILE 'tbs5.dbf' SIZE 20G AUTOEXTEND ON
      EXTENT MANAGEMENT LOCAL
      SEGMENT SPACE MANAGEMENT AUTO;

      SET ECHO ON
      SET FEEDBACK 1
      SET NUMWIDTH 10
      SET LINESIZE 80
      SET TRIMSPOOL ON
      SET TAB OFF
      SET PAGESIZE 10000
      SET LONG 10000

   2. Create a local user (docuser) and grant necessary privileges:

      DROP USER docuser cascade;

      GRANT DB_DEVELOPER_ROLE, DEFAULT TABLESPACE tbs1 quota unlimited on tbs1
      TO docuser IDENTIFIED BY password;

   3. Connect as the local user (docuser):

      CONN docuser/password

2. Prepare tables to implement your JSON duality view.
   1. Create the employees and departments tables:

      CREATE TABLE employees
              ( employee_id    NUMBER(6) primary key, 
                first_name     varchar2(4000), 
                last_name      varchar2(4000), 
                department_id  NUMBER(4)
              );

      CREATE TABLE departments
              ( department_id    NUMBER(5) primary key, 
                department_name  VARCHAR2(30), 
                manager_id       NUMBER(6)
              );

   2. Alter the employees table to include a foreign key constraint (emp_dept_fkey) to it.

      Here, you specify that the department_id column in the employees table uses a foreign key to the department_id column in the departments table:

      ALTER TABLE employees
        ADD (
             CONSTRAINT  emp_dept_fkey
             FOREIGN KEY (department_id)
             REFERENCES departments
      );

   3. Populate the employees and departments tables:

      INSERT INTO departments VALUES(10, 'Administration', 100);
      INSERT INTO employees VALUES(100, 'Robert', 'Smith', 10);
      INSERT INTO employees VALUES(101, 'James', 'Martin' ,10);
      INSERT INTO employees VALUES(102, 'John', 'Doe', 10);
      
      commit;

3. Create a DBMS_SEARCH index named MY_SEARCH_INDEX.

   exec dbms_search.create_index('MY_SEARCH_INDEX',NULL,'JSON');

   Note that you can omit tablespace and datatype (defaults to JSON), as follows:

   exec dbms_search.create_index('MY_SEARCH_INDEX');

   Run the following command to determine the structure of your index, which is created in the DOCUSER schema:

   DESC MY_SEARCH_INDEX;

    Name             Null?      Type
    ---------------- -------- ----------------------------
    METADATA         NOT NULL   JSON
    DATA                        JSON
    OWNER                       VARCHAR2(128)
    SOURCE                      VARCHAR2(128)
    KEY                         VARCHAR2(1024)

4. Define a JSON duality view over the tables you created, and then add that view to MY_SEARCH_INDEX.
   1. Create an employee-centric JSON duality view named MY_EMP_VIEW.

      CREATE or replace JSON relational duality VIEW MY_EMP_VIEW
            AS
          select JSON {
          'EMPLOYEE_ID' is emp.EMPLOYEE_ID,
          'FIRST_NAME'  is emp.FIRST_NAME,
          'LAST_NAME' is emp.last_name,
          'department_info' is
          (
            select JSON
           {
           'DEPARTMENT_ID' is dept.department_id ,
           'departmentname' is dept.department_name WITH(UPDATE)
           }
           from departments dept WITH(UPDATE,CHECK ETAG)
           where dept.department_id = emp.department_id
         )
      }
      from employees emp WITH(INSERT,UPDATE,DELETE);

   2. Add the duality view (MY_EMP_VIEW) to MY_SEARCH_INDEX as a data source:

      exec dbms_search.add_source('MY_SEARCH_INDEX','MY_EMP_VIEW');

5. Examine what is indexed in MY_SEARCH_INDEX:
   1. Query the METADATA column to review the source information from where the tables are extracted.

      select JSON_SERIALIZE(METADATA FORMAT JSON) META from MY_SEARCH_INDEX
      order by owner,source,key;

      The index table's METADATA column stores a JSON representation of the following form for each indexed row:

      META
      --------------------------------------------------------------------------------
      {"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":100}}
      {"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":101}}
      {"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":102}}

   2. Examine the metadata values as indexed in the DATA column.

      select data from MY_EMP_VIEW
      order by 1;

      DATA
      --------------------------------------------------------------------------------
      {"_metadata":{"etag":"77AACDD6860BE5D14FCEB2A8633336D7","asof":"0000000000000000
      {"_metadata":{"etag":"4B233BD8A51C3B905A357F8446FBABDA","asof":"0000000000000000
      {"_metadata":{"etag":"5669EAC90AB697DA6C13D2045D6C6638","asof":"0000000000000000

   3. View a virtual indexed document to examine the actual fields that are indexed.

      select JSON_SERIALIZE(
        DBMS_SEARCH.GET_DOCUMENT('MY_SEARCH_INDEX', METADATA) FORMAT JSON) DOC 
      from MY_SEARCH_INDEX
      order by owner,source,key;

      The output returns a JSON document with combined metadata values, as indexed in MY_SEARCH_INDEX:

      DOC
      -----------------------------------------------------------------------------------------
      {"DOCUSER":{"MY_EMP_VIEW":{"DATA":{"_metadata":{"etag":"77AACDD6860BE5D14FCEB2A8633336D7",
      "asof":"0000000000000000"},"EMPLOYEE_ID":100,"FIRST_NAME":"Robert","LAST_NAME":"Smith",
      "department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}}}}
      
      {"DOCUSER":{"MY_EMP_VIEW":{"DATA":{"_metadata":{"etag":"4B233BD8A51C3B905A357F8446FBABDA",
      "asof":"0000000000000000"},"EMPLOYEE_ID":101,"FIRST_NAME":"James","LAST_NAME":"Martin",
      "department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}}}}
      
      {"DOCUSER":{"MY_EMP_VIEW":{"DATA":{"_metadata":{"etag":"5669EAC90AB697DA6C13D2045D6C6638",
      "asof":"0000000000000000"},"EMPLOYEE_ID":102,"FIRST_NAME":"John","LAST_NAME":"Doe",
      "department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}}}}

6. Query your duality view using the JSON_EXISTS operator.

   • This statement generates an execution plan that retrieves the EMPLOYEE_ID from the MY_SEARCH_INDEX table, filtering the JSON data for entries where the FIRST_NAME is Robert and the LAST_NAME is Smith:

     explain plan for 
     select 
     t.metadata.KEY."EMPLOYEE_ID".number() as employee_id 
     from MY_SEARCH_INDEX t
     where 
     json_exists(data,
       '$.DOCUSER.MY_EMP_VIEW.DATA?(@.FIRST_NAME == "Robert" && @.LAST_NAME == "Smith")');
     
     SELECT PLAN_TABLE_OUTPUT FROM 
       TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC PREDICATE PROJECTION'));

     The output appears as:

     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     Plan hash value: 1710711056
     
     --------------------------------------------------------------
     | Id  | Operation                          | Name            |
     --------------------------------------------------------------
     |   0 | SELECT STATEMENT                   |                 |
     |   1 |  PARTITION LIST ALL                |                 |
     |*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| MY_SEARCH_INDEX |
     |*  3 |    DOMAIN INDEX                    | MY_SEARCH_INDEX |
     --------------------------------------------------------------
     
     Predicate Information (identified by operation id):
     ---------------------------------------------------
     
        2 - filter(JSON_EXISTS2("DBMS_SEARCH"."GET_DOCUMENT"('"DOCUSER"."MY_SEARCH_INDEX"',
                   "T"."METADATA" /*+ LOB_BY_VALUE */ ) FORMAT OSON,
                   '$.DOCUSER.MY_EMP_VIEW.DATA?(@.FIRST_NAME == "Robert" && @.LAST_NAME ==
                   "Smith")' /* json_path_str  $.DOCUSER.MY_EMP_VIEW.DATA?((@.FIRST_NAME.string() 
                   == "Robert") && (@.LAST_NAME.string() == "Smith"))  */  FALSE ON ERROR TYPE(LAX) )=1)
     
        3 - access("CTXSYS"."CONTAINS"("T"."DATA" /*+ LOB_BY_VALUE */
                   ,'(sdata(FVCH_DFE32BED91ED02414AB59BAEC23126D1_FIRST_NAME  = "Robert" )
                   and sdata(FVCH_9A8FA0A86CCA96ADF45C533C7C92EFF7_LAST_NAME  = "Smith"
                   ))')>0)
     
     Column Projection Information (identified by operation id):
     -----------------------------------------------------------
     
        1 - "T"."METADATA" /*+ LOB_BY_VALUE */ [JSON,8200]
        2 - "T"."METADATA" /*+ LOB_BY_VALUE */ [JSON,8200]
        3 - "T".ROWID[ROWID,10]
     
     31 rows selected.

   • This statement retrieves the primary key defined for MY_EMP_VIEW by searching the EMPLOYEE_ID field from the MY_SEARCH_INDEX table, where the JSON data in the data column contains FIRST_NAME as Robert and LAST_NAME as Smith.

     select 
     t.metadata.KEY."EMPLOYEE_ID".number() as employee_id 
     from MY_SEARCH_INDEX t
     where 
     json_exists(data,
       '$.DOCUSER.MY_EMP_VIEW.DATA?(@.FIRST_NAME == "Robert" && @.LAST_NAME == "Smith")');

   The output returns a row with EMPLOYEE_ID as 100:

    EMPLOYEE_ID
   -----------
    100

7. Update some fields in the duality view directly, and then query the index again to analyze the changes.
   1. Add a new name for an employee with Employee ID as 100.

      update my_emp_view 
      set data = 
        '{"EMPLOYEE_ID":100,"FIRST_NAME":"new_name",
          "LAST_NAME":"new_lastname",
          "department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}'
        where json_value(data,'$.EMPLOYEE_ID') = 100;
      
      commit;

   2. View all contents extracted from the original base tables querying the DATA column of your index.

      select data from my_emp_view order by 1;

      The DATA column creates a JSON representation of the following form for each indexed row of MY_EMP_VIEW that is added as a data source to this index:

      DATA
      --------------------------------------------------------------------------------------
      {"_metadata":{"etag":"B7CAD96918892950C1FBA12E58AC198E","asof":"0000000000000000"},
      "EMPLOYEE_ID":100,"FIRST_NAME":"new_name","LAST_NAME":"new_lastname","department_info":
      {"DEPARTMENT_ID":10,"departmentname":"Administration"}}
      
      {"_metadata":{"etag":"4B233BD8A51C3B905A357F8446FBABDA","asof":"0000000000000000"},
      "EMPLOYEE_ID":101,"FIRST_NAME":"James","LAST_NAME":"Martin","department_info":
      {"DEPARTMENT_ID":10,"departmentname":"Administration"}}
      
      {"_metadata":{"etag":"5669EAC90AB697DA6C13D2045D6C6638","asof":"0000000000000000"},
      "EMPLOYEE_ID":102,"FIRST_NAME":"John","LAST_NAME":"Doe","department_info":
      {"DEPARTMENT_ID":10,"departmentname":"Administration"}}
      
      {"_metadata":{"etag":"3F8A0577A0E8F8AAF38B088D1CD3EAE6","asof":"0000000000000000"},
      "EMPLOYEE_ID":103,"FIRST_NAME":"new_name2","LAST_NAME":"new_lastname2","department_info":
      {"DEPARTMENT_ID":10,"departmentname":"Administration"}}
      
      
      4 rows selected.

   3. Query the employee for whom you updated the new name:

      select 
      t.metadata.KEY."EMPLOYEE_ID".number() as employee_id 
      from MY_SEARCH_INDEX t
      where json_exists(data,
        '$.DOCUSER.MY_EMP_VIEW.DATA?(@.FIRST_NAME == "new_name" && @.LAST_NAME == "new_lastname")');

      The output returns a row with EMPLOYEE_ID as 100:

       EMPLOYEE_ID
      -----------
       100
      
      1 row selected.

   4. Add a new employee to the Administration department.

      insert into my_emp_view values('
        {"EMPLOYEE_ID":103,"FIRST_NAME":"new_name2",
         "LAST_NAME":"new_lastname2",
         "department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}');

   5. Add a new department named HR.

      INSERT INTO departments VALUES
        ( 20, 'HR', 103);

   6. Add a new employee to the HR department.

      insert into  my_emp_view values('
        {"EMPLOYEE_ID":104,"FIRST_NAME":"new_name3",
         "LAST_NAME":"new_lastname3",
         "department_info":{"DEPARTMENT_ID":20,"departmentname":"HR"}}');
      
      commit;

   7. Query the DATA column of your index again to compare the updated indexed data.

      select data from my_emp_view 
        order by 1; 

      DATA
      --------------------------------------------------------------------------------
      {"_metadata":{"etag":"B7CAD96918892950C1FBA12E58AC198E","asof":"0000000000000000
      "},"EMPLOYEE_ID":100,"FIRST_NAME":"new_name","LAST_NAME":"new_lastname",
      "department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}
      
      {"_metadata":{"etag":"4B233BD8A51C3B905A357F8446FBABDA","asof":"0000000000000000
      "},"EMPLOYEE_ID":101,"FIRST_NAME":"James","LAST_NAME":"Martin",
      "department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}
      
      {"_metadata":{"etag":"5669EAC90AB697DA6C13D2045D6C6638","asof":"0000000000000000
      "},"EMPLOYEE_ID":102,"FIRST_NAME":"John","LAST_NAME":"Doe",
      "department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}
      
      {"_metadata":{"etag":"3F8A0577A0E8F8AAF38B088D1CD3EAE6","asof":"0000000000000000
      "},"EMPLOYEE_ID":103,"FIRST_NAME":"new_name2","LAST_NAME":"new_lastname2",
      "department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}
      
      
      4 rows selected.

      View the METADATA column of your updated index.

      select JSON_SERIALIZE(METADATA FORMAT JSON) META from MY_SEARCH_INDEX 
        order by owner,source,key;

      The METADATA column stores a JSON representation of the following form for each indexed row:

      META
      --------------------------------------------------------------------------------
      {"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":100}}
      {"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":101}}
      {"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":102}}
      {"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":103}}
      {"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":104}}

      Compare the virtual document that returns a JSON document with combined metadata values, as indexed for each row.

      select JSON_SERIALIZE(
        DBMS_SEARCH.GET_DOCUMENT('MY_SEARCH_INDEX', METADATA) FORMAT JSON) DOC
      from MY_SEARCH_INDEX
      order by owner,source,key;

8. Run queries against your index using the JSON_TEXT_CONTAINS operator.
   1. Search for all recently updated employees whose names contain "new name":

      select t.metadata.KEY."EMPLOYEE_ID".number() as employee_id  
      from MY_SEARCH_INDEX t
      where json_textcontains(data,'$.DOCUSER.MY_EMP_VIEW.DATA.FIRST_NAME','new name%')
      order by employee_id;

      The output returns three rows with EMPLOYEE_IDs as 100, 103, and 104:

       EMPLOYEE_ID
      -----------
        100
        103
        104

   2. Search for an employee from the HR department:

      select t.metadata.KEY."EMPLOYEE_ID".number() as employee_id 
      from MY_SEARCH_INDEX t
      where json_exists(data,'$.DOCUSER.MY_EMP_VIEW.DATA?(@.department_info.departmentname == "HR")');

      The output returns a row with EMPLOYEE_ID as 104:

       EMPLOYEE_ID
      -----------
        104

9. Run against your index across the schema, using the CONTAINS operator.

   select JSON_SERIALIZE(           
     DBMS_SEARCH.GET_DOCUMENT('MY_SEARCH_INDEX', METADATA) FORMAT JSON) DOC 
   from MY_SEARCH_INDEX
   where contains(data,'Robert or HR')>0
   order by owner,source,key;

   The query retrieves a virtual indexed document with metadata values from MY_SEARCH_INDEX, where the DATA column contains keywords Robert and HR:

   DOC
   --------------------------------------------------------------------------------------------------
   {"DOCUSER":{"MY_EMP_VIEW":{"DATA":{"_metadata":{"etag":"7CB51D7BF53FD85174B3A1FC72EEECDB",
   "asof":"0000000000000000"},"EMPLOYEE_ID":104,"FIRST_NAME":"new_name3","LAST_NAME":"new_lastname3",
   "department_info":{"DEPARTMENT_ID":20,"departmentname":"HR"}}}}}