JSON indexing with functional indexes and JSON search index

View All Scripts Login to Run Script

Script Name JSON indexing with functional indexes and JSON search index
Description This script describes how you can index JSON documents with functional (B-Tree) indexes
Area SQL General / JSON
Contributor Beda Hammerschmidt (Oracle)
Created Tuesday December 11, 2018

Statement 1
```
drop table PLAN_TABLE
```

Statement 2

This is required to show EXPLAIN PLANs later is the script

create table PLAN_TABLE ( 
        statement_id       varchar2(30), 
        plan_id            number, 
        timestamp          date, 
        remarks            varchar2(4000), 
        operation          varchar2(30), 
        options            varchar2(255), 
        object_node        varchar2(128), 
        object_owner       varchar2(30), 
        object_name        varchar2(30), 
        object_alias       varchar2(65), 
        object_instance    numeric, 
        object_type        varchar2(30), 
        optimizer          varchar2(255), 
        search_columns     number, 
        id                 numeric, 
        parent_id          numeric, 
        depth              numeric, 
        position           numeric, 
        cost               numeric, 
        cardinality        numeric, 
        bytes              numeric, 
        other_tag          varchar2(255), 
        partition_start    varchar2(255), 
        partition_stop     varchar2(255), 
        partition_id       numeric, 
        other              long, 
        distribution       varchar2(30), 
        cpu_cost           numeric, 
        io_cost            numeric, 
        temp_space         numeric, 
        access_predicates  varchar2(4000), 
        filter_predicates  varchar2(4000), 
        projection         varchar2(4000), 
        time               numeric, 
        qblock_name        varchar2(30), 
        other_xml          clob 
)

Statement 3
```
drop table t
```
Statement 4
creating a table to store json data. Please note a JSON check constraint is added.
```
create table t (jcol varchar(32767) check (jcol is json))
```
Statement 5
```
insert into t values ('{"a":1, "b":2}')
```

Statement 6

insert into t values ('{"a":2, "b":[3,4]}')

Statement 7
```
insert into t values ('{"a":3, "b":5}')
```
Statement 8
```
commit
```
Statement 9
Select all 'a' values. They are all singletons.
```
select t.jcol, t.jcol.a from t t
```
Statement 10
The 'b' values returns singletons and an array (a collection of values).
```
select t.jcol, t.jcol.b from t t
```
Statement 11
The simplified syntax (dot notation) returns every value as a VARCHAR2(4000) value. HEre we have numeric values and we want to index them as number. The RETURNING clause in JSON_VALUE allows us to specify the sql data type of the value.
```
select JSON_VALUE(jcol, '$.a' RETURNING NUMBER) from T
```

Statement 12

Use of JSON_VALUE in WHERE clause.

select jcol from t where JSON_VALUE(jcol, '$.a' RETURNING NUMBER) > 2

Statement 13
```
delete from plan_table
```

Statement 14

Let's look at the plan of this query: select jcol from t where JSON_VALUE(jcol, '$.a' RETURNING NUMBER) > 2

EXPLAIN PLAN   
    SET STATEMENT_ID = 'jsonPlan'  
    FOR  
    select jcol from t where JSON_VALUE(jcol, '$.a' RETURNING NUMBER) > 2

Statement 15

SELECT LPAD('............................',2*(LEVEL-1))||operation operation, options,   
object_name, position   
    FROM plan_table   
    START WITH id = 0 AND statement_id = 'jsonPlan'  
    CONNECT BY PRIOR id = parent_id AND statement_id = 'jsonPlan'

Statement 16
Create an index on the 'a' values as numbers; Treating the values as numbers inside the index enables correct numeric range lookups.
```
create index idx1 on t (JSON_VALUE(jcol, '$.a' RETURNING NUMBER))
```
Statement 17
```
delete from plan_table
```

Statement 18

another look at the plan - we should see that the index is being used.

EXPLAIN PLAN   
    SET STATEMENT_ID = 'jsonPlan'  
    FOR  
    select jcol from t where JSON_VALUE(jcol, '$.a' RETURNING NUMBER) > 2

Statement 19

SELECT LPAD('............................',2*(LEVEL-1))||operation operation, options,   
object_name, position   
    FROM plan_table   
    START WITH id = 0 AND statement_id = 'jsonPlan'  
    CONNECT BY PRIOR id = parent_id AND statement_id = 'jsonPlan'

Statement 20
Another way to express a filter is a 'path predicate'. Here the filter is part of the JSON path language. The comparison is done as part of the path evaluations. Because the JSON data model allows arrays of values a path predicate is matched against each item in the array. An example could be a person having more than one address (home, work, etc). The array of addresses would contain multiple zip/postal codes. If we're looking for one zip/postal code we would need to compare all values of one JSON document.
```
select jcol from t where JSON_EXISTS(jcol, '$?(@.a > 2)' )
```
Statement 21
```
delete from plan_table
```
Statement 22
The index is NOT used? Why is that? This is because JSON_EXISTS with a path predicate is expected to support multiple values whereas the JSON_VALUE statement we used to create the index can at most return one value. (The B-Tree representing the index only one value per row - not multiple).
```
EXPLAIN PLAN   
    SET STATEMENT_ID = 'jsonPlan'  
    FOR  
    select jcol from t where JSON_EXISTS(jcol, '$?(@.a > 2)' )
```

Statement 23

SELECT LPAD('............................',2*(LEVEL-1))||operation operation, options,   
object_name, position   
    FROM plan_table   
    START WITH id = 0 AND statement_id = 'jsonPlan'  
    CONNECT BY PRIOR id = parent_id AND statement_id = 'jsonPlan'

Statement 24
```
drop index idx1
```
Statement 25
We re-create the index in a way that JSON_EXISTS queries with path predicates can use the index. We do this by specifying the an ON ERROR/EMPTY clause. ON EMPTY defines what to do if a value is missing (e.g. a person has no address). NULL ON EMPTY specifies that NULL is returned in this case and consequently a NULL value is indexed for such JSON data. ERROR ON ERROR specifies that any error (e.g. multiple values selected by the path expression) is to be raised. If an error is encountered the index creation would abort. If the index creation succeeds then we know that there were not array values in the indexed data.
```
create index idx1 on t (JSON_VALUE(jcol, '$.a' RETURNING NUMBER   ERROR ON ERROR  NULL ON EMPTY))
```

Statement 26

select jcol from t where JSON_EXISTS(jcol, '$?(@.a > 2)' )

Statement 27
```
delete from plan_table
```

Statement 28

Now we should see that the index is being used.

EXPLAIN PLAN   
    SET STATEMENT_ID = 'jsonPlan'  
    FOR  
    select jcol from t where JSON_EXISTS(jcol, '$?(@.a > 2)' )

Statement 29

SELECT LPAD('............................',2*(LEVEL-1))||operation operation, options,   
object_name, position   
    FROM plan_table   
    START WITH id = 0 AND statement_id = 'jsonPlan'  
    CONNECT BY PRIOR id = parent_id AND statement_id = 'jsonPlan'

Statement 30
Note: The existence of an ERROR ON ERROR index prevents the insertion of any data that has multiple values for an indexed path. This prevents the insertion of data that cannot be indexed. But it also limits schema changes (see JSON Search Index) as a possible solution).
```
insert into t values('{a:[8,9]}');
```
Statement 31
Now lets look at the array case. Here one path selects more than one value. The values do not have to be directly inside the array. The same holds true if the have an array of objects where the objects hold the scalar values.
```
select t.jcol, t.jcol.b from t t
```
Statement 32
```
drop index idx1
```

Statement 33

The index creation fails because we have more than one value for at least one row.

create index idx1 on t (JSON_VALUE(jcol, '$.b' RETURNING NUMBER   ERROR ON ERROR  NULL ON EMPTY))

Statement 34
We can override the ON ERROR clause by providing a default value to be returned. Because this value is scalar it is possible to index it. This method may be a good option in some cases. I do not recommend it as a way to deal with multiple values because (as we will see) it may lead to odd results if we scan the index for a value.
```
create index idx1 on t (JSON_VALUE(jcol, '$.b' RETURNING NUMBER   DEFAULT '0' ON ERROR  NULL ON EMPTY))
```
Statement 35
```
delete from plan_table
```

Statement 36

This query should now use the index.

EXPLAIN PLAN   
    SET STATEMENT_ID = 'jsonPlan'  
    FOR  
    select jcol from t where JSON_VALUE(jcol, '$.b' RETURNING NUMBER   DEFAULT '-1' ON ERROR  NULL ON EMPTY) > 2

Statement 37

SELECT LPAD('............................',2*(LEVEL-1))||operation operation, options,   
object_name, position   
    FROM plan_table   
    START WITH id = 0 AND statement_id = 'jsonPlan'  
    CONNECT BY PRIOR id = parent_id AND statement_id = 'jsonPlan'

Statement 38
But this returns the mentioned 'odd' result. The index lookup for the value 0 returns a row. But this row does not contain the 0 value. Instead 0 masked the error.
```
select jcol from t where JSON_VALUE(jcol, '$.b' RETURNING NUMBER   DEFAULT '0' ON ERROR  NULL ON EMPTY) = 0
```
Statement 39
If we want to index multiple (all) values we can chose the JSON Search index. It is based on posting lists which capture every value in the JSON data in a path aware manner.
```
create search index j_search_idx on t (jcol) for json
```
Statement 40
We drop the functional index because the optimizer prefers it over the JSON Search Index in the execution plan.
```
drop index idx1
```
Statement 41
```
delete from plan_table
```

Statement 42

This query should now use the JSON Search Index.

EXPLAIN PLAN   
    SET STATEMENT_ID = 'jsonPlan'  
    FOR  
    select jcol from t where JSON_VALUE(jcol, '$.b' RETURNING NUMBER) > 2

Statement 43

SELECT LPAD('............................',2*(LEVEL-1))||operation operation, options,   
object_name, position   
    FROM plan_table   
    START WITH id = 0 AND statement_id = 'jsonPlan'  
    CONNECT BY PRIOR id = parent_id AND statement_id = 'jsonPlan'

Statement 44
```
delete from plan_table
```

Statement 45

JSON_EXISTS also uses the JSON Search Index.

EXPLAIN PLAN   
    SET STATEMENT_ID = 'jsonPlan'  
    FOR  
    select jcol from t where JSON_EXISTS(jcol, '$?(@.b > 2)' )

Statement 46

SELECT LPAD('............................',2*(LEVEL-1))||operation operation, options,   
object_name, position   
    FROM plan_table   
    START WITH id = 0 AND statement_id = 'jsonPlan'  
    CONNECT BY PRIOR id = parent_id AND statement_id = 'jsonPlan'

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database