JSON Index 1

Welcome to the introductory post of a new series on JSON indexes in the Oracle database. This post shows how JSON indexes are created and used in SQL queries. As always – lets get right to the code!

SQL> CREATE TABLE json_data
 2   ( json_col CLOB,
 3       CONSTRAINT json_only CHECK ( json_col IS JSON ) );
Table created.

-- Put 100 simple JSON values into the demo table
SQL> DECLARE
 2     v_val VARCHAR2(100);
 3   BEGIN
 4     FOR counter IN 1..100 LOOP
 5       v_val := '{"col":"val' || counter || '"}';
 6       INSERT INTO json_data
 7       VALUES(v_val);
 8     END LOOP;
 9   END;
 10 /
PL/SQL procedure successfully completed.

-- Create an index using dot notation to denote the
-- JSON key to index. In this case its the "col" key in the
-- JSON data stored in the "json_col" column of the
-- "json_data" table.
SQL> CREATE INDEX json_col_ix ON json_data jd
 2   ( jd.json_col.col );
Index created.

-- The index is implemented via a virtual column added
-- to the json_data_table. In this case the virtual column
-- is named SYS_NC00002$
SQL> SELECT index_name,
 2          column_name
 3     FROM user_ind_columns
 4    WHERE index_name = 'JSON_COL_IX';

INDEX_NAME      COLUMN_NAME
--------------- ---------------
JSON_COL_IX     SYS_NC00002$
1 row selected.

-- Here are the details of the virtual column. Note the
-- dot notation index was translated into a call
-- to the JSON_QUERY function.
SQL> SELECT data_type,
 2          data_length,
 3          data_default
 4     FROM user_tab_cols
 5    WHERE column_name = ( SELECT column_name
 6                            FROM user_ind_columns
 7                           WHERE index_name = 'JSON_COL_IX' );
TYPE     LNGTH DATA_DEFAULT
-------- ----- -------------------------
VARCHAR2 4000 JSON_QUERY("JSON_COL" FORMAT JSON ,
              '$.col' RETURNING VARCHAR2(4000) ASIS
              WITHOUT ARRAY WRAPPER NULL ON ERROR)
1 row selected.

-- Now to query a value from the table using dot notation
-- in the where clause
SQL> SELECT *
 2     FROM json_data jd
 3    WHERE jd.json_col.col = 'val33';

JSON_COL
---------------
{"col":"val33"}
1 row selected.

-- Now use explain plan to verify if the index was used
SQL> EXPLAIN PLAN FOR
 2   SELECT *
 3     FROM json_data jd
 4    WHERE jd.json_col.col = 'val33';
Explained.

-- Viewing the plan shows the index was used!
SQL> SELECT *
 2     FROM TABLE(DBMS_XPLAN.DISPLAY)
 3    WHERE TRIM(plan_table_output) IS NOT NULL;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 716575500
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0  | SELECT STATEMENT | | 1 | 4004 | 2 (0)| 00:00:01 |
| 1  | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA | 1 | 4004 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | JSON_COL_IX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access(JSON_QUERY("JSON_COL" FORMAT JSON ,
     '$.col' RETURNING VARCHAR2(4000) ASIS
     WITHOUT ARRAY WRAPPER NULL ON ERROR)='val33')
Note
-----
 - dynamic statistics used: dynamic sampling (level=2)

Its important to note that dot notation was translated into JSON_QUERY twice:

  • When the index was created
  • When the WHERE clause was specified

This ensures the comparison will be “apples to apples” to allow the index to be used. The next few posts in this series will explore just how easy it is to wind up comparing “oranges to apples” rendering indexes unused.

Thanks for reading!

JSON Check Constraints…or not

I’ve been researching the Oracle SQL JSON functions and found some weird¬†interesting behaviors. My New Year’s resolution is to look for the goodness in things so I’ll present them as features¬†and tie them loosely (very, very loosely) to existing Oracle features. Here’s the first entry.

The following code imitates a check constraint by requiring an element named MustBeThere.

-- Create a table with a JSON column
SQL> CREATE TABLE json_data
 2 ( json_col CLOB,
 3 CONSTRAINT json_or_nothing_pal
 4 CHECK ( json_col IS JSON ) );

Table created.

-- create a JSON_VALUE index on an element named MustBeThere
-- and specify ERROR ON ERROR to fail if the element is
-- not found
SQL> CREATE INDEX json_data_ix ON json_data jd
 2 ( JSON_VALUE(jd.json_col,'$.MustBeThere' ERROR ON ERROR ));

Index created.

-- Try to insert a record without the MustBeThere element
-- The index ERROR ON ERROR clause will cause it to fail
SQL> INSERT INTO json_data
 2 VALUES(
 3 '{"akey" : "avalue" }');
INSERT INTO json_data
 *
ERROR at line 1:
ORA-40462: JSON_VALUE evaluated to no value


-- Insert a record with the element
SQL> INSERT INTO json_data
 2 VALUES(
 3 '{"MustBeThere" : "OKHereItIs"}' );

1 row created.

I don’t recommend actually doing this because because the error message is not very helpful and will cause confusion. I just wanted to make everyone aware of the situation in case they encounter it.

Happy 2017 and thanks for reading!

JSON Unique Keys

When it comes to JSON unique key values there are two schools of thought – those that want keys within objects (squiggly brackets) to be unique and those that don’t care. This can present some interesting outcomes when parsing the data as shown in this post using the SQL JSON_QUERY function.

Note that array pointers start at zero

-- allow non-unique keys is the default when
-- adding a JSON column to a table
SQL> CREATE TABLE json_table
 2   ( json_col CLOB
 3      CONSTRAINT just_json
 4      CHECK ( json_col IS JSON ) );
Table created.

-- insert a row with duplicate keys
SQL> INSERT INTO json_table
 2   VALUES(
 3   '{ "field1" : "value1",
 4      "field1" : "also value1"
 5    }');
1 row created.

-- specifying field1 directly returns the first value
SQL> SELECT JSON_QUERY(jt.json_col,'$.field1' WITH WRAPPER ) AS field1
 2     FROM json_table jt;
FIELD1
------------------------------
["value1"]

-- specifying the first array pointer (zero) returns both
-- values
SQL> SELECT JSON_QUERY(jt.json_col,'$.field1[0]' WITH WRAPPER ) AS field1
 2     FROM json_table jt;
FIELD1
------------------------------
["value1","also value1"]

-- specifying all array values be returned does as well...
SQL> SELECT JSON_QUERY(jt.json_col,'$.field1[*]' WITH WRAPPER ) AS field1
 2     FROM json_table jt;
FIELD1
------------------------------
["value1","also value1"]

-- specifying the second array pointer (1) returns an error
-- meaning its an array of length 1 with 2 values...?
SQL> SELECT JSON_QUERY(jt.json_col,'$.field1[1]' WITH WRAPPER ERROR ON ERROR ) AS field1
 2     FROM json_table jt;
 FROM json_table jt
 *
ERROR at line 2:
ORA-40462: JSON_VALUE evaluated to no value

This JSON Expedition is getting more and more interesting.

Thanks for reading!