Live SQL

JSON_OBJECTAGG Introduction

View All Scripts Login to Run Script

Script Name JSON_OBJECTAGG Introduction
Description An introduction to the Oracle 12.2 JSON_OBJECTAGG function translating column values into JSON key-value pairs.
Area SQL General / JSON
Contributor Darryl Hurley
Created Sunday January 15, 2017

Statement 1
Create a simple demo table with 1 numeric column and one varchar2 column.
Demo Data
```
CREATE TABLE demo 
( col1 NUMBER, 
  col2 VARCHAR2(30) )
```
```
Table created.
```
Statement 2
Create test row 1. Note that both column have values.
Test Row 1
```
INSERT INTO demo 
VALUES(1,'One')
```
```
1 row(s) inserted.
```
Statement 3
Create test row 2. Note that both column have values.
Test Row 2
```
INSERT INTO demo 
VALUES(2,'Two')
```
```
1 row(s) inserted.
```
Statement 4
Call the function with the two mandatory parameters to produce a JSON KEY-VALUE pair. The KEY which will come from the col2 column in the table and the VALUE which will come from the col1 column in the table. Think of the JSON key-value pair like this: {"DEMO.COL1" : "DEMO.COL2" }
Simple JSON_OBJECTAGG call
```
SELECT JSON_OBJECTAGG(KEY col2 VALUE col1 ) colname 
  FROM demo
```
```
COLNAME
{"One":1,"Two":2}
```

Statement 5

The JSON object is returned as single row.

Prove its just one row

SELECT COUNT(*) num_rows 
  FROM  ( SELECT JSON_OBJECTAGG(KEY col2 VALUE col1 ) 
            FROM demo )


NUM_ROWS
1

Statement 6

Remove Demo Row 2

DELETE demo 
WHERE col1 = 2

1 row(s) deleted.

Statement 7
Add a row with a NULL value for col2
Add Demo Row 2
```
INSERT INTO demo 
VALUES(2,NULL)
```
```
1 row(s) inserted.
```
Statement 8
The ABSENT ON NULL clause will remove any key-value pairs with a NULL value from the output. But in this case NULL has been specified for col2 which denotes the key. This means the name of the key cannot be resolved, let alone excluded, so Oracle throws an error.
ABSENT ON NULL
```
SELECT JSON_OBJECTAGG(KEY col2 VALUE col1 ABSENT ON NULL ) colname  
  FROM demo
```
```
ORA-40595: Name input to JSON generation function cannot be null.
```
More Details: https://docs.oracle.com/error-help/db/ora-40595

Statement 9

Remove Demo Row 2

DELETE demo 
WHERE col1 = 2

1 row(s) deleted.

Statement 10

Add a row with null for col1.

Add Demo Row 2

INSERT INTO demo 
VALUES(NULL,'Two')

1 row(s) inserted.

Statement 11
The ABSENT ON NULL clause causes any KEY-VALUE pairs where the value is NULL to be excluded or absent from the output. Note that NULL on NULL is the default behavior.
ABSENT ON NULL
```
SELECT JSON_OBJECTAGG(KEY col2 VALUE col1 ABSENT ON NULL ) colname  
  FROM demo
```
```
COLNAME
{"One":1}
```
Statement 12
The optional returning clause may cause problems if the length is not large enough. Note that the default datatype and length is VARCHAR2(4000) and the default length is 4000 when VARCHAR2 is specified by itself.
Returning Declared Too Small
```
SELECT JSON_OBJECTAGG(KEY col2 VALUE col1 ABSENT ON NULL RETURNING VARCHAR2(10)) colname 
  FROM demo
```
```
ORA-40459: output value too large (actual: 12, maximum: 10)
```
More Details: https://docs.oracle.com/error-help/db/ora-40459

Statement 13

A length of 4000 is the default for VARCHAR2

Specify Returning Datatype With No Length

SELECT JSON_OBJECTAGG(KEY col2 VALUE col1 ABSENT ON NULL RETURNING VARCHAR2) colname 
  FROM demo


COLNAME
{"One":1}

Statement 14
If a hardcoded string is specified for the value then it will be included once per row returned by the query.
String For Value
```
SELECT JSON_OBJECTAGG(KEY col2 VALUE 'HardCode' ) colname 
  FROM demo
```
```
COLNAME
{"One":"HardCode","Two":"HardCode"}
```

Statement 15

If the key is hardcoded then a single key-value pair is returned.

Harcode Key

SELECT JSON_OBJECTAGG(KEY 'Hardcode' VALUE col1 ) colname 
  FROM demo


COLNAME
{"Hardcode":1,"Hardcode":null}

Statement 16

Add Duplicate Keys

BEGIN 
  FOR counter IN 3..6 LOOP 
    INSERT INTO demo 
    VALUES(counter,'Three'); 
  END LOOP; 
END;

1 row(s) inserted.

Statement 17
If the columns in the table contain duplicate keys then only the last value is shown. The key-value pairs of Three:3 and Three:4 will not be shown.
WITHOUT DUPLICATE KEYS
```
SELECT JSON_OBJECTAGG(KEY col2 VALUE col1 ) colname  
  FROM demo
```
```
COLNAME
{"One":1,"Two":null,"Three":3,"Three":4,"Three":5,"Three":6}
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database