In Oracle Database, the JSON_OBJECTAGG() function creates a JSON object from a key-value pair.

Typically, the property key, the property value, or both are columns of SQL expressions.

Syntax

The syntax goes like this:

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

Where:

• [ KEY ] key_expr VALUE val_expr specifies a property key-value pair.
• FORMAT JSON indicates that the input string is JSON, and will therefore not be quoted in the output.
• JSON_on_null_clause specifies the behaviour to use when expr equates to null (i.e. either include null values in the output or don’t).
• JSON_returning_clause specifies the type of return value.
• STRICT checks whether or not the output of the JSON generation function is correct JSON. If the check fails, a syntax error is raised.
• WITH UNIQUE KEYS guarantees that generated JSON objects have unique keys.

Example

Here’s an example to demonstrate how it works:

SELECT JSON_OBJECTAGG(KEY 'score' VALUE 37) FROM DUAL;

Result:

{"score":37}

The KEY part is optional, so the following achieves the same result:

SELECT JSON_OBJECTAGG('score' VALUE 37) FROM DUAL;

Result:

{"score":37}

Nested JSON

If one of the values you pass contains JSON, you can use the FORMAT JSON argument to specify that it contains JSON and should not be quoted in the output.

Example:

SELECT JSON_OBJECTAGG(
    KEY 'details' 
    VALUE '{"name": "Peter", "score": 64}' 
    FORMAT JSON
    ) 
FROM DUAL;

Result:

{"details":{"name": "Peter", "score": 64}}

Here’s what happens if we remove the FORMAT JSON part:

SELECT JSON_OBJECTAGG(
    KEY 'details' 
    VALUE '{"name": "Peter", "score": 64}'
    ) 
FROM DUAL;

Result:

{"details":"{\"name\": \"Peter\", \"score\": 64}"} 

Database Example

The JSON_OBJECTAGG() function is typically used to produce JSON documents based on the values in a database column.

Suppose we run the following query:

SELECT *
FROM jobs
WHERE min_salary > 9000;

Result:

    JOB_ID                        JOB_TITLE    MIN_SALARY    MAX_SALARY 
__________ ________________________________ _____________ _____________ 
AD_PRES    President                                20000         40000 
AD_VP      Administration Vice President            15000         30000 
SA_MAN     Sales Manager                            10000         20000

Here’s an example of how we can use the JSON_OBJECTAGG() function to produce a JSON document from two of the above columns:

SELECT JSON_OBJECTAGG(KEY job_id VALUE min_salary) 
FROM jobs
WHERE min_salary > 9000;

Result:

{"AD_PRES":20000,"AD_VP":15000,"SA_MAN":10000}

In this case, the job_id column is the key, and the min_salary column is the value.

See Oracle’s documentation for a more detailed explanation of this function.