is json and
is not json are complementary. They test whether their argument is syntactically correct, that is, well-formed, JSON data. You can use them in a
CASE expression or the
WHERE clause of a
If the argument is syntactically correct then
is json returns true and
is not json returns false. If the argument cannot be evaluated for some reason (for example, if an error occurs during parsing) then the data is considered to not be well-formed:
is json returns false;
is not json returns true. Well-formed data means syntactically correct data. JSON data can be well-formed in two senses, referred to as strict and lax syntax.
By default, field names need not be unique for a given JSON object. But you can specify that particular JSON data is to be considered well-formed only if none of its objects have duplicate field names.
The JSON standard does not specify whether field names must be unique for a given JSON object. This means that, a priori, a well-formed JSON object can have multiple members that have the same field name. This is the default behavior for handling JSON data in Oracle Database because checking for duplicate names takes additional time.
You can specify that particular JSON data is to be considered well-formed only if all of the objects it contains have unique field names, that is, no object has duplicate field names. You do this by using the keywords
WITH UNIQUE KEYS with SQL/JSON condition
is json.Foot 1
If you do not specify
UNIQUE KEYS, or if you use the keywords
WITHOUT UNIQUE KEYS, then objects can have duplicate field names and still be considered well-formed.
The evaluation that Oracle Database employs always uses only one of the object members that have a given field name; any other members with the same field name are ignored. It is unspecified which of multiple such members is used.
Whether duplicate field names are allowed in well-formed JSON data is orthogonal to whether Oracle uses strict or lax syntax to determine well-formedness.
Standard ECMA-404, the JSON Data Interchange Format, and ECMA-262, the ECMAScript Language Specification, define JSON syntax.
According to these specifications, each JSON field and each string value must be enclosed in double quotation marks (
"). Oracle supports this strict JSON syntax, but it is not the default syntax.
'). Oracle also supports this lax JSON syntax, and it is the default syntax.
Case variations for keywords
null (for example,
An extra comma (
,) after the last element of an array or the last member of an object (for example,
[a, b, c
Numerals with one or more leading zeros (for example,
Fractional numerals that lack
0 before the decimal point (for example,
.14 instead of
Numerals with no fractional part after the decimal point (for example,
A plus sign (
+) preceding a numeral, meaning that the number is non-negative (for example,
This syntax too is allowed as part of the Oracle default (lax) JSON syntax. (See the JSON standard for the strict numeral syntax.)
In addition to the ASCII space character (U+0020), the JSON standard defines the following characters as insignificant (ignored) whitespace when used outside a quoted field or a string value:
Tab, horizontal tab (
^I, decimal 9, U+0009,
Line feed, newline (
^J, decimal 10, U+000A,
Carriage return (
^M, decimal 13, U+000D,
The lax JSON syntax, however, treats all of the ASCII control characters (Control+0 through Control+31), as well as the ASCII space character (decimal 32, U+0020), as (insignificant) whitespace characters. The following are among the control characters:
^@, decimal 0, U+0000,
^G, decimal 7, U+0007,
Vertical tab (
^K, decimal 11, U+000B)
^[, decimal 27, U+001B,
^?, decimal 127, U+007F)
An ASCII space character (U+0020) is the only whitespace character allowed, unescaped, within a quoted field or a string value. This is true for both the lax and strict JSON syntaxes.
For both strict and lax JSON syntax, quoted object field and string values can contain any Unicode character, but some of them must be escaped, as follows:
ASCII control characters are not allowed, except for those represented by the following escape sequences:
\f (form feed),
\n (newline, line feed),
\r (carriage return), and
\t (tab, horizontal tab).
Double quotation mark (
"), slash (
/), and backslash (
\) characters must also be escaped (preceded by a backslash):
In the lax JSON syntax, an object field that is not quoted can contain any Unicode character except whitespace and the JSON structural characters — left and right brackets (
]) and curly braces (
}), colon (
:), and comma (
,), but escape sequences are not allowed.
Any Unicode character can also be included in a name or string by using the ASCII escape syntax
\u followed by the four ASCII hexadecimal digits that represent the Unicode code point.
Note that other Unicode characters that are not printable or that might appear as whitespace, such as a no-break space character (U+00A0), are not considered whitespace for either the strict or the lax JSON syntax.
Table 5-1 shows some examples of JSON syntax.
Table 5-1 JSON Object Field Syntax Examples
Lax and strict: yes. Space characters are allowed.
Lax (and strict): no. Whitespace characters, including space characters, are not allowed in unquoted names.
Lax and strict: yes. Escape sequence for tab character is allowed.
Lax and strict: no. Unescaped tab character is not allowed. Space is the only unescaped whitespace character allowed.
Lax and strict: yes. Escaped double quotation marks are allowed, if name is quoted.
Lax and strict: no. Name must be quoted.
Lax: yes, strict: no. Single-quoted names (object fields and strings) are allowed for lax syntax only. Escaped double quotation mark is allowed in a quoted name.
Lax and strict: yes. Any Unicode character is allowed in a quoted name. This includes whitespace characters and characters, such as colon (
Lax (and strict): no. Structural characters are not allowed in unquoted names.
http://www.ecma-international.org/publications/standards/Ecma-404.htm for the syntax of JSON Data Interchange Format
The default JSON syntax for Oracle Database is lax. Strict or lax syntax matters only for SQL/JSON conditions
is json and
is not json. All other SQL/JSON functions and conditions use lax syntax for interpreting input and strict syntax when returning output.
If you need to be sure that particular JSON input data has strictly correct syntax, then check it first using
is json or
is not json.
You specify that data is to be checked as strictly well-formed according to the JSON standard by appending
(STRICT) (parentheses included) to an
is json or an
is not json expression.
Example 5-1 Using IS JSON in a Check Constraint to Ensure JSON Data is Strictly Well-Formed (Standard)
CREATE TABLE j_purchaseorder (id VARCHAR2 (32) NOT NULL PRIMARY KEY, date_loaded TIMESTAMP (6) WITH TIME ZONE, po_document VARCHAR2 (32767) CONSTRAINT ensure_json CHECK (po_document IS JSON (STRICT)));
Footnote LegendFootnote 1: An object field is sometimes called an object “key”.