JSON_OBJECT() Function in Oracle

by

In Oracle Database, the JSON_OBJECT() function creates a JSON object from a sequence of key-value pairs or one object type instance.

Syntax

The syntax goes like this:

JSON_OBJECT
  ( [ [ KEY ] key_expr VALUE val_expr [ FORMAT JSON ]
     ,]...
    [ JSON_on_null_clause ] [ JSON_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_OBJECT(KEY 'score' VALUE 37) FROM DUAL;

Result:

{"score":37}

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

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

Result:

{"score":37}

Multiple Key/Value Pairs

Here’s an example with multiple key/value pairs:

SELECT JSON_OBJECT(
    KEY 'fname' VALUE 'Homer',
    KEY 'lname' VALUE 'Escobar',
    KEY 'score' VALUE 237
    ) 
FROM DUAL;

Result:

{"fname":"Homer","lname":"Escobar","score":237}

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_OBJECT(
    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_OBJECT(
    KEY 'details' 
    VALUE '{"name": "Peter", "score": 64}'
    ) 
FROM DUAL;

Result:

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

Database Example

Suppose we run the following query:

SELECT *
FROM regions;

Result:

   REGION_ID               REGION_NAME 
____________ _________________________ 
           1 Europe                    
           2 Americas                  
           3 Asia                      
           4 Middle East and Africa

Here’s the same query, but with the selected columns passed to the JSON_OBJECT() function:

SELECT JSON_OBJECT(*) 
FROM regions;

Result:

{"REGION_ID":1,"REGION_NAME":"Europe"}                    
{"REGION_ID":2,"REGION_NAME":"Americas"}                  
{"REGION_ID":3,"REGION_NAME":"Asia"}                      
{"REGION_ID":4,"REGION_NAME":"Middle East and Africa"}

And here it is with just one column passed to the function:

SELECT JSON_OBJECT(region_name) 
FROM regions;

Result:

{"region_name":"Europe"}                    
{"region_name":"Americas"}                  
{"region_name":"Asia"}                      
{"region_name":"Middle East and Africa"}

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