Oracle JSON_OBJECT Function

The JSON_OBJECT function in Oracle SQL is a "constructor" function. Its job is the opposite of JSON_VALUE or JSON_QUERY. Instead of extracting data from JSON, JSON_OBJECT builds a new JSON object string from regular SQL key-value pairs.

This is essential when you need to generate JSON data (for an API, a report, or to store in a CLOB column) directly from your relational data. It's the partner to JSON_ARRAY, which builds JSON arrays.

What is the JSON_OBJECT Function in Oracle?

The JSON_OBJECT(KEY 'key' VALUE 'value', ...) function takes a list of key-value pairs and returns a single VARCHAR2 string (or CLOB/JSON type) representing a JSON object.

• KEY 'name' VALUE 'John' becomes "name": "John".
• KEY 'age' VALUE 30 becomes "age": 30.

It's the function you use to build JSON in the "{}" object format, right inside your SQL query.

JSON_OBJECT Function Syntax

The syntax for JSON_OBJECT has a few variations, but the most common form is:

JSON_OBJECT(
    [KEY] key_string VALUE expr [FORMAT JSON],
    [KEY] key_string VALUE expr [FORMAT JSON],
    ...
    [ NULL ON NULL | ABSENT ON NULL ]
    [ RETURNING data_type ]
)

A popular and shorter syntax uses a colon (:) instead of KEY and VALUE:

JSON_OBJECT(
    key_string : expr [FORMAT JSON],
    key_string : expr [FORMAT JSON],
    ...
)

Let's break that down:

• key_string: The property name (key) for your JSON object. This is a text string (e.g., 'name').
• expr: The SQL value, column, or expression that will be the JSON value (e.g., last_name, 30, SYSDATE).
• [FORMAT JSON] (Optional): This is a critical clause. If your expr is already a JSON string (like the output of JSON_ARRAY or another JSON_OBJECT), you must use FORMAT JSON to prevent Oracle from treating it as a plain string and "double-quoting" it.
• [NULL ON NULL | ABSENT ON NULL]:
  • ABSENT ON NULL (Default): If your expr is NULL, the entire key-value pair is omitted from the final object.
  • NULL ON NULL: If your expr is NULL, the key-value pair is included with a JSON null value (e.g., "middle_name": null).
• [RETURNING data_type] (Optional): Specifies the return type, such as VARCHAR2(4000) (default), CLOB, or JSON.

Oracle JSON_OBJECT Function Examples

All examples below use the DUAL table, which is a simple one-row table in Oracle for testing functions.

Example 1: Creating a Simple Object with JSON_OBJECT

This is the most common use case. We'll build a simple JSON object from text and number values. We will use the simple colon (:) syntax.

Query:

SELECT
  JSON_OBJECT(
    'name' : 'John Doe',
    'age'  : 30,
    'active' : 'true'  -- Note: This becomes a JSON string "true"
  ) AS "JSON_Object_String"
FROM DUAL;

Result: (The result is a single JSON string. Note that 'true' became a JSON string, not a boolean.)

JSON_Object_String
------------------------------------------
{"name":"John Doe","age":30,"active":"true"}

Example 2: Handling NULL Values with JSON_OBJECT

This example shows the critical difference between the default ABSENT ON NULL and NULL ON NULL.

Query:

SELECT
  JSON_OBJECT(
    'first_name' : 'John',
    'middle_name' : NULL,
    'last_name' : 'Doe'
    -- ABSENT ON NULL is the default
  ) AS "Default_Null_Handling",
  
  JSON_OBJECT(
    'first_name' : 'John',
    'middle_name' : NULL,
    'last_name' : 'Doe'
    NULL ON NULL  -- We explicitly ask to include NULLs
  ) AS "Null_On_Null_Handling"
FROM DUAL;

Result: (Notice the middle_name key is missing from the first result but present in the second.)

Default_Null_Handling            Null_On_Null_Handling
-------------------------------- -------------------------------------------
{"first_name":"John","last_name":"Doe"} {"first_name":"John","middle_name":null,"last_name":"Doe"}

Example 3: Nesting Objects and Arrays with FORMAT JSON

This is a more advanced example. If you want to put a JSON object or array inside another JSON object, you must use the FORMAT JSON clause.

Query:

SELECT
  JSON_OBJECT(
    'name' : 'Jane Doe',
    'address' : JSON_OBJECT(
                    'city' : 'New York',
                    'zip'  : 10001
                  ) FORMAT JSON, -- Nesting an object
    
    'skills' : JSON_ARRAY(
                   'SQL', 'Python', 'JSON'
                 ) FORMAT JSON   -- Nesting an array
                 
    NULL ON NULL -- Applied to the whole object
    
  ) AS "Complex_JSON"
FROM DUAL;

Result: (The address and skills values are correctly nested as JSON, not as double-quoted strings.)

Complex_JSON
--------------------------------------------------------------------------------
{"name":"Jane Doe","address":{"city":"New York","zip":10001},"skills":["SQL","Python","JSON"]}