JSON Search Index on Autonomous Database

 Just a quick test to validate JSON Search Index on ADB.   My environment:

• ADB Shared 19c

 Below are the commands to create a simple table, insert a row, create the search index and validate the execution plan.

 

create table departments_json (
  department_id   integer not null primary key,
  department_data blob not null
);

alter table departments_json
  add constraint dept_data_json
  check ( department_data is json );
 
insert into departments_json
  values ( 110, utl_raw.cast_to_raw ( '{
  "department": "Accounting",
  "employees": [
    {
      "name": "Higgins, Shelley",
      "job": "Accounting Manager",
      "hireDate": "2002-06-07T00:00:00"
    },
    {
      "name": "Gietz, William",
      "job": "Public Accountant",
      "hireDate": "2002-06-07T00:00:00"
    }
  ]
}' ));

create search index dept_json_i on departments_json ( department_data ) for json;
 
select * from   departments_json d where  json_textcontains ( department_data, '$', 'Public' );

explain plan for select * from   departments_json d where  json_textcontains ( department_data, '$', 'Public' );

SELECT * FROM table(dbms_xplan.display); 

 

Looking at the plan we can see the search index was used.

 

Example came from this helpful blog article.