Oracle JSON_OBJECTAGG Function

The JSON_OBJECTAGG function in Oracle SQL is an aggregate function. Its job is to take all the rows in a group and combine them into a single JSON object.

This is the partner to JSON_ARRAYAGG (which aggregates rows into an array) and the "aggregate version" of JSON_OBJECT (which makes an object from the columns of a single row).

You use JSON_OBJECTAGG when you want to pivot data, turning rows of key-value pairs into a single JSON document. For example, you could turn a table of employee properties into one JSON object per employee.

What is the JSON_OBJECTAGG Function in Oracle?

The JSON_OBJECTAGG(KEY key_expr VALUE value_expr) function takes two columns as input from multiple rows. For each row, it creates a key-value pair and then aggregates all those pairs into one JSON object.

This is essential for:

• "Pivoting" rows of data into a single object.
• Creating a single JSON document from a GROUP BY query.
• Combining many rows of "settings" or "properties" into one JSON configuration object.

JSON_OBJECTAGG Function Syntax

The syntax for JSON_OBJECTAGG is:

JSON_OBJECTAGG(
    [KEY] key_expr VALUE value_expr
    [ NULL ON NULL | ABSENT ON NULL ]
    [ RETURNING data_type ]
)

Let's break that down:

• KEY key_expr: The column or value that will become the "key" or "property name" in the JSON object (e.g., 'Administration'). The KEY keyword is optional.
• VALUE value_expr: The column or value that will become the "value" for that key (e.g., 10).
• [NULL ON NULL | ABSENT ON NULL]:
  • ABSENT ON NULL (Default): If the value_expr is NULL, the entire key-value pair is omitted from the final object.
  • NULL ON NULL: If the value_expr is NULL, the key-value pair is included with a JSON null value (e.g., "Proxy": null).
• [RETURNING data_type] (Optional): Specifies the return type, such as VARCHAR2(4000) (default), CLOB, or JSON.

Oracle JSON_OBJECTAGG Function Examples

Because JSON_OBJECTAGG is an aggregate function that works on multiple rows, we must first simulate a multi-row table. We can do this using the DUAL table with UNION ALL.

Example 1: Creating a Simple Object from Rows using JSON_OBJECTAGG

This is the most common use case. Let's take a list of department names and their IDs (from 3 different "rows") and roll them up into a single JSON object.

Query:

-- First, we simulate a 'departments' table with 3 rows using DUAL
WITH departments AS (
  SELECT 'Administration' AS department_name, 10 AS department_id FROM DUAL UNION ALL
  SELECT 'Marketing' AS department_name, 20 AS department_id FROM DUAL UNION ALL
  SELECT 'Purchasing' AS department_name, 30 AS department_id FROM DUAL
)
-- Now, we aggregate the rows into one object
SELECT
  JSON_OBJECTAGG(
    KEY department_name VALUE department_id
  ) AS "Department_Numbers"
FROM departments;

Result: (The 3 rows are combined into one JSON object.)

Department_Numbers
----------------------------------------------------
{"Administration":10,"Marketing":20,"Purchasing":30}

Example 2: Handling NULL Values with JSON_OBJECTAGG

This example shows the difference between the default ABSENT ON NULL and using NULL ON NULL. We will aggregate a list of settings, where one of the values is NULL.

Query:

-- Simulate a 'settings' table where 'Proxy' is NULL
WITH settings AS (
  SELECT 'Theme' AS setting_key, 'Dark' AS setting_value FROM DUAL UNION ALL
  SELECT 'Font' AS setting_key, 'Arial' AS setting_value FROM DUAL UNION ALL
  SELECT 'Proxy' AS setting_key, NULL AS setting_value FROM DUAL
)
SELECT
  JSON_OBJECTAGG(
    KEY setting_key VALUE setting_value ABSENT ON NULL
  ) AS "Default_Handling",

  JSON_OBJECTAGG(
    KEY setting_key VALUE setting_value NULL ON NULL
  ) AS "Null_On_Null_Handling"
FROM settings;

Result: (The 'Proxy' key is missing from the first result, but present with a null value in the second.)

Default_Handling                    Null_On_Null_Handling
----------------------------------- ---------------------------------------------
{"Theme":"Dark","Font":"Arial"}     {"Theme":"Dark","Font":"Arial","Proxy":null}