Oracle JSON Type Constructor

Important Prerequisite: The JSON type constructor is a modern feature. To use it, your database compatible parameter must be set to 20 or higher. If you are on an older version (like 19c or 12c), you will get an error, and you should use CLOB or VARCHAR2 columns with an IS JSON check constraint instead.

The JSON Type Constructor (written simply as JSON(...)) is the simplest and most direct way to convert a valid JSON text string into Oracle's native, binary JSON data type.

Think of it like TO_DATE or TO_NUMBER. It's a formal conversion function that takes text and returns an actual JSON type instance, which is highly optimized for storage and querying.

What is the JSON Type Constructor in Oracle?

The JSON(expr) constructor is a simple function that takes a text string (a VARCHAR2, CLOB, or BLOB) and parses it. If the string contains valid JSON, the function returns it as the native, binary JSON type.

This is useful for:

• Inserting data: This is the standard way to insert a string into a JSON type column.
• Validation: The constructor will fail with an error if the input string is not valid JSON, making it a strict validator.
• Type Conversion: Formally converting a VARCHAR2 string to a JSON type in a SELECT statement.

JSON Type Constructor Syntax

The syntax for the JSON constructor is very simple:

JSON(expr)

Let's break that down:

• expr: The text expression or column containing the JSON data. This must be a string (like VARCHAR2 or CLOB) or binary (like BLOB). The string must be well-formed JSON.

Oracle JSON Type Constructor Examples

Here are two practical examples of how to use the JSON constructor.

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

Example 1: Inserting Data into a JSON Type Column using JSON Constructor

This is the primary use case for the constructor. You have a table with a column of data type JSON, and you use the JSON() constructor in your INSERT statement to convert your text string into that native JSON type.

Query:

-- First, we need a table with a native JSON type column
-- (This requires compatible >= 20)
CREATE TABLE my_json_data (
  id    NUMBER,
  doc   JSON
);

-- Now, we use the JSON() constructor to insert a string
INSERT INTO my_json_data (id, doc)
VALUES (
  1,
  JSON('{"name": "John Doe", "status": "Active", "logins": [1, 2, 3]}')
);

Result:

1 row created.

In this example, the string '{"name":...}' was parsed and stored as an optimized binary JSON type in the doc column.

Example 2: Converting a String Array in a SELECT Statement using JSON Constructor

This example shows how the constructor works in a simple SELECT statement. It takes the text string of an array and converts it to a JSON data type instance.

Query:

SELECT 
  JSON('["Apple", "Banana", "Cherry"]') AS "My_JSON_Array"
FROM DUAL;

Result: (The result is a value of the JSON data type, not a VARCHAR2 string.)

My_JSON_Array
--------------------------------
["Apple","Banana","Cherry"]