Demo Data
CREATE TABLE demo
( col1 NUMBER,
col2 VARCHAR2(30) )
Table created.
Test Row 1
INSERT INTO demo
VALUES(1,'One')
1 row(s) inserted.
Test Row 2
INSERT INTO demo
VALUES(2,'Two')
1 row(s) inserted.
Simple JSON_OBJECTAGG call
SELECT JSON_OBJECTAGG(KEY col2 VALUE col1 ) colname
FROM demo
| COLNAME | {"One":1,"Two":2} |
|---|
Prove its just one row
SELECT COUNT(*) num_rows
FROM ( SELECT JSON_OBJECTAGG(KEY col2 VALUE col1 )
FROM demo )
| NUM_ROWS | 1 |
|---|
Remove Demo Row 2
DELETE demo
WHERE col1 = 2
1 row(s) deleted.
Add Demo Row 2
INSERT INTO demo
VALUES(2,NULL)
1 row(s) inserted.
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
Remove Demo Row 2
DELETE demo
WHERE col1 = 2
1 row(s) deleted.
Add Demo Row 2
INSERT INTO demo
VALUES(NULL,'Two')
1 row(s) inserted.
ABSENT ON NULL
SELECT JSON_OBJECTAGG(KEY col2 VALUE col1 ABSENT ON NULL ) colname
FROM demo
| COLNAME | {"One":1} |
|---|
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
Specify Returning Datatype With No Length
SELECT JSON_OBJECTAGG(KEY col2 VALUE col1 ABSENT ON NULL RETURNING VARCHAR2) colname
FROM demo
| COLNAME | {"One":1} |
|---|
String For Value
SELECT JSON_OBJECTAGG(KEY col2 VALUE 'HardCode' ) colname
FROM demo
| COLNAME | {"One":"HardCode","Two":"HardCode"} |
|---|
Harcode Key
SELECT JSON_OBJECTAGG(KEY 'Hardcode' VALUE col1 ) colname
FROM demo
| COLNAME | {"Hardcode":1,"Hardcode":null} |
|---|
Add Duplicate Keys
BEGIN
FOR counter IN 3..6 LOOP
INSERT INTO demo
VALUES(counter,'Three');
END LOOP;
END;
1 row(s) inserted.
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} |
|---|