ma_jobs           one-to-many ma_job_notes
ma_jobs           one-to-many ma_job_sections
ma_job_section    one-to-many ma_job_attributes
ma_job_attributes one-to-many ma_job_attribute_options
ma_job_attributes one-to-many ma_job_attr_validation_rules
ma_job_attributes one-to-many ma_job_attribute_dependencies
ma_job_attributes one-to-many ma_job_attribute_values

ma_jobs[1], ma_jobs[1].ma_job_notes[1]
            ma_jobs[1].ma_job_notes[2]
            ma_jobs[1].ma_job_notes[3]
            ma_jobs[1].ma_job_notes[n]
ma_jobs[2], ma_jobs[2].ma_job_notes[1]
            ma_jobs[2].ma_job_notes[2]
            ma_jobs[2].ma_job_notes[n]
ma_jobs[n], ma_jobs[n].ma_job_notes[1]
            ma_jobs[n].ma_job_notes[2]
            ma_jobs[n].ma_job_notes[3]
            ma_jobs[n].ma_job_notes[n]

DROP SEQUENCE jobs_nb_seq;
DROP SEQUENCE notes_nb_seq;
DROP SEQUENCE occupants_nb_seq;
DROP SEQUENCE occupant_attributes_nb_seq;

CREATE sequence jobs_nb_seq                START WITH 100;
CREATE sequence notes_nb_seq               START WITH 200;
CREATE sequence occupants_nb_seq           START WITH 300;
CREATE sequence occupant_attributes_nb_seq START WITH 400;

DROP TABLE jobs_nb;
DROP TABLE notes_nb;
DROP TABLE occupants_nb;
DROP TABLE occupant_attributes_nb;
DROP TABLE docs_nb;

CREATE TABLE docs_nb( 
  doc VARCHAR2(4000) CHECK(doc IS JSON));
  
CREATE TABLE jobs_nb ( 
  id   INT            NOT NULL, 
  name VARCHAR2(20)   NOT NULL);
  
CREATE TABLE notes_nb ( 
  id     INT          NOT NULL,
  job_id INT          NOT NULL, 
  title  VARCHAR2(20) NOT NULL, 
  text   VARCHAR2(30) NOT NULL);

CREATE TABLE occupants_nb (
  id     INT          NOT NULL,
  job_id INT          NOT NULL,
  name   VARCHAR2(20) NOT NULL,
  gender VARCHAR2(30) NOT NULL);  
  
CREATE TABLE occupant_attributes_nb (
  id          INT          NOT NULL,
  occupant_id INT          NOT NULL,
  code        VARCHAR2(4)  NOT NULL,
  name        VARCHAR2(20) NOT NULL,
  value       VARCHAR2(30) NOT NULL);   
  
INSERT INTO docs_nb 
VALUES (
'{
  "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": "Ms Smith",
          "gender": "Female"
        }
      ]
    },
    {
      "name": "Wash Car 2",
      "notes": [
        {
          "title": "Address",
          "text": "2 High Street"
        },
        {
          "title": "Warning",
          "text": "Scaffolding Required 2"
        },
        {
          "title": "Another note",
          "text": "This is another note 2"
        }
      ],
      "occupants": [
        {
          "name": "Mr Smith2",
          "gender": "Male2",
          "attributes": [
            {
              "code": "EC",
              "name": "Eye Colour",
              "value": "Brown"
            },
            {
              "code": "HT",
              "name": "Height",
              "value": "5"
            },
            {
              "code": "HC",
              "name": "Hair Colour",
              "value": "Blonde"
            }
          ]
        },
        {
          "name": "Mrs Smith2",
          "gender": "Female2"
        }
      ]
    }
  ]
}');
 
DECLARE

  TYPE t_jobs_rec IS RECORD (
    name      jobs_nb.name%TYPE,
    notes     CLOB,
    occupants CLOB);

  TYPE t_jobs_tab IS TABLE OF t_jobs_rec INDEX BY BINARY_INTEGER;
  
  l_jobs_tab         t_jobs_tab;
  l_job_ids_tab      dbms_sql.number_table;
  l_occupant_ids_tab dbms_sql.number_table;

BEGIN

  -- Bulk Collect each job into PL/SQL jobs tabl
  WITH jobs AS (
    SELECT name,
           notes,
           occupants
    FROM   JSON_TABLE((SELECT doc FROM docs_nb), '$.jobs[*]' NULL ON ERROR 
             COLUMNS(name      VARCHAR2(20)   PATH '$.name', 
                     notes     VARCHAR2(1000) FORMAT JSON PATH '$.notes',
                     occupants VARCHAR2(1000) FORMAT JSON PATH '$.occupants'))
  )
  SELECT name,
         notes,
         occupants 
  BULK COLLECT INTO l_jobs_tab 
  FROM jobs;

  -- Insert each job in PL/SQL jobs table into database jobs_nb table
  -- l_job_ids_tab used to store sequence number generated jobs_nb.id primary key
  FORALL i IN 1 .. l_jobs_tab.COUNT
    INSERT INTO jobs_nb (
      id,
      name)
    VALUES (
      jobs_nb_seq.NEXTVAL,
      l_jobs_tab(i).name) RETURNING id BULK COLLECT INTO l_job_ids_tab;
      
  -- Insert each occupant in PL/SQL jobs table into database occupants_nb table
  -- l_job_ids_tab used to retreive sequence number generated jobs_nb.id for occupants_nb.job_id foreign key
  FORALL i IN 1 .. l_jobs_tab.COUNT
    INSERT INTO occupants_nb (
      id,
      job_id,
      name,
      gender)
    WITH json AS (
      SELECT l_jobs_tab(i).occupants j 
      FROM   dual
    )
    SELECT occupants_nb_seq.NEXTVAL,
           l_job_ids_tab(i),
           j.name,
           j.gender
    FROM   json,
           JSON_TABLE(j,'$[*]' NULL ON ERROR
             COLUMNS(name   VARCHAR2(100) PATH '$.name',
                     gender VARCHAR2(100) PATH '$.gender')) j;
                     
  -- Insert each occupant attribute in PL/SQL jobs table into database occupant_attributes_nb table
  -- TO DO
  -- How would I populate the l_occupant_ids_tab for this insert         
    
  -- Insert each note in PL/SQL jobs table into database notes_nb table
  -- l_job_ids_tab used to retreive sequence number generated jobs_nb.id for notes_nb.job_id foreign key
  FORALL i IN 1 .. l_jobs_tab.COUNT
    INSERT INTO notes_nb(
      id,
      job_id,
      title,
      text)
    WITH json AS ( 
      SELECT l_jobs_tab(i).notes j 
      FROM   dual
    )
    SELECT notes_nb_seq.NEXTVAL,
           l_job_ids_tab(i),
           j.title,
           j.text
    FROM   json,
           JSON_TABLE(j,'$[*]' NULL ON ERROR
             COLUMNS(title VARCHAR2(100) PATH '$.title', 
                     text  VARCHAR2(100) PATH '$.text')) j;
                     
END;
/

SELECT * FROM jobs_nb
/

SELECT * FROM notes_nb
/

SELECT * FROM occupants_nb
/

SELECT * FROM occupant_attributes_nb
/

DECLARE

  CURSOR c_jobs(p_index IN PLS_INTEGER) IS
    SELECT name
    FROM   JSON_TABLE((SELECT doc FROM docs_nb), '$.jobs['||p_index||']' ERROR ON ERROR 
              COLUMNS(name VARCHAR2(20)PATH '$.name'));
              
  l_jobs c_jobs%ROWTYPE; 
  l_jobs_index PLS_INTEGER := 0;

BEGIN

  LOOP
 
    OPEN c_jobs(0);
    FETCH c_jobs INTO l_jobs;
    IF c_jobs%FOUND THEN
      CLOSE c_jobs;
    ELSE
      CLOSE c_jobs;
      EXIT;
    END IF;

    INSERT INTO jobs_nb (
      id,
      name)
    VALUES (
      jobs_nb_seq.NEXTVAL,
      l_jobs.name);
      
    l_jobs_index := l_jobs_index + 1;  
  
  END LOOP;


END;
/