Intro To JSON_OBJECT

View All Scripts Login to Run Script

Script Name Intro To JSON_OBJECT
Description JSON_OBJECT is one of Oracle's function to generate JSON output from an SQL query. It takes column names as arguments and combines them with column values in JSON key element pairs.
Area SQL General / JSON
Contributor Darryl Hurley
Created Tuesday January 10, 2017

Statement 1
```
DROP TABLE demo
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

CREATE TABLE demo 
( col1 NUMBER, 
  col2 VARCHAR2(30) )

Table created.

Statement 3

INSERT INTO demo 
VALUES(1,'One')

1 row(s) inserted.

Statement 4

INSERT INTO demo 
VALUES(2,'Two')

1 row(s) inserted.

Statement 5

INSERT INTO demo 
VALUES(3,NULL)

1 row(s) inserted.

Statement 6
```
COMMIT
```
```
Statement processed.
```

Statement 7

SELECT JSON_OBJECT ( 
    KEY 'col1' IS d.col1 FORMAT JSON,   -- FORMAT JSON is optional 
    KEY 'col2' IS d.col2,               --   if data comes from table 
    KEY 'col3' IS 'TEXT' FORMAT JSON,   --   but not otherwise 
    KEY 'col4' IS d.col1 ABSENT ON NULL -- NULL ON NULL is the default behavior 
    ) col1 
  FROM demo d 
  ORDER BY d.col1


COL1
{"col1":1,"col2":"One","col3":TEXT,"col4":1}
{"col1":2,"col2":"Two","col3":TEXT,"col4":2}
{"col1":3,"col3":TEXT,"col4":3}

3 rows selected.

COL1
{"col1":1,"col2":"One","col3":TEXT,"col4":1}
{"col1":2,"col2":"Two","col3":TEXT,"col4":2}
{"col1":3,"col3":TEXT,"col4":3}

Additional Information

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