Create JSON Table
CREATE TABLE json_data
( json_col CLOB,
CONSTRAINT must_be_json CHECK ( json_col IS JSON ) )
Table created.
Insert Row One
INSERT INTO json_data
VALUES ('{ "zebra" : { "name" : "Marty",
"stripes" : ["Black","White"],
"handler" : "Bob" }}')
1 row(s) inserted.
Insert Row Two
INSERT INTO json_data
VALUES ('{ "zebra" : { "name" : "Zigby",
"stripes" : ["White","Black10"],
"handler" : "Handy Marty" }}')
1 row(s) inserted.
COMMIT
Statement processed.
No Text Index Yet
SELECT json_col TC_Is_Marty_There
FROM json_data
WHERE JSON_TEXTCONTAINS(json_col, '$', 'Marty');
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.
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" }} |
---|
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" }} |
---|
JTC Element Name
SELECT json_col TC_Is_zebra_There
FROM json_data
WHERE JSON_TEXTCONTAINS(json_col, '$', 'zebra')
no data found
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" }} |
---|
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" }} |
---|