JSON_TEXTCONTAINS vs JSON_EXISTS

View All Scripts Login to Run Script

Script Name JSON_TEXTCONTAINS vs JSON_EXISTS
Description A comparison of JSON_TEXTCONTAINS and JSON_EXISTS for searching JSON data in the Oracle Database.
Area SQL General / JSON
Contributor Darryl Hurley
Created Thursday April 13, 2017

Statement 1

Create a table with a JSON column.

Create JSON Table

CREATE TABLE json_data   
( json_col CLOB,   
    CONSTRAINT must_be_json CHECK ( json_col IS JSON ) )

Table created.

Statement 2

Insert one JSON row.

Insert Row One

INSERT INTO json_data   
VALUES ('{ "zebra" : { "name" : "Marty",   
                       "stripes" : ["Black","White"],  
                       "handler" : "Bob" }}')

1 row(s) inserted.

Statement 3

Insert another JSON row. <<>> notice the use of Marty as the zebra's handler - it matches the name used in the first row.

Insert Row Two

INSERT INTO json_data   
VALUES ('{ "zebra" : { "name" : "Zigby",   
                       "stripes" : ["White","Black10"],  
                       "handler" : "Handy Marty" }}')

1 row(s) inserted.

Statement 4
```
COMMIT
```
```
Statement processed.
```

Statement 5

Raises "ORA-40467" because a text index does not exist.

No Text Index Yet

SELECT json_col TC_Is_Marty_There  
  FROM json_data   
 WHERE JSON_TEXTCONTAINS(json_col, '$', 'Marty');

Statement 6

Create a text index on the JSON column. This index is a requirement for the JSON_TEXTCONTAINS function.

Create JSON Text Index

CREATE INDEX json_index   
  ON json_data(json_col)   
  INDEXTYPE IS CTXSYS.CONTEXT   
  PARAMETERS ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)')

Index created.

Statement 7

Use JSON_TEXTCONTAINS to search the JSON document for Marty. Finds 2 rows.

JTC Whole Document

SELECT json_col TC_Is_Marty_There  
  FROM json_data   
 WHERE JSON_TEXTCONTAINS(json_col, '$', 'Marty')


TC_IS_MARTY_THERE
{ "zebra" : { "name" : "Marty",   
                       "stripes" : ["Black","White"],  
                       "handler" : "Bob" }}
{ "zebra" : { "name" : "Zigby",   
                       "stripes" : ["White","Black10"],  
                       "handler" : "Handy Marty" }}

2 rows selected.

TC_IS_MARTY_THERE
{ "zebra" : { "name" : "Marty", "stripes" : ["Black","White"], "handler" : "Bob" }}
{ "zebra" : { "name" : "Zigby", "stripes" : ["White","Black10"], "handler" : "Handy Marty" }}

Statement 8

Search for Marty in the zebra.name element of the JSON document.

JTC Specific Element

SELECT json_col TC_Is_Marty_A_Zebra_There  
  FROM json_data   
 WHERE JSON_TEXTCONTAINS(json_col, '$.zebra.name', 'Marty')


TC_IS_MARTY_A_ZEBRA_THERE
{ "zebra" : { "name" : "Marty",   
                       "stripes" : ["Black","White"],  
                       "handler" : "Bob" }}

Statement 9
Search the whole JSON document for zebra. This wont find anything because zebra is a key not a value.
JTC Element Name
```
SELECT json_col TC_Is_zebra_There  
  FROM json_data   
 WHERE JSON_TEXTCONTAINS(json_col, '$', 'zebra')
```
```
no data found
```

TC_IS_MARTY_A_ZEBRA_THERE
{ "zebra" : { "name" : "Marty", "stripes" : ["Black","White"], "handler" : "Bob" }}

Statement 10

Use JSON_EXISTS to search the JSON document for a key named zebra.

JE Whole Document

SELECT json_col JX_Is_zebra_There  
  FROM json_data   
 WHERE JSON_EXISTS(json_col, '$.zebra')


JX_IS_ZEBRA_THERE
{ "zebra" : { "name" : "Marty",   
                       "stripes" : ["Black","White"],  
                       "handler" : "Bob" }}
{ "zebra" : { "name" : "Zigby",   
                       "stripes" : ["White","Black10"],  
                       "handler" : "Handy Marty" }}

2 rows selected.

JX_IS_ZEBRA_THERE
{ "zebra" : { "name" : "Marty", "stripes" : ["Black","White"], "handler" : "Bob" }}
{ "zebra" : { "name" : "Zigby", "stripes" : ["White","Black10"], "handler" : "Handy Marty" }}

Statement 11

Use JSON_EXISTS to search for a key of zebra with a value of Marty.

JE JSON Key

SELECT json_col JX_Is_Marty_A_Zebra_There  
  FROM json_data   
 WHERE JSON_EXISTS(json_col, '$.zebra')  
   AND JSON_EXISTS(json_col,'$?(@.zebra.name == "Marty")')


JX_IS_MARTY_A_ZEBRA_THERE
{ "zebra" : { "name" : "Marty",   
                       "stripes" : ["Black","White"],  
                       "handler" : "Bob" }}

JX_IS_MARTY_A_ZEBRA_THERE
{ "zebra" : { "name" : "Marty", "stripes" : ["Black","White"], "handler" : "Bob" }}

Additional Information

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