SQL/JSON Conditions
SQL/JSON conditions allow you to test JavaScript Object Notation (JSON) data as follows:
-
IS JSON Condition lets you test whether an expression is syntactically correct JSON data
-
JSON_EXISTS Condition lets you test whether a specified JSON value exists in JSON data
-
JSON_TEXTCONTAINS Condition lets you test whether a specified character string exists in JSON property values.
JSON_condition::=
IS JSON Condition
Use this SQL/JSON condition to test whether an expression is syntactically correct, or well-formed, JSON data.
-
If you specify
IS
JSON
, then this condition returnsTRUE
if the expression is well-formed JSON data andFALSE
if the expression is not well-formed JSON data. -
If you specify
IS
NOT
JSON
, then this condition returnsTRUE
if the expression is not well-formed JSON data andFALSE
if the expression is well-formed JSON data.
is_JSON_condition::=
-
Use
expr
to specify the JSON data to be evaluated. Specify an expression that evaluates to a text literal. Ifexpr
is a column, then the column must be of data typeVARCHAR2
,CLOB
, orBLOB
. Ifexpr
evaluates to null or a text literal of length zero, then this condition returnsUNKNOWN
. -
You must specify
FORMAT
JSON
ifexpr
is a column of data typeBLOB
. -
If you specify
STRICT
, then this condition considers only strict JSON syntax to be well-formed JSON data. If you specifyLAX
, then this condition also considers lax JSON syntax to be well-formed JSON data. The default isLAX
. Refer to Oracle Database JSON Developer’s Guide for more information on strict and lax JSON syntax. -
If you specify
WITH
UNIQUE
KEYS
, then this condition considers JSON data to be well-formed only if key names are unique within each object. If you specifyWITHOUT
UNIQUE
KEYS
, then this condition considers JSON data to be well-formed even if duplicate key names occur within an object. AWITHOUT
UNIQUE
KEYS
test performs faster than aWITH
UNIQUE
KEYS
test. The default isWITHOUT
UNIQUE
KEYS
.
Examples
Testing for STRICT or LAX JSON Syntax: Example
The following statement creates table t
with column col1
:
CREATE TABLE t (col1 VARCHAR2(100));
The following statements insert values into column col1
of table t
:
INSERT INTO t VALUES ( '[ "LIT192", "CS141", "HIS160" ]' ); INSERT INTO t VALUES ( '{ "Name": "John" }' ); INSERT INTO t VALUES ( '{ "Grade Values" : { A : 4.0, B : 3.0, C : 2.0 } }'); INSERT INTO t VALUES ( '{ "isEnrolled" : true }' ); INSERT INTO t VALUES ( '{ "isMatriculated" : False }' ); INSERT INTO t VALUES (NULL); INSERT INTO t VALUES ('This is not well-formed JSON data');
The following statement queries table t
and returns col1
values that are well-formed JSON data. Because neither the STRICT
nor LAX
keyword is specified, this example uses the default LAX
setting. Therefore, this query returns values that use strict or lax JSON syntax.
SELECT col1 FROM t WHERE col1 IS JSON; COL1 -------------------------------------------------- [ "LIT192", "CS141", "HIS160" ] { "Name": "John" } { "Grade Values" : { A : 4.0, B : 3.0, C : 2.0 } } { "isEnrolled" : true } { "isMatriculated" : False }
The following statement queries table t
and returns col1
values that are well-formed JSON data. This example specifies the STRICT
setting. Therefore, this query returns only values that use strict JSON syntax.
SELECT col1 FROM t WHERE col1 IS JSON STRICT; COL1 -------------------------------------------------- [ "LIT192", "CS141", "HIS160" ] { "Name": "John" } { "isEnrolled" : true }
The following statement queries table t
and returns col1
values that use lax JSON syntax, but omits col1
values that use strict JSON syntax. Therefore, this query returns only values that contain the exceptions allowed in lax JSON syntax.
SELECT col1 FROM t WHERE col1 IS NOT JSON STRICT AND col1 IS JSON LAX; COL1 -------------------------------------------------- { "Grade Values" : { A : 4.0, B : 3.0, C : 2.0 } } { "isMatriculated" : False }
Testing for Unique Keys: Example
The following statement creates table t
with column col1
:
CREATE TABLE t (col1 VARCHAR2(100));
The following statements insert values into column col1
of table t
:
INSERT INTO t VALUES ('{a:100, b:200, c:300}'); INSERT INTO t VALUES ('{a:100, a:200, b:300}'); INSERT INTO t VALUES ('{a:100, b : {a:100, c:300}}');
The following statement queries table t and returns col1
values that are well-formed JSON data with unique key names within each object:
SELECT col1 FROM t WHERE col1 IS JSON WITH UNIQUE KEYS; COL1 --------------------------- {a:100, b:200, c:300} {a:100, b : {a:100, c:300}}
The second row is returned because, while the key name a
appears twice, it is in two different objects.
The following statement queries table t
and returns col1
values that are well-formed JSON data, regardless of whether there are unique key names within each object:
SELECT col1 FROM t WHERE col1 IS JSON WITHOUT UNIQUE KEYS; COL1 --------------------------- {a:100, b:200, c:300} {a:100, a:200, b:300} {a:100, b : {a:100, c:300}}
Using IS JSON as a Check Constraint: Example
The following statement creates table j_purchaseorder
, which will store JSON data in column po_document
. The statement uses the IS
JSON
condition as a check constraint to ensure that only well-formed JSON is stored in column po_document
.
CREATE TABLE j_purchaseorder (id RAW (16) NOT NULL, date_loaded TIMESTAMP(6) WITH TIME ZONE, po_document CLOB CONSTRAINT ensure_json CHECK (po_document IS JSON));
JSON_EQUAL Condition
Purpose
The Oracle SQL condition JSON_EQUAL
compares two JSON
values and returns true if they are equal. It returns false if the two values are not equal. The input values must be valid JSON
data.
The comparison ignores insignificant whitespace and insignificant object member order. For example, JSON
objects are equal, if they have the same members, regardless of their order.
If either of the two compared inputs has one or more duplicate fields, then the value returned by JSON_EQUAL
is unspecified.
JSON_EQUAL
supports ERROR ON ERROR
, FALSE ON ERROR
, and TRUE ON ERROR
. The default is FALSE ON ERROR
. A typical example of an error is when the input expression is not valid JSON
.
Examples
The following statements return TRUE:
JSON_EQUAL('{}', '{ }')
JSON_EQUAL('{a:1, b:2}', '{b:2 , a:1 }')
The following statement return FALSE:
JSON_EQUAL('{a:"1"}', '{a:1 }') -> FALSE
The following statement results in a ORA-40441
JSON
syntax error
JSON_EQUAL('[1]', '[}' ERROR ON ERROR)
See Also:
-
Oracle Database JSON Developer’s Guide for more information.
JSON_EXISTS Condition
Use the SQL/JSON condition JSON_EXISTS
to test whether a specified JSON value exists in JSON data. This condition returns TRUE
if the JSON value exists and FALSE
if the JSON value does not exist.
JSON_exists_condition::=
(JSON_basic_path_expression
: See Oracle Database JSON Developer’s Guide)
JSON_passing_clause::=
JSON_exists_on_error_clause::=
expr
Use this clause to specify the JSON data to be evaluated. For expr
, specify an expression that evaluates to a text literal. If expr
is a column, then the column must be of data type VARCHAR2
, CLOB
, or BLOB
. If expr
evaluates to null or a text literal of length zero, then the condition returns UNKNOWN
.
If expr
is not a text literal of well-formed JSON data using strict or lax syntax, then the condition returns FALSE
by default. You can use the JSON_exists_on_error_clause
to override this default behavior. Refer to the JSON_exists_on_error_clause.
FORMAT JSON
You must specify FORMAT
JSON
if expr
is a column of data type BLOB
.
JSON_basic_path_expression
Use this clause to specify a SQL/JSON path expression. The condition uses the path expression to evaluate expr
and determine if a JSON value that matches, or satisfies, the path expression exists. The path expression must be a text literal, but it can contain variables whose values are passed to the path expression by the JSON_passing_clause
. See Oracle Database JSON Developer’s Guide for the full semantics of JSON_basic_path_expression
.
JSON_passing_clause
Use this clause to pass values to the path expression. Forexpr
, specify a value of data type VARCHAR2
, NUMBER
, BINARY_DOUBLE
, DATE
, TIMESTAMP
, or TIMESTAMP
WITH
TIME
ZONE
. The result of evaluating expr
is bound to the corresponding identifier in the JSON_basic_path_expression
.
JSON_exists_on_error_clause
Use this clause to specify the value returned by this condition when expr
is not well-formed JSON data.
You can specify the following clauses:
-
ERROR
ON
ERROR
- Returns the appropriate Oracle error whenexpr
is not well-formed JSON data. -
TRUE
ON
ERROR
- ReturnsTRUE
whenexpr
is not well-formed JSON data. -
FALSE
ON
ERROR
- ReturnsFALSE
whenexpr
is not well-formed JSON data. This is the default.
Examples
The following statement creates table t
with column name
:
CREATE TABLE t (name VARCHAR2(100));
The following statements insert values into column name
of table t
:
INSERT INTO t VALUES ('[{first:"John"}, {middle:"Mark"}, {last:"Smith"}]'); INSERT INTO t VALUES ('[{first:"Mary"}, {last:"Jones"}]'); INSERT INTO t VALUES ('[{first:"Jeff"}, {last:"Williams"}]'); INSERT INTO t VALUES ('[{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]'); INSERT INTO t VALUES (NULL); INSERT INTO t VALUES ('This is not well-formed JSON data');
The following statement queries column name
in table t
and returns JSON data that consists of an array whose first element is an object with property name first
. The ON
ERROR
clause is not specified. Therefore, the JSON_EXISTS
condition returns FALSE
for values that are not well-formed JSON data.
SELECT name FROM t WHERE JSON_EXISTS(name, '$[0].first'); NAME -------------------------------------------------- [{first:"John"}, {middle:"Mark"}, {last:"Smith"}] [{first:"Mary"}, {last:"Jones"}] [{first:"Jeff"}, {last:"Williams"}] [{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]
The following statement queries column name
in table t
and returns JSON data that consists of an array whose second element is an object with property name middle
. The ON
ERROR
clause is not specified. Therefore, the JSON_EXISTS
condition returns FALSE
for values that are not well-formed JSON data.
SELECT name FROM t WHERE JSON_EXISTS(name, '$[1].middle'); NAME -------------------------------------------------------------------------------- [{first:"John"}, {middle:"Mark"}, {last:"Smith"}] [{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]
The following statement is similar to the previous statement, except that the TRUE
ON
ERROR
clause is specified. Therefore, the JSON_EXISTS
condition returns TRUE
for values that are not well-formed JSON data.
SELECT name FROM t WHERE JSON_EXISTS(name, '$[1].middle' TRUE ON ERROR); NAME -------------------------------------------------------------------------------- [{first:"John"}, {middle:"Mark"}, {last:"Smith"}] [{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}] This is not well-formed JSON data
The following statement queries column name
in table t
and returns JSON data that consists of an array that contains an element that is an object with property name last
. The wildcard symbol (*
) is specified for the array index. Therefore, the query returns arrays that contain such an object, regardless of its index number in the array.
SELECT name FROM t WHERE JSON_EXISTS(name, '$[*].last'); NAME -------------------------------------------------- [{first:"John"}, {middle:"Mark"}, {last:"Smith"}] [{first:"Mary"}, {last:"Jones"}] [{first:"Jeff"}, {last:"Williams"}] [{first:"Jean"}, {middle:"Anne"}, {last:"Brown"}]
JSON_TEXTCONTAINS Condition
Use the SQL/JSON condition JSON_TEXTCONTAINS
to test whether a specified character string exists in JSON property values. You can use this condition to filter JSON data on a specific word or number.
This condition takes the following arguments:
-
A table or view column that contains JSON data. A JSON search index, which is an Oracle Text index designed specifically for use with JSON data, must be defined on the column. Each row of JSON data in the column is referred to as a JSON document.
-
A SQL/JSON path expression. The path expression is applied to each JSON document in an attempt to match a specific JSON object within the document. The path expression can contain only JSON object steps; it cannot contain JSON array steps.
-
A character string. The condition searches for the character string in all of the string and numeric property values in the matched JSON object, including array values. The string must exist as a separate word in the property value. For example, if you search for 'beth', then a match will be found for string property value "beth smith", but not for "elizabeth smith". If you search for '10', then a match will be found for numeric property value 10 or string property value "10 main street", but a match will not be found for numeric property value 110 or string property value "102 main street".
This condition returns TRUE
if a match is found, and FALSE
if a match is not found.
JSON_textcontains_condition::=
(JSON_basic_path_expression
: See Oracle Database JSON Developer’s Guide)
column
Specify the name of the table or view column containing the JSON data to be tested. The column must be of data type VARCHAR2
, CLOB
, or BLOB
. A JSON search index, which is an Oracle Text index designed specifically for use with JSON data, must be defined on the column. If a column value is a null or a text literal of length zero, then the condition returns UNKNOWN
.
If a column value is not a text literal of well-formed JSON data using strict or lax syntax, then the condition returns FALSE
.
JSON_basic_path_expression
Use this clause to specify a SQL/JSON path expression. The condition uses the path expression to evaluate column
and determine if a JSON value that matches, or satisfies, the path expression exists. The path expression must be a text literal. See Oracle Database JSON Developer’s Guide for the full semantics of JSON_basic_path_expression
.
string
The condition searches for the character string specified by string
. The string must be enclosed in single quotation marks.
Examples
The following statement creates table families
with column family_doc
:
CREATE TABLE families (family_doc VARCHAR2(200));
The following statement creates a JSON search index on column family_doc
:
CREATE INDEX ix ON families(family_doc) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');
The following statements insert JSON documents that describe families into column family_doc
:
INSERT INTO families VALUES ('{family : {id:10, ages:[40,38,12], address : {street : "10 Main Street"}}}'); INSERT INTO families VALUES ('{family : {id:11, ages:[42,40,10,5], address : {street : "200 East Street", apt : 20}}}'); INSERT INTO families VALUES ('{family : {id:12, ages:[25,23], address : {street : "300 Oak Street", apt : 10}}}');
The following statement commits the transaction:
COMMIT;
The following query returns the JSON documents that contain 10
in any property value in the document:
SELECT family_doc FROM families WHERE JSON_TEXTCONTAINS(family_doc, '$', '10'); FAMILY_DOC -------------------------------------------------------------------------------- {family : {id:10, ages:[40,38,12], address : {street : "10 Main Street"}}} {family : {id:11, ages:[42,40,10,5], address : {street : "200 East Street", apt : 20}}} {family : {id:12, ages:[25,23], address : {street : "300 Oak Street", apt : 10}}}
The following query returns the JSON documents that contain 10 in the id
property value:
SELECT family_doc FROM families where json_textcontains(family_doc, '$.family.id', '10'); FAMILY_DOC -------------------------------------------------------------------------------- {family : {id:10, ages:[40,38,12], address : {street : "10 Main Street"}}}
The following query returns the JSON documents that have a 10 in the array of values for the ages
property:
SELECT family_doc FROM families WHERE JSON_TEXTCONTAINS(family_doc, '$.family.ages', '10'); FAMILY_DOC -------------------------------------------------------------------------------- {family : {id:11, ages:[42,40,10,5], address : {street : "200 East Street", apt : 20}}}
The following query returns the JSON documents that have a 10 in the address
property value:
SELECT family_doc FROM families WHERE JSON_TEXTCONTAINS(family_doc, '$.family.address', '10'); FAMILY_DOC -------------------------------------------------------------------------------- {family : {id:10, ages:[40,38,12], address : {street : "10 Main Street"}}} {family : {id:12, ages:[25,23], address : {street : "300 Oak Street", apt : 10}}}
The following query returns the JSON documents that have a 10 in the apt
property value:
SELECT family_doc FROM families WHERE JSON_TEXTCONTAINS(family_doc, '$.family.address.apt', '10'); FAMILY_DOC -------------------------------------------------------------------------------- {family : {id:12, ages:[25,23], address : {street : "300 Oak Street", apt : 10}}}