SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query for dynamically nested JSON (with duplicate field names)

AllThingsOrclMay 25 2018 — edited Jun 6 2018

I have sample JSON as below, please note how many times "listOfItems" tag appears is dynamic, it can be just one or many children's within child's and so on. JSON's are stored in a CLOB column, this is just one sample JSON to help understand its structure. The nested levels is dynamic and vary from one JSON to another. The table will have many rows with varying nested levels. I should be able to search for a field and its value across all rows to figure out the matching rows or the rows that have the element and its value per input.

CREATE TABLE json_test (

json_id INTEGER GENERATED ALWAYS AS IDENTITY,

json CLOB,

CONSTRAINT ensure_json CHECK (json IS JSON )

);

INSERT INTO json_test (json) VALUES ('{"id":1000,"version":1,"itemnum":"123456","type":"Y","itemName":"somename1","listOfItems":[{"id":2000,"version":2,"itemnum":"234567","type":"Y","itemName":"somename2","listOfItems":[{"id":3000,"version":3,"itemnum":"345678","type":"Y","itemName":"somename3","listOfItems":[{"id":4000,"version":4,"itemnum":"456789","type":"N","itemName":"somename2","listOfItems":[{"id":5000,"version":5,"itemnum":"567890","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[] }]}]}]}]}');

--in order to avoid below error, using script block to insert long JSON

--SQL Error: ORA-01704: string literal too long

declare

lv_json clob ;

begin

lv_json:='{"id":1000,"version":1,"itemnum":"123456","type":"Y","itemName":"somename1","listOfItems":[{"id":2000,"version":2,"itemnum":"234567","type":"Y","itemName":"somename2","listOfItems":[{"id":3000,"version":3,"itemnum":"345678","type":"Y","itemName":"somename3","listOfItems":[{"id":4000,"version":4,"itemnum":"456789","type":"N","itemName":"somename2","listOfItems":[{"id":5000,"version":5,"itemnum":"567890","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[]},{"id":6000,"version":6,"itemnum":"678901","type":"N","itemName":"somename3","listOfItems":[] }]}]}]}]}';

INSERT INTO json_test (json) VALUES (lv_json);

end;

Oracle version is - Oracle Database 12c (12.1.0.2.0) .

Appreciate any inputs/suggestions.

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 4 2018

Added on May 25 2018

16 comments

4,158 views