Oracle JSON_SCALAR Function

The JSON_SCALAR function in Oracle SQL is a constructor function that converts a single SQL scalar value (like a NUMBER, VARCHAR2, or DATE) into its equivalent JSON scalar value, returned as the native JSON type.

A "scalar" is a simple, single value, not a complex structure like an object or array. This function is the standard way to convert individual SQL data types into their JSON representations.

What is the JSON_SCALAR Function in Oracle?

The JSON_SCALAR(sql_expr) function takes a single SQL value and converts it to a JSON type.

This is different from the JSON() constructor:

• JSON('{"a":1}') parses a text string that is already in JSON format.
• JSON_SCALAR('Hello') converts the SQL string 'Hello' into the JSON string value "Hello".
• JSON_SCALAR(123) converts the SQL NUMBER 123 into the JSON number value 123.

One of its most powerful features is its ability to convert SQL data types that are not part of the JSON standard (like DATE, TIMESTAMP, and VECTOR) into their proper JSON string or array representations.

JSON_SCALAR Function Syntax

The syntax for JSON_SCALAR is:

JSON_SCALAR(sql_expr [ NULL ON NULL | EMPTY STRING ON NULL ])

Let's break that down:

• sql_expr: The SQL scalar value or column you want to convert (e.g., 123, 'Hello', SYSDATE).
• NULL ON NULL (Optional): If the sql_expr is a SQL NULL, this clause will return a JSON null value.
• EMPTY STRING ON NULL (Optional): If the sql_expr is a SQL NULL, this clause will return an empty JSON string ("").
• (Default): If you omit both NULL clauses, a SQL NULL input will return a SQL NULL (which is different from a JSON null).

Oracle JSON_SCALAR Function Examples

(Note: These queries will only work if your database compatible parameter is 20 or higher.)

Example 1: Converting Different SQL Data Types with JSON_SCALAR

This example shows how JSON_SCALAR converts a SQL NUMBER, VARCHAR2, and DATE into their JSON type equivalents.

Query:

SELECT 
  JSON_SCALAR(123.45) AS "Json_Number",
  JSON_SCALAR('Hello World') AS "Json_String",
  JSON_SCALAR(DATE '2025-11-20') AS "Json_Date"
FROM DUAL;

Result: (Notice the string is quoted, the number is not, and the date is converted to a standard ISO string.)

Json_Number Json_String     Json_Date
----------- --------------- --------------------------
123.45      "Hello World"   "2025-11-20T00:00:00"

Example 2: Handling NULL Values with JSON_SCALAR

This example demonstrates the special NULL handling options of the function.

Query:

SELECT 
  JSON_SCALAR(NULL) AS "Default_Null_Handling",
  JSON('null')      AS "JSON_Null_Value",
  JSON('""')        AS "Empty_String_Value"
FROM dual;

Result: (The first column is a true SQL NULL, while the other two are JSON type instances containing null and "".)

Default_Null_Handling JSON_Null_Value Empty_String_Value
--------------------- --------------- ------------------
(NULL)                null            ""