SQL/JSON Conditions IS JSON and IS NOT JSON

5 SQL/JSON Conditions IS JSON and IS NOT JSON

SQL/JSON conditions is json and is not json are complementary. They test whether their argument is syntactically correct, that is, well-formed, JSON data. With optional keyword VALIDATE they test whether the data is also valid with respect to a given JSON schema.

You can use is json and is not json in a CASE expression or the WHERE clause of a SELECT statement. You can use is json in a check constraint.

If the data tested is syntactically correct and keyword VALIDATE is not present then is json returns true and is not json returns false.

If keyword VALIDATE is present then the data is tested to ensure that it is (or is not) both well-formed and valid with respect to the specified JSON schema. Keyword VALIDATE (optionally followed by keyword USING) must be followed by a SQL string literal that is the JSON schema to validate against.

For JSON-type data, as an alternative to using VALIDATE with a simple JSON schema that checks only that the data is an object, array, or scalar value, you can use keyword OBJECT, ARRAY, or SCALAR, respectively. For example, this condition tests whether the data is an object:

is json OBJECT

Modifier keyword SCALAR can be followed by a keyword that specifies the required type of scalar: BOOLEAN, BINARY, BINARY_DOUBLE, BINARY_FLOAT, DATE, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, NULL, NUMBER, STRING, TIMESTAMP, or TIMESTAMP WITH TIME ZONE.

You can also use more than one JSON-type modifier, in which case you separate them with commas and wrap the list in parentheses. For example, (OBJECT, SCALAR DATE) allows only objects or dates, and these two equivalent conditions test whether the data is an object or a scalar value:

is json (OBJECT, SCALAR)

is not json ARRAY

You can combine the use of JSON-type modifier keywords with other keywords. (The modifier keywords need to come first.) But if the effect of the modifier keywords conflicts with the effect of other keywords present, such as specifying is json SCALAR DISALLOW SCALARS, then an error is raised.

You cannot use JSON-type modifier keywords when is json is used as a check constraint:

CREATE TABLE t1 (c1 JSON CHECK (c1 is json OBJECT));
CREATE TABLE t1 (c1 JSON CHECK (c1 is json OBJECT))
                                           *
ERROR at line 1:
ORA-02252: check constraint condition not properly ended

If an error occurs during parsing (or validating) an is (not) JSON condition, the error is not raised, and the data is considered to not be well-formed (or not valid): is json returns false; is not JSON returns true. (If an error occurs other than during parsing or validating then that error is raised.)

Well-formed data means syntactically correct data. JSON data stored textually can be well-formed in two senses, referred to as strict and lax syntax. In addition, for textual JSON data you can specify whether a JSON object can have duplicate fields (keys).

For JSON data of any data type (textual or JSON type):

You can specify whether a document of well-formed data can have a scalar value at top level (provided database initialization parameter compatible is 20 or greater). And you can specify that it must also be valid.
You can specify that the data must (or must not, with is not JSON) validate against a given JSON schema.

Whenever textual JSON data is generated inside the database it satisfies condition is json with keyword STRICT. This includes generation in these ways:

Using a SQL/JSON generation function (unless you specify keyword STRICT with FORMAT JSON, which means that you declare that the data is JSON data; you vouch for it, so its well-formedness is not checked)
Using SQL function json_serialize
Using SQL function to_clob, to_blob, or to_string on a PL/SQL DOM
Using SQL/JSON function json_query
Using SQL/JSON function json_table with FORMAT JSON

Note:

JSON type data has only unique object keys (field names), and the notions of strict and lax syntax do not apply to it. When you serialize JSON data (of any data type) to produce textual JSON data the result always has strict syntax.

If JSON data is stored using JSON data type and you use an is json check constraint then:

If you specify keywords DISALLOW SCALARS, the JSON column cannot store documents with top-level scalar JSON values.
If you specify no keywords or you specify any keywords other than DISALLOW SCALARS, the is json constraint is ignored. The keywords change nothing.

You can omit the keywords CHECK and IS JSON. For example, these two check constraints are equivalent; they each ensure that the value of column jcol is a JSON string:

CHECK (jcol IS JSON VALIDATE '{"type": "string"}')

jcol VALIDATE '{"type": "string"}'

5.1 Unique Versus Duplicate Fields in JSON Objects

The JSON standard recommends that a JSON object not have duplicate field names. Oracle Database enforces this for JSON type data by raising an error. If stored textually, Oracle recommends that you do not allow duplicate field names, by using an is json check constraint with keywords WITH UNIQUE KEYS.

If stored textually (VARCHAR2, CLOB, BLOB column), JSON data is, by default, allowed to have duplicate field names, simply because checking for duplicate names takes additional time. This default behavior for JSON data stored textually can result in inconsistent behavior, so Oracle recommends against relying on it.

You can override this default behavior, to instead raise an error if an attempt is made to insert data containing an object with duplicate fields. You do this by using an is json check constraint with the keywords WITH UNIQUE KEYS. (These keywords have no effect for data inserted into a JSON type column.)

Whether duplicate field names are allowed in well-formed textual JSON data is orthogonal to whether Oracle uses strict or lax syntax to determine well-formedness.

Parent topic: SQL/JSON Conditions IS JSON and IS NOT JSON

5.2 About Strict and Lax JSON Syntax

On input, the Oracle default syntax for JSON is lax. It reflects the JavaScript syntax for object fields; the Boolean and null values are not case-sensitive; and it is more permissive with respect to numerals, whitespace, and escaping of Unicode characters. Oracle outputs JSON data that strictly respects the standard.

Standards ECMA 404, the JSON Data Interchange Format, and ECMA 262, the ECMAScript Language Specification, define JSON syntax.

According to these specifications (prior to ECMAScript edition 5.1), each JSON field and each string value must be enclosed in double quotation marks ("). Oracle supports this strict JSON syntax, and it always respects this syntax on output, but this is not the default syntax for data on input.

In JavaScript notation, a field used in an object literal can be, but need not be, enclosed in double quotation marks. It can also be enclosed in single quotation marks ('). ECMA 262, 5.1 Edition relaxes the strict syntax in several ways, including allowing such fields. Oracle lax JSON syntax supports this syntax specified by ECMAScript 5.1.

More generally, in Oracle lax JSON syntax single quotation marks, like double quotation marks, delimit strings.

By default, Oracle uses lax JSON syntax when it accepts data on input. This syntax differs in one detail from that specified by ECMAScript 5.1: Oracle allows the inclusion in JSON strings of unescaped line and paragraph separator characters (U+2028 and U+2029). (This is also the case for JSON5, which, apart from this difference, is a proper subset of ECMAScript 5.1.)

In addition, in practice, some JavaScript implementations allow one or more of the following:

Case variations for true, false, and null (for example, TRUE, True, TrUe, fALSe, NulL).
A single extra comma (,) after the last element of an array or the last member of an object (for example, [a, b, c,], {a:b, c:d,}). (This is also allowed by ECMAScript 5.1.)
Numerals with one or more leading zeros (for example, 0042.3).
Fractional numerals that lack 0 before the decimal point (for example, .14 instead of 0.14).
Numerals with no fractional part after the decimal point (for example, 342. or 1.e27).
A plus sign (+) preceding a numeral, meaning that the number is non-negative (for example, +1.3).

This syntax too is allowed on input 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 (decimal 32, U+0020), the JSON standard defines the following ASCII characters as insignificant whitespace; that is, they are ignored when used outside a quoted field or a string value.

Tab, horizontal tab (HT, ^I, decimal 9, U+0009, \t)
Line feed, newline (LF, ^J, decimal 10, U+000A, \n)
Carriage return (CR, ^M, decimal 13, U+000D, \r)

However, Oracle lax JSON syntax treats all of the ASCII control characters (Control+0 through Control+31) as insignificant whitespace. The following are among the control characters:

Null (NUL, ^@, decimal 0, U+0000, \0)
Bell (NEL, ^G, decimal 7, U+0007, \a)
Vertical tab (VT, ^K, decimal 11, U+000B)
Escape (ESC, ^[, decimal 27, U+001B, \e)
Delete (DEL, ^?, decimal 127, U+007F)

Oracle strict JSON syntax treats all ASCII whitespace characters as insignificant, and ASCII space character (U+0020) is the only whitespace character allowed, unescaped, within a quoted field or a string value. In Oracle lax syntax, as in ECMAScript 5.1, all Unicode whitespace characters are treated as insignificant.

In Oracle lax JSON syntax, an object field name that is not quoted can contain any unescaped Unicode character except the following (but escape sequences are not allowed):

Whitespace, that is, Unicode characters that have the whitespace property — see Unicode character property, Whitespace.
JSON structural characters: left and right brackets ([, ]) and curly braces ({, }), colon (:), and comma (,).
Solidus (also known as slash), /. This is not allowed because /* begins a JSON comment (which is ended by */), in both ECMAScript 5.1 and Oracle lax syntax.
Reverse solidus (also known as backslash), \. This is not allowed because it introduces an escape sequence.
Single and double quotation marks (', "). These are not allowed because they function as string delimiters.

For both strict and lax JSON syntax, quoted object field names and other string values can contain any Unicode characters. Each character can be included by using the ASCII escape syntax \u followed by the four ASCII hexadecimal digits that represent the Unicode code point.

The following Unicode characters must be represented in field names and strings either by \u followed by their code point or by special escape sequences:

ASCII control characters backspace (CONTROL-H), form feed (CONTROL-L), newline (line feed) (CONTROL-J), carriage return (CONTROL-M), and (horizontal) tab (CONTROL-I).

Use \b for backspace; \f for form feed; \n for newline ; \r for carriage return; and \t for tab.
Reverse solidus (also known as backslash), \; and double quotation mark, ".

To escape these, precede each with a reverse solidus character: \\ and \", respectively. (You need not, but you can, escape a solidus/slash character the same way: \/.)

Table 5-1 shows some examples of JSON syntax.

Table 5-1 JSON Object Field Syntax Examples

Example	Well-Formed?
`"part number": 1234`	Lax and strict: yes. Space characters are allowed.
`part number: 1234`	Lax (and strict): no. Whitespace characters, including space characters, are not allowed in unquoted names.
`"part\tnumber": 1234`	Lax and strict: yes. Escape sequence for tab character is allowed.
`"part number": 1234`	Lax: yes, strict: no. Unescaped tab character is allowed only in the lax syntax. Space (U+0020) is the only unescaped whitespace character allowed in the strict syntax.
`"\"part\"number": 1234`	Lax and strict: yes. Escaped double quotation marks are allowed, if name is quoted.
`\"part\"number: 1234`	Lax and strict: no. The field name must be quoted to contain embedded double quotation marks.
`'\"part\"number': 1234`	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.
`"'part'number": 1234`	Lax: yes, strict: no. Single quotation marks are allowed, unescaped, inside strings for lax syntax (only).
`"pärt : number":1234`	Lax and strict: yes. Any Unicode character is allowed in a quoted name. This includes whitespace characters and characters, such as colon (`:`), that are structural in JSON.
`part:number:1234`	Lax (and strict): no. Structural characters are not allowed in unquoted names.

Related Topics

5.3 Specifying Strict or Lax JSON Syntax

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 textual JSON 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 illustrates this. It is identical to Example 4-2 except that it uses (STRICT) to ensure that all data inserted into the column is well-formed according to the JSON standard.