Oracle Database API for MongoDB - Part V

In this blogpost, we will see about how to transform the documents into Relational format to avoid the inconsistency and data duplication in the document format.
 
Really a JSON Collection is an abstraction over the actual storage. Similar to a Java, where we define an Interface and work against the Interface and then we can have the different implementation of the Interface, It is the same here with JSON collections.
 
 

 
 
Let’s say for this new project I really want to use JSON Collections and write it against JSON Collection API’s either using SQL or Mongo API or Simple Object Document Access (SODA) API commands, but then we can still choose to persist data as JSON on disk using JSON Collection tables or we can map it to relational table tables using JSON Relational Duality views or you can even change the storage on the go, one example would be to enhance the project start persisting the JSON data and once the application got stabilised and you want to enforce more integrity across the data and changes in one collection you want the changes to be visible across the entity then you can easily migrate the persistence of JSON documents to a JSON Relational Duality views using a tool called document migrator.
 
For this demo, we will create a sample collection and insert few documents into it from Mongo Shell supporting conference schedule, for four different presenters.
 
mdb_test2> //verify the collection, create above exists in Mongo Shell
 
mdb_test2> db.runCommand({listCollections:1,filter:{name:'CONF_SCHEDULE'}});
{
  ok: 1,
  cursor: {
    id: Long('0'),
    ns: 'mdb_test2.$cmd.listCollections',
    firstBatch: []
  }
}
mdb_test2> //MongoDB command to Create and Insert many rows into the collections.
 
mdb_test2> db.CONF_SCHEDULE.insertMany([
...     {
...         "_id" : 1
...         , "name" : "Beda"
...         , "schedule" :[
...             {
...                 "scheduleId" : 1
...                 , "sessionId" : 1
...                 , "name" : "JSON and SQL"
...                 , "location" : "Room 1"
...                 , "speakerId" : 1
...                 , "speaker" : "Tirthankar"
...             },
...             {
...                 "scheduleId" : 2
...                 , "sessionId" : 2
...                 , "name" : "PL/SQL or Javascript"
...                 , "location" : "Room 2"
...                 , "speakerId" : 1
...                 , "speaker" : "Tirthankar"
...             },
...             {
...                 "scheduleId" : 4
...                 , "sessionId" : 3
...                 , "name" : "Oracle on IPhone"
...                 , "location" : "Room 1"
...                 , "speakerId" : 2
...                 , "speaker" : "Jenny"
...             },
...             {
...                 "scheduleId" : 6
...                 , "sessionId" : 5
...                 , "name" : "MongoDB API Internals"
...                 , "location" : "Room 4"
...                 , "speakerId" : 4
...                 , "speaker" : "Julian"
...             }
...         ]
...     },
...     {
...         "_id" : 2
...         , "name" : "Hermann"
...         , "schedule" :[
...             {
...                 "scheduleId" : 3
...                 , "sessionId" : 2
...                 , "name" : "PL/SQL or Javascript"
...                 , "location" : "Room 2"
...                 , "speakerId" : 1
...                 , "speaker" : "Tirthankar"
...             },
...            {
...                 "scheduleId" : 7
...                 , "sessionId" : 5
...                 , "name" : "MongoDB API Internals"
...                 , "location" : "Room 4"
...                 , "speakerId" : 4
...                 , "speaker" : "Julian"
...             }
...         ]
...     },
...     {
...        "_id" : 4
...         , "name" : "Ranjan"
...         , "schedule" : []
...     },
...     {
...         "_id" : 3
...         , "name" : "Julian"
...         , "schedule" :[
...             {
...                 "scheduleId" : 5
...                 , "sessionId" : 4
...                 , "name" : "JSON Duality View"
...                 , "location" : "Room 3"
...                 , "speakerId" : 3
...                 , "speaker" : "Cetin"
...             }
...         ]
...     }
... ]);
{ acknowledged: true, insertedIds: { '0': 1, '1': 2, '2': 4, '3': 3 } }
mdb_test2>
 
mdb_test2> //MongoDB command to count the documents in Collections
 
mdb_test2> db.CONF_SCHEDULE.countDocuments();
4
mdb_test2> db.runCommand({listCollections:1,filter:{name:'CONF_SCHEDULE'}});
{
  ok: 1,
  cursor: {
    id: Long('0'),
    ns: 'mdb_test2.$cmd.listCollections',
    firstBatch: [ { name: 'CONF_SCHEDULE' } ]
  }
}
mdb_test2>
 
Now, we will connect to Oracle database and use an API (newly introduced in Oracle 23ai) called DBMS_JSON_DUALITY  got a method
 

• INFER_AND_GENERATE_SCHEMA – which can read a collection and generated the necessary tables, relationship, indexes, and the Duality views to support the move / migrate of data from JSON Collections to Duality views.
• IMPORT – which will read the collection and map the documents to the underlying relational tables, and this method does the actual movement of data from JSON collections to relational tables.
  

 
mdb-test2@FREEPDB1> declare
  2     l_sql clob ;
  3     l_inputs json;
  4  begin
  5     l_inputs := json(' {"tableNames" :["CONF_SCHEDULE"]
  6         , "useFlexFields" : false
  7         , "updatability" : true } ');
  8     l_sql := dbms_json_duality.infer_and_generate_schema(l_inputs);
  9
 10     dbms_output.put_line('------------------ DDL Scripts ------------------------');
 11     dbms_output.put_line(l_sql);
 12     execute immediate l_sql;
 13
 14     dbms_json_duality.import( table_name=> 'CONF_SCHEDULE'
 15         , view_name => 'CONF_SCHEDULE_DUALITY' ) ;
 16  end;
 17* /
------------------ DDL Scripts ------------------------
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE conf_schedule_root(
   "_id"  number  GENERATED BY DEFAULT ON NULL AS IDENTITY,
   name  varchar2(64)  /* UNIQUE */,
   PRIMARY KEY("_id")
)';
 
EXECUTE IMMEDIATE 'CREATE TABLE conf_schedule_schedule(
   name  varchar2(64),
   speaker  varchar2(64),
   location  varchar2(64),
   session_id  number,
   speaker_id  number,
   schedule_id  number  GENERATED BY DEFAULT ON NULL AS IDENTITY,
   "_id_conf_schedule_root"  number,
   PRIMARY KEY(schedule_id)
)';
 
EXECUTE IMMEDIATE 'ALTER TABLE conf_schedule_schedule
ADD CONSTRAINT fk_conf_schedule_schedule_to_conf_schedule_root FOREIGN KEY ("_id_conf_schedule_root") REFERENCES conf_schedule_root("_id")';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_conf_schedule_schedule_to_conf_schedule_root_index ON conf_schedule_schedule("_id_conf_schedule_root")';
 
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW CONF_SCHEDULE_DUALITY AS
conf_schedule_root @insert @update @delete
{
  "_id"
  name
  schedule: conf_schedule_schedule @insert @update @delete
  {
    name
    speaker
    location
    sessionId: session_id
    speakerId: speaker_id
    scheduleId: schedule_id
  }
}';
END;
 
 
 
PL/SQL procedure successfully completed.
 
This newly created table will have the necessary relational data created from JSON documents.
 
mdb-test2@FREEPDB1> select * from conf_schedule_root ;
 
       _id NAME
---------- -------------------------
         1 Beda
         2 Hermann
         4 Ranjan
         3 Julian
 

mdb-test2@FREEPDB1> select * from conf_schedule_schedule;

  

NAME                   SPEAKER     LOCATION   SESSION_ID SPEAKER_ID SCHEDULE_ID _id_conf_schedule_root

---------------------- ----------- ---------- ---------- ---------- ----------- ----------------------

JSON and SQL           Tirthankar  Room 1              1          1           1                      1

PL/SQL or Javascript   Tirthankar  Room 2              2          1           2                      1

Oracle on IPhone       Jenny       Room 1              3          2           4                      1

MongoDB API Internals  Julian      Room 4              5          4           6                      1

PL/SQL or Javascript   Tirthankar  Room 2              2          1           3                      2

MongoDB API Internals  Julian      Room 4              5          4           7                      2

JSON Duality View      Cetin       Room 3              4          3           5                      3

  

7 rows selected.

 
mdb-test2@FREEPDB1>
 
and the newly created duality view, will represent the relational data in a JSON Format (equivalent to JSON documents in Mongo Collections )
 
mdb-test2@FREEPDB1> select * from conf_schedule_duality ;
 
DATA
--------------------------------------------------------------------------------
{
  "_id" : 1,
  "_metadata" :
  {
    "etag" : "73245692311D9B3DA9F43F1CB4DC58B7",
    "asof" : "0000000000D7EB60"
  },
  "name" : "Beda",
  "schedule" :
  [
    {
      "name" : "JSON and SQL",
      "speaker" : "Tirthankar",
      "location" : "Room 1",
      "sessionId" : 1,
      "speakerId" : 1,
      "scheduleId" : 1
    },
    {
      "name" : "PL/SQL or Javascript",
      "speaker" : "Tirthankar",
      "location" : "Room 2",
      "sessionId" : 2,
      "speakerId" : 1,
      "scheduleId" : 2
    },
    {
      "name" : "Oracle on IPhone",
      "speaker" : "Jenny",
      "location" : "Room 1",
      "sessionId" : 3,
      "speakerId" : 2,
      "scheduleId" : 4
    },
    {
      "name" : "MongoDB API Internals",
      "speaker" : "Julian",
      "location" : "Room 4",
      "sessionId" : 5,
      "speakerId" : 4,
      "scheduleId" : 6
    }
  ]
}
 
{
  "_id" : 2,
  "_metadata" :
  {
    "etag" : "8BD6CDC7026AE0EC95027185DB6BEC33",
    "asof" : "0000000000D7EB60"
  },
  "name" : "Hermann",
  "schedule" :
  [
    {
      "name" : "PL/SQL or Javascript",
      "speaker" : "Tirthankar",
      "location" : "Room 2",
      "sessionId" : 2,
      "speakerId" : 1,
      "scheduleId" : 3
    },
    {
      "name" : "MongoDB API Internals",
      "speaker" : "Julian",
      "location" : "Room 4",
      "sessionId" : 5,
      "speakerId" : 4,
      "scheduleId" : 7
    }
  ]
}
 
{
  "_id" : 4,
  "_metadata" :
  {
    "etag" : "D4EFDFC1431205D3ACF7762BF5E3B2F9",
    "asof" : "0000000000D7EB60"
  },
  "name" : "Ranjan",
  "schedule" :
  [
  ]
}
 
{
  "_id" : 3,
  "_metadata" :
  {
    "etag" : "682F016CD60DA210AD39C20261E4D92A",
    "asof" : "0000000000D7EB60"
  },
  "name" : "Julian",
  "schedule" :
  [
    {
      "name" : "JSON Duality View",
      "speaker" : "Cetin",
      "location" : "Room 3",
      "sessionId" : 4,
      "speakerId" : 3,
      "scheduleId" : 5
    }
  ]
}
 
 
mdb-test2@FREEPDB1>
 
and this duality view is also accessible using MongoDB commands from Mongo Shell.
 
mdb_test2> db.CONF_SCHEDULE_DUALITY.find();
[
  {
    _id: 1,
    name: 'Beda',
    schedule: [
      {
        name: 'JSON and SQL',
        speaker: 'Tirthankar',
        location: 'Room 1',
        sessionId: 1,
        speakerId: 1,
        scheduleId: 1
      },
      {
        name: 'PL/SQL or Javascript',
        speaker: 'Tirthankar',
        location: 'Room 2',
        sessionId: 2,
        speakerId: 1,
        scheduleId: 2
      },
      {
        name: 'Oracle on IPhone',
        speaker: 'Jenny',
        location: 'Room 1',
        sessionId: 3,
        speakerId: 2,
        scheduleId: 4
      },
      {
        name: 'MongoDB API Internals',
        speaker: 'Julian',
        location: 'Room 4',
        sessionId: 5,
        speakerId: 4,
        scheduleId: 6
      }
    ],
    _metadata: {
      etag: Binary.createFromBase64('cyRWkjEdmz2p9D8ctNxYtw==', 0),
      asof: Binary.createFromBase64('AAAAAADX668=', 0)
    }
  },
  {
    _id: 2,
    name: 'Hermann',
    schedule: [
      {
        name: 'PL/SQL or Javascript',
        speaker: 'Tirthankar',
        location: 'Room 2',
        sessionId: 2,
        speakerId: 1,
        scheduleId: 3
      },
      {
        name: 'MongoDB API Internals',
        speaker: 'Julian',
        location: 'Room 4',
        sessionId: 5,
        speakerId: 4,
        scheduleId: 7
      }
    ],
    _metadata: {
      etag: Binary.createFromBase64('i9bNxwJq4OyVAnGF22vsMw==', 0),
      asof: Binary.createFromBase64('AAAAAADX668=', 0)
    }
  },
  {
    _id: 4,
    name: 'Ranjan',
    schedule: [],
    _metadata: {
      etag: Binary.createFromBase64('1O/fwUMSBdOs93Yr9eOy+Q==', 0),
      asof: Binary.createFromBase64('AAAAAADX668=', 0)
    }
  },
  {
    _id: 3,
    name: 'Julian',
    schedule: [
      {
        name: 'JSON Duality View',
        speaker: 'Cetin',
        location: 'Room 3',
        sessionId: 4,
        speakerId: 3,
        scheduleId: 5
      }
    ],
    _metadata: {
      etag: Binary.createFromBase64('aC8BbNYNohCtOcICYeTZKg==', 0),
      asof: Binary.createFromBase64('AAAAAADX668=', 0)
    }
  }
]
mdb_test2>
 
 
In the next blog post, we will see about how this Duality view will replace the JSON collection tables and brings the inter-operability between Oracle and mongo DB API commands.