Intro To JSON_ARRAY

View All Scripts Login to Run Script

Script Name Intro To JSON_ARRAY
Description An introduction to the JSON_ARRAY SQL function introduced in Oracle 12.2.
Area SQL General / JSON
Contributor Darryl Hurley
Created Saturday March 25, 2017

Statement 1

Parent Demo Table

CREATE TABLE parts 
( part_id   NUMBER NOT NULL PRIMARY KEY, 
  part_desc VARCHAR2(30) )

Table created.

Statement 2

Child Demo Table

CREATE TABLE part_price 
( part_id    NUMBER, 
  part_price NUMBER(10,2), 
  is_current VARCHAR2(1) )

Table created.

Statement 3

Create Demo Data

BEGIN
  INSERT INTO parts 
  VALUES(1,'Red Wrench');

  INSERT INTO parts 
  VALUES(2,'Other Red Wrench');

  INSERT INTO parts 
  VALUES(3,NULL);

  INSERT INTO part_price 
  VALUES(1,'10.50','Y');

  INSERT INTO part_price 
  VALUES(1,'11.50','N');

  INSERT INTO part_price 
  VALUES(2,'11.50','Y');

  INSERT INTO part_price 
  VALUES(2,'12.50','N');

END;

1 row(s) inserted.

Statement 4
Call JSON_ARRAY with two columns specified. Note that part desc is not included for PART_ID=3 because "ABSENT WHEN NULL" is the default behavior.
Simple JSON_ARRAY
```
SELECT JSON_ARRAY( part_id, 
                   part_desc ) 
  FROM parts
```
```
JSON_ARRAY(PART_ID,PART_DESC)
[1,"Red Wrench"]
[2,"Other Red Wrench"]
[3]

3 rows selected.
```

JSON_ARRAY(PART_ID,PART_DESC)
[1,"Red Wrench"]
[2,"Other Red Wrench"]
[3]

Statement 5

This JSON_ARRAY call includes a sub-query which gets a value from the PART_PRICE table. Note that the sub-query must return one and only one row otherwise the Oracle error "ORA-01427: single-row subquery returns more than one row" will be thrown.

Inline Subquery

SELECT JSON_ARRAY( part_id, 
                   part_desc, 
                   ( SELECT part_price 
                       FROM part_price 
                      WHERE part_id = p.part_id 
                        AND is_current = 'Y') ) 
  FROM parts p


JSON_ARRAY(PART_ID,PART_DESC,(SELECTPART_PRICEFROMPART_PRICEWHEREPART_ID=P.PART_IDANDIS_CURRENT='Y'))
[1,"Red Wrench",10.5]
[2,"Other Red Wrench",11.5]
[3]

3 rows selected.

JSON_ARRAY(PART_ID,PART_DESC,(SELECTPART_PRICEFROMPART_PRICEWHEREPART_ID=P.PART_IDANDIS_CURRENT='Y'))
[1,"Red Wrench",10.5]
[2,"Other Red Wrench",11.5]
[3]

Statement 6

Specify that the text 'null' be shown for NULL values.

NULL ON NULL

SELECT JSON_ARRAY( part_id, 
                   part_desc NULL ON NULL ) 
  FROM parts


JSON_ARRAY(PART_ID,PART_DESCNULLONNULL)
[1,"Red Wrench"]
[2,"Other Red Wrench"]
[3,null]

3 rows selected.

JSON_ARRAY(PART_ID,PART_DESCNULLONNULL)
[1,"Red Wrench"]
[2,"Other Red Wrench"]
[3,null]

Statement 7

Specify a RETURNING data type. In this case VARCHAR2(30).

Returning Clause

SELECT JSON_ARRAY( part_id, 
                   part_desc RETURNING VARCHAR2(30) ) 
  FROM parts


JSON_ARRAY(PART_ID,PART_DESCRETURNINGVARCHAR2(30))
[1,"Red Wrench"]
[2,"Other Red Wrench"]
[3]

3 rows selected.

Statement 8
Specify a RETURNING data type with a length unable to accomodate the full text.
RETURNING too small
```
SELECT JSON_ARRAY( part_id, 
                   part_desc RETURNING VARCHAR2(10) ) 
  FROM parts
```
```
ORA-40478: output value too large (maximum: 10)
```
More Details: https://docs.oracle.com/error-help/db/ora-40478

JSON_ARRAY(PART_ID,PART_DESCRETURNINGVARCHAR2(30))
[1,"Red Wrench"]
[2,"Other Red Wrench"]
[3]

Statement 9

Creates two JSON_ARRAYs based on values from 2 tables.

JSON_ARRAY across table join

SELECT JSON_ARRAY( p.part_id, 
                   part_desc ), 
       JSON_ARRAY( part_price, 
                   is_current) 
  FROM parts p, 
       part_price pp 
 WHERE p.part_id = pp.part_id


JSON_ARRAY(P.PART_ID,PART_DESC) JSON_ARRAY(PART_PRICE,IS_CURRENT)
[1,"Red Wrench"] [10.5,"Y"]
[1,"Red Wrench"] [11.5,"N"]
[2,"Other Red Wrench"] [11.5,"Y"]
[2,"Other Red Wrench"] [12.5,"N"]

4 rows selected.

JSON_ARRAY(P.PART_ID,PART_DESC)	JSON_ARRAY(PART_PRICE,IS_CURRENT)
[1,"Red Wrench"]	[10.5,"Y"]
[1,"Red Wrench"]	[11.5,"N"]
[2,"Other Red Wrench"]	[11.5,"Y"]
[2,"Other Red Wrench"]	[12.5,"N"]

Statement 10

One JSON_ARRAY call is embedded in the other across a table join.

Embedded JSON_ARRAY call

SELECT JSON_ARRAY( p.part_id, 
                   part_desc, 
                  JSON_ARRAY( part_price, 
                              is_current ) ) 
  FROM parts p, 
       part_price pp 
 WHERE p.part_id = pp.part_id


JSON_ARRAY(P.PART_ID,PART_DESC,JSON_ARRAY(PART_PRICE,IS_CURRENT))
[1,"Red Wrench",[10.5,"Y"]]
[1,"Red Wrench",[11.5,"N"]]
[2,"Other Red Wrench",[11.5,"Y"]]
[2,"Other Red Wrench",[12.5,"N"]]

4 rows selected.

JSON_ARRAY(P.PART_ID,PART_DESC,JSON_ARRAY(PART_PRICE,IS_CURRENT))
[1,"Red Wrench",[10.5,"Y"]]
[1,"Red Wrench",[11.5,"N"]]
[2,"Other Red Wrench",[11.5,"Y"]]
[2,"Other Red Wrench",[12.5,"N"]]

Statement 11

One JSON_ARRAY call is embedded within another across an outer table join. Thus a row for PART_ID=3 is included with an empty set returned from the PART_PRICE table.

Outer Join

SELECT JSON_ARRAY( p.part_id, 
                   part_desc, 
                  JSON_ARRAY( part_price, 
                              is_current ) ) 
  FROM parts p, 
       part_price pp 
 WHERE p.part_id = pp.part_id (+)


JSON_ARRAY(P.PART_ID,PART_DESC,JSON_ARRAY(PART_PRICE,IS_CURRENT))
[1,"Red Wrench",[10.5,"Y"]]
[1,"Red Wrench",[11.5,"N"]]
[2,"Other Red Wrench",[11.5,"Y"]]
[2,"Other Red Wrench",[12.5,"N"]]
[3,[]]

5 rows selected.

JSON_ARRAY(P.PART_ID,PART_DESC,JSON_ARRAY(PART_PRICE,IS_CURRENT))
[1,"Red Wrench",[10.5,"Y"]]
[1,"Red Wrench",[11.5,"N"]]
[2,"Other Red Wrench",[11.5,"Y"]]
[2,"Other Red Wrench",[12.5,"N"]]
[3,[]]

Additional Information

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