Oracle JSON_OBJECTAGG() Function

Oracle JSON_OBJECTAGG() is a built-in aggregate function that constructs an object member for each key-value pair and returns a single JSON object containing these object members.

Oracle JSON_OBJECTAGG() Syntax

Here is the syntax for the Oracle JSON_OBJECTAGG() function:

JSON_OBJECTAGG
  ( [ KEY ] key_str VALUE expr [ FORMAT JSON ]
    [ JSON_on_null_clause ] [ JSON_agg_returning_clause ]
    [ STRICT ] [ WITH UNIQUE KEYS ]
  )

Parameters

key_str

Required. It specifies the property key name, which is a case-sensitive text literal.

expr

Required. It specifies the property value. For expr, you can specify any expression that evaluates to a SQL numeric literal, text literal, date, or timestamp. Date and timestamp data types are printed in the ISO 8601 date format as JSON strings in the generated JSON object or array. If expr evaluates to a numeric literal, the generated property value is a JSON number; otherwise, the generated property value is a case-sensitive JSON string value enclosed in double quotes.

FORMAT JSON

Optional. Use this optional clause to indicate that the input string is JSON and therefore not quoted in the output.

JSON_on_null_clause

Optional. You can use this clause to specify the function’s behavior when expr evaluates to null.

• NULL ON NULL - When NULL ON NULL is specified, the value for the given key will use a JSON null value.
• ABSENT ON NULL - If you specify this clause, the function will omit the property key-value pair in the JSON object.

JSON_agg_returning_clause

Optional. You can use this clause to specify the string data type returned by this function. You can specify the following data types:

• VARCHAR2[(``size [BYTE,CHAR])] - You must specify the size when you specify the VARCHAR2 data type elsewhere in SQL. However, you can omit the size in this clause. The default is VARCHAR2(4000).
• CLOB - Returns a character large object that contains single-byte or multibyte characters.
• BLOB - Returns a binary large object in the AL32UTF8 character set.

STRICT

Optional. You can specify the STRICT clause to validate whether the JSON-generating function’s output is correct. If the check fails, a syntax error is raised.

WITH UNIQUE KEYS

Optional. You can use WITH UNIQUE KEYS to ensure that the generated JSON object has unique keys.

Return Value

The Oracle JSON_OBJECTAGG() function returns a JSON object containing all key-value pairs specified by the arguments.

If any of the arguments are NULL, JSON_OBJECTAGG() returns NULL.

Oracle JSON_OBJECTAGG() Example

This example simulates a data table with the following statement:

SELECT 'Tim' name, 'English' subject, 80 score FROM DUAL
UNION ALL
SELECT 'Tim', 'Maths', 90 FROM DUAL
UNION ALL
SELECT 'Lucy', 'Maths', 96 FROM DUAL;

输出：

NAME    SUBJECT       SCORE
_______ __________ ________
Tim     English          80
Tim     Maths            90
Lucy    Maths            96

If you need to retrieve all the scores from the above dataset, use the following statement:

SELECT JSON_OBJECTAGG(KEY subject VALUE score) score
FROM (
    SELECT 'Tim' name, 'English' subject, 80 score FROM DUAL
    UNION ALL
    SELECT 'Tim', 'Maths', 90 FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Maths', 96 FROM DUAL
);

输出：

SCORE
_______________________________________
{"English":80,"Maths":90,"Maths":96}

If you need to retrieve the scores for each person from the above dataset, use the following statement with the GROUP BY clause:

SELECT name, JSON_OBJECTAGG(KEY subject VALUE score) score
FROM (
    SELECT 'Tim' name, 'English' subject, 80 score FROM DUAL
    UNION ALL
    SELECT 'Tim', 'Maths', 90 FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Maths', 96 FROM DUAL
)
GROUP BY name;

输出：

NAME    SCORE
_______ ____________________________
Lucy    {"Maths":96}
Tim     {"English":80,"Maths":90}

Conclusion

Oracle’s JSON_OBJECTAGG() is a built-in aggregate function that constructs an object member for each key-value pair and returns a single JSON object containing these object members.