Error Clause And JSON Indexes

This post concludes my series on JSON indexes. It shows how the ERROR clause impacts index usage and query results. The code below relies on tables and indexes created in previous posts in this thread.

Now lets get to the code…

--
-- This query uses the JSON_4 index to quickly find the
-- desired value
--
SQL> SELECT JSON_VALUE(jd.json_col,'$.col'
 2            RETURNING VARCHAR2(4) )
 3     FROM json_data jd
 4    WHERE JSON_VALUE(jd.json_col,'$.col'
 5            RETURNING VARCHAR2(4) ) = 'val1';
JSON
----
val1

--
-- This query does NOT use the index. It follows this
-- algorithm using a FULL TABLE SCAN
--   1) Try to find the rows satisfying the query but...
--   2) ...also look at every other row and...
--   3) ...if you find one longer that 4 characters fail
--
SQL> SELECT JSON_VALUE(jd.json_col,'$.col'
 2            RETURNING VARCHAR2(4) )
 3     FROM json_data jd
 4    WHERE JSON_VALUE(jd.json_col,'$.col'
 5            RETURNING VARCHAR2(4) ERROR ON ERROR ) = 'val1';
ERROR:
ORA-40478: output value too large (maximum: 4)
no rows selected

--
-- This query also does not use the index. It follows this
-- algorithm using a FULL TABLE SCAN
--   1) Try to find the rows satisfying the query but...
--   2) ...also look at every other row but...
--   3) ...if you find one then set it to WTF but don't
--   4) ...return it
--
SQL> SELECT JSON_VALUE(jd.json_col,'$.col' 
 2            RETURNING VARCHAR2(4) )
 3     FROM json_data jd
 4    WHERE JSON_VALUE(jd.json_col,'$.col'
 5            RETURNING VARCHAR2(4) DEFAULT 'WTF' ON ERROR ) = 'val1';
JSON
----
val1

This behavior is very different from indexes on relational tables. I’m still trying to see how it might benefit application design or performance. I’ll post what I find in the future.

Thanks for reading!