Extracting json data using oracle pl/sql

------------------------------Example 1: 
create table t( doc_id  int,
 doc_details varchar2(1000)
);

--loading json format data:

insert into t values (1 , '{"id":1, "name" : "Jeff"}' );
insert into t values (2 , '{"id":2, "name" : "Jane", "status":"Gold"}' );
insert into t values (3 , '{"id":3, "name" : "Jill", "status":["Important","Gold"]}' );
insert into t values (4 , '{"name" : "John", "status":"Silver"}' );
commit;


--extracting the status key as fields:

select json_value(doc_details, '$.status') from t;


-----------------------Example 2 :

/* sample json data:

{"jobs":[{"name":"Wash Car","notes":[{"title":"Address","text":"1 High Street"},{"title":"Warning","text":"Scaffolding Required"}],"occupants":[{"name":"Mr Smith","gender":"Male"},{"name":"Mrs Smith","gender":"Female"}]},
{"name":"Wash Car","notes":[{"title":"Address","text":"1 Another Street"}],"occupants":[{"name":"Mr Jones","gender":"Male"},{"name":"Mrs Jones","gender":"Female"}]}]}

*/

-- From above sample data, we are going to extract data & store into three different tables.

create table jobs (
  job_id int,
  jb     varchar2(20)
);

create table notes (
  job_id int,
  title  varchar2(20),
  text   varchar2(30)
);

create table occupants (
  job_id int,
  name   varchar2(20),
  gender varchar2(30)
);
       
       
--insert operation performs here..       
insert all
  when rnj = 1 then into jobs values (row_number, job_name)
  when rnnm = 1 then into occupants values (row_number, name, gender)
  when rnnt = 1 then into notes values (row_number, note_title, note_text)
 
----Query starts 
with json as
  (select
'{"jobs":[{"name":"Wash Car","notes":[{"title":"Address","text":"1 High Street"},{"title":"Warning","text":"Scaffolding Required"}],"occupants":[{"name":"Mr Smith","gender":"Male"},{"name":"Mrs Smith","gender":"Female"}]},
{"name":"Wash Car","notes":[{"title":"Address","text":"1 Another Street"}],"occupants":[{"name":"Mr Jones","gender":"Male"},{"name":"Mrs Jones","gender":"Female"}]}]}'
    doc
  from dual
  ), jobs as (
select /*+ no_merge */row_number, job_name, notes, names
from json_table (
  ( select doc from json ) ,
   '$.jobs[*]' null on error
   columns ( row_number for ordinality,
    job_name varchar2 ( 20 ) path '$.name',
    notes varchar2(1000) format json path '$.notes'
    ,
    names varchar2(1000) format json path '$.occupants'
  )
)
)
  select row_number, job_name,
         note_id, note_title, note_text,
         name_id, name, gender,
         row_number() over (partition by row_number order by row_number) rnj,
         row_number() over (partition by row_number, note_id order by row_number, name_id) rnnt,
         row_number() over (partition by row_number, name_id order by row_number, note_id) rnnm
  from   jobs,
         json_table(notes, '$[*]'
          columns
           note_id    for ordinality,
           note_title varchar2(30) path '$.title',
           note_text  varchar2(30) path '$.text'
         ),
         json_table(names, '$[*]'
          columns
           name_id    for ordinality,
           name varchar2(30) path '$.name',
           gender  varchar2(30) path '$.gender'
         );
--Query ends




-- check the results on these tables once fired above query ,

select * from jobs;

JOB_ID  JB       
1       Wash Car 
2       Wash Car

select * from occupants;

JOB_ID  NAME       GENDER 
1       Mr Smith   Male   
1       Mrs Smith  Female 
2       Mr Jones   Male   
2       Mrs Jones  Female 

select * from notes;

JOB_ID  TITLE    TEXT                 
1       Address  1 High Street       
1       Warning  Scaffolding Required 
2       Address  1 Another Street