JSON_SCALAR

The JSON_SCALAR function accepts a SQL scalar value as input and returns a corresponding JSON scalar value as a JSON type instance. The value can be an Oracle-specific JSON-language type, including types that are not part of the JSON standard (such as DATE).

Syntax

JSON_SCALAR(json_expr [NULL_clause] [JSON_SCALAR_on_error])

NULL_clause::= [{SQL|JSON}] NULL ON NULL | EMPTY STRING ON NULL

JSON_SCALAR_on_error::= {ERROR|NULL} ON ERROR

Parameters

JSON_SCALAR has the parameters:

Parameter Description

json_expr

Input to the JSON_SCALAR function.

The json_expr parameter can be an instance of any of these SQL data types:

  • NUMBER, TT_TINYINT, TT_SMALLINT, TT_INTEGER, TT_BIGINT

  • BINARY_DOUBLE, BINARY_FLOAT, FLOAT

  • BINARY, VARBINARY

  • JSON

  • BLOB, CLOB, NCLOB

  • CHAR, VARCHAR2, NCHAR, NVARCHAR2

  • DATE, TIMESTAMP, TT_DATE, TT_TIMESTAMP

[NULL_clause]

Optional clause used for SQL NULL handling.

[{SQL|JSON}] NULL ON NULL

One of the options for SQL NULL handling.

If the input to JSON_SCALAR is a SQL NULL value, the return value is as follows:

  • If you specify SQL NULL ON NULL, the return value is SQL NULL.

  • If you specify JSON NULL ON NULL, the return value is JSON null.

  • If you specify NULL ON NULL, the return value is JSON null.

  • If you do not specify this clause, the default return value is SQL NULL.

You cannot specify this clause with the EMPTY STRING ON NULL clause.

[EMPTY STRING ON NULL]

Another option for SQL NULL handling.

If the input to JSON_SCALAR is a SQL NULL value, and you specify EMPTY STRING ON NULL, the value returned is an empty JSON string.

You cannot specify this clause with the [{SQL|JSON}] NULL ON NULL clause.

[JSON_SCALAR_on_error]

Optional clause that controls how error handling is processed.

If you do not specify this clause, the default is ERROR ON ERROR.

Valid clauses are ERROR ON ERROR and NULL ON ERROR (described in the rows that follow).

ERROR ON ERROR

If there is an error, and you specify ERROR ON ERROR, JSON_SCALAR returns the error.

NULL ON ERROR

If there is an error, and you specify NULL ON ERROR, JSON_SCALAR returns NULL and does not return the error.

Description

  • The JSON_SCALAR function accepts a SQL scalar value as input and returns a corresponding JSON scalar value as a JSON type instance.

  • The JSON_SCALAR function converts SQL scalar values into their corresponding JSON scalar representations.

  • The input scalar value can be an Oracle-specific JSON-language type that is not part of the JSON standard, such as DATE or TIMESTAMP.

  • The JSON_SCALAR function does not parse input as JSON.

  • The returned JSON type instance is a JSON-language scalar value supported by TimesTen. For example, JSON_SCALAR(SYSDATE) returns an Oracle JSON value of type DATE (as an instance of SQL data type JSON).

  • If the input to JSON_SCALAR results in a SQL NULL value, you can control the returned value by specifying the NULL_clause. For example,

    • SQL NULL ON NULL: Returns SQL NULL.

    • JSON NULL ON NULL or NULL ON NULL: Returns JSON null.

    • EMPTY STRING ON NULL: Returns an empty JSON string (" ").

    • If you do not specify the NULL_clause, the default return value is SQL NULL.

  • You can control error handling by specifying ERROR ON ERROR or NULL ON ERROR. The default is ERROR ON ERROR.

Examples

The following examples illustrate various uses of the JSON_SCALAR function.

The examples use ttIsql and are run from the ttIsql Command> prompt.

Use JSON_SCALAR with SYSDATE.
SELECT JSON_SCALAR (SYSDATE);
The output is similar to the following:
< "2026-03-05T21:37:18" >
1 row found.
Use JSON_SCALAR with a numeric.
SELECT JSON_SCALAR(1);
The output is the following:
< 1 >
1 row found.

Let's create a table and store data of type JSON in a column. Let's drop the table first, in case it already exists. 

DROP TABLE test;
CREATE TABLE test (col1 JSON);
Insert two rows into the table.
INSERT INTO test VALUES ('{a:100, b:200}');
INSERT INTO test VALUES ('{a:300}');

Use JSON_SCALAR to return data from the JSON column. 

SELECT JSON_SCALAR (ta.col1.b) FROM test ta;
The output is the following. Note that JSON_SCALAR returns SQL NULL for the second row.
< 200 >
< <NULL> >
2 rows found.
Let's add the JSON NULL ON NULL clause to return JSON null instead of SQL NULL.
SELECT JSON_SCALAR (ta.col1.b JSON NULL ON NULL) FROM test ta;
The output is the following:
< 200 >
< null >
2 rows found.

Let's create a table and store data of type JSON in a column. Let's drop the table first, in case it already exists. 

DROP TABLE test;
CREATE TABLE test (col1 JSON);
INSERT INTO test VALUES ('[{name:"abc"}, {name:"xyz"}]');
The output shows one row is inserted into the table:
1 row inserted.
Attempt to use JSON_SCALAR to return a non-scalar type.
SELECT JSON_SCALAR(ta.col1.name) FROM test ta;

Sine JSON_SCALAR only supports scalar types, an error results. 

 2387: Json error: ORA-40575: JSON operation only supports scalar types.
The command failed.

See Also