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  

MongoDB Installation on Ubuntu

Installation of Mongodb in Ubuntu(16.04):-

Step 1 : Import the public key used by the package management system.

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 2930ADAE8CAF5059EE73BB4B58712A2291FA4AD5

Step 2 :  Create a list file for MongoDB.

echo "deb [ arch=amd64,arm64 ] https://repo.mongodb.org/apt/ubuntu xenial/mongodb-org/3.6 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-3.6.list

Step 3 : Reload local package database.

sudo apt-get update

Step 4 : Install the MongoDB packages.

sudo apt-get install -y mongodb-org

Step 5 : Install a specific release of MongoDB.

sudo apt-get install -y mongodb-org=3.6.5 mongodb-org-server=3.6.5 mongodb-org-shell=3.6.5 mongodb-org-mongos=3.6.5 mongodb-org-tools=3.6.5

Note:-

Data files:- The MongoDB instance stores its data files in /var/lib/mongodb
Log Files:-  its log files in /var/log/mongodb
To change Data/log file locations:-You can specify alternate log
and data file directories in /etc/mongod.conf. 

Installation completed successfully.

1. To Start mongodb:

sudo service mongod start

2. To Verify that MongoDB has started successfully:

cd /var/log/mongodb/mongod.log

3. To Stop MongoDB:

sudo service mongod stop

4. To Restart MongoDB:

sudo service mongod restart

5. To Begin using MongoDB:

mongo --host 127.0.0.1 --port 27017