JSON Type Constructor

The JSON type constructor (JSON) takes as input a textual JSON value (a scalar, object, or array), parses it, and returns the value as an instance of JSON type. You can use the JSON data type constructor JSON to parse textual JSON input ( a scalar, object, or array), and return it as an instance of type JSON.

Syntax

JSON (expr)

expr

expr::= CHAR|NCHAR|VARCHAR2|NVARCHAR2|BLOB|CLOB|NCLOB|JSON

The input to expr must be a syntactically valid textual representation of type CHAR, NCHAR, VARCHAR2, NVARCHAR2, BLOB, CLOB, NCLOB or JSON. The expr argument can also be a literal SQL string. A SQL NULL input value results in a JSON type instance of SQL NULL.

Examples

Example: Return a Value as an Instance of Type JSON from a Textual JSON String

From the command prompt (Command>) in ttIsql, input a textual JSON string to the JSON type constructor. Observe that the JSON type constructor returns the value as an instance of type JSON.

SELECT JSON('{"cust_name": ["John", "Barron"]}');

The output is as follows:

< {"cust_name":["John","Barron"]} >
1 row found.

Example: Return an Error from an Invalid Textual JSON String

From the command prompt (Command>) in ttIsql, input an invalid textual string to the JSON type constructor. Observe that the JSON type constructor returns a valid syntax error.

Command> SELECT JSON(‘{“cust_name”: [“John”, “Smith”]’];

The output is as follows:

37000: Syntax error or access violation
The command failed.

Example: Return a JSON Type Instance That Represents an Array

Use the JSON type constructor to input a textual string and return a JSON type instance that represents an array.

SELECT JSON('["apple","banana","cherry"]') AS json_array_example;

The output is as follows:

< ["apple","banana","cherry"] >
1 row found.

Example: Handle Scalar Values

Since the JSON type constructor parses the input as JSON data, if you specify the '{}' textual string as input, JSON type constructor returns an empty JSON object {} as an instance of type JSON.

This behavior is in contrast to JSON_SCALAR, which does not parse textual input but just converts it to a JSON string value. To produce the same JSON string using JSON type constructor, you must add explicit double-quote characters. For example, JSON('"{}"').

Let's look at some examples:

In this example, JSON type constructors returns an empty object.

Command> SELECT JSON('{}');
< {} >
1 row found.

Observe that JSON_SCALAR converts the same input to a JSON string value.

Command> SELECT JSON_SCALAR ('{}');
< "{}" >
1 row found.

To produce the same JSON string using JSON type constructor, use explicit double-quotes.

Command> SELECT JSON('"{}"');
< "{}" >
1 row found.