Parent Demo Table
CREATE TABLE parts
( part_id NUMBER NOT NULL PRIMARY KEY,
part_desc VARCHAR2(30) )
Table created.
Child Demo Table
CREATE TABLE part_price
( part_id NUMBER,
part_price NUMBER(10,2),
is_current VARCHAR2(1) )
Table created.
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.
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] |
|---|
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] |
|---|
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] |
|---|
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] |
|---|
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 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"] |
|---|
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"]] |
|---|
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,[]] |
|---|