Oracle Database API for MongoDB - Part VII

One of many nifty enhancement introduced in Oracle 23ai(23.6) is JSON Collection views.
 
A JSON Collection view simply maps JSON documents to underlying relational data – there are two flavours.
 

• Duality View – can be directly updatable, which mean we can directly insert, update, or delete the documents, which in-turn modify the data in the underlying relation table.

• JSON collection view – that is not directly updatable, equivalent to Duality view in a read only format. Using a document API, we can only query its contents, however we can update the supported documents indirectly by modify the data in the underlying relation tables.

 
We will explain with a simple test case.
 
demo@ATP23ai> CREATE OR REPLACE JSON relational duality VIEW EMPVIEW_JDVW AS
  2  SELECT JSON {'_id'         : employee_id,
  3                last_name,
  4                'contactInfo' : {email, phone_number},
  5                hire_date,
  6                salary}
  7  FROM employees with update;
 
View created.
 
demo@ATP23ai>
demo@ATP23ai> CREATE OR REPLACE JSON COLLECTION VIEW EMPVIEW_JCVW AS
  2  SELECT JSON {'_id'         : employee_id,
  3                last_name,
  4                'contactInfo' : {email, phone_number},
  5                hire_date,
  6                salary} data
  7  FROM employees;
 
View created.
 
So we created two views – one Duality view and one JSON Collection view.
 
Updates to the duality view are possible.
 
demo@ATP23ai> update EMPVIEW_JDVW v
  2  set data = json('{
  3    "_id" : 100,
  4    "last_name" : "King",
  5    "contactInfo" :
  6    {
  7      "email" : "SKING",
  8      "phone_number" : "1.515.555.0100"
  9    },
 10    "hire_date" : "2013-06-17T00:00:00",
 11    "salary" : 24000
 12  }')
 13  where v.data."_id".number() = 100;
 
1 row updated.
 
But not on the JSON Collection view.
 
demo@ATP23ai> update EMPVIEW_JCVW v
  2  set data = json('{
  3    "_id" : 100,
  4    "last_name" : "King",
  5    "contactInfo" :
  6    {
  7      "email" : "SKING",
  8      "phone_number" : "1.515.555.0100"
  9    },
 10    "hire_date" : "2013-06-17T00:00:00",
 11    "salary" : 24000
 12  }')
 13  where v.data."_id".number() = 100;
where v.data."_id".number() = 100
      *
ERROR at line 13:
ORA-01733: virtual column not allowed here
Help: https://docs.oracle.com/error-help/db/ora-01733/
 
This JSON Collection view sounds like a traditional view.
 
demo@ATP23ai> CREATE OR REPLACE VIEW EMPVIEW_VW AS
  2  SELECT JSON {'_id'         : employee_id,
  3                last_name,
  4                'contactInfo' : {email, phone_number},
  5                hire_date,
  6                salary} data
  7  FROM employees;
 
View created.
 
demo@ATP23ai> update EMPVIEW_VW v
  2  set data = json('{
  3    "_id" : 100,
  4    "last_name" : "King",
  5    "contactInfo" :
  6    {
  7      "email" : "SKING",
  8      "phone_number" : "1.515.555.0100"
  9    },
 10    "hire_date" : "2013-06-17T00:00:00",
 11    "salary" : 24000
 12  }')
 13  where v.data."_id".number() = 100;
where v.data."_id".number() = 100
      *
ERROR at line 13:
ORA-01733: virtual column not allowed here
Help: https://docs.oracle.com/error-help/db/ora-01733/
 
However, the key difference between JSON collection view and the traditional view was, both JSON collection view and Duality view are accessible from either Mongo-DB commands or SQL command where are traditional view is accessible only from SQL commands.
 
Connection to the database from Mongoshell list the JSON Collection view and Duality view but not the traditional views.
 
C:\Users\Rajeshwaran>mongosh --tls --tlsAllowInvalidCertificates "mongodb://demo@G26BE7C92912CDB-ATP23AI.adb.us-ashburn-1.oraclecloudapps.com:27017/demo?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true"
Enter password: *************
Current Mongosh Log ID: 675702044d99615e2886b01c
Connecting to:          mongodb://<credentials>@G26BE7C92912CDB-ATP23AI.adb.us-ashburn-1.oraclecloudapps.com:27017/demo?authMechanism=PLAIN&authSource=%24external&ssl=true&retryWrites=false&loadBalanced=true&tls=true&tlsAllowInvalidCertificates=true&appName=mongosh+2.3.2
Using MongoDB:          4.2.14
Using Mongosh:          2.3.2
mongosh 2.3.4 is available for download: https://www.mongodb.com/try/download/shell
 
For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/
 
demo>
 
demo> show collections
EMP_DV
EMP2
EMPLOYEE
EMPVIEW_JCVW
EMPVIEW_JDVW
PERSON_DV
RAW_SRCH_VRTL_QUERY_MOD_TEST
SCHEDULE_DAY_EXTENDED_DV
STUDENTS_DV
TEST_DV1
X
XBASE
 
Also document API access possible on JSON Collection view and Duality view
 
demo> db.EMPVIEW_JCVW.find({"_id":100});
[
  {
    _id: 100,
    last_name: 'King',
    contactInfo: { email: 'SKING', phone_number: '1.515.555.0100' },
    hire_date: ISODate('2013-06-17T00:00:00.000Z'),
    salary: 24000
  }
]
demo>
 
demo> db.EMPVIEW_JDVW.find({"_id":100});
[
  {
    _id: 100,
    last_name: 'King',
    contactInfo: { email: 'SKING', phone_number: '1.515.555.0100' },
    hire_date: ISODate('2013-06-17T00:00:00.000Z'),
    salary: 24000,
    _metadata: {
      etag: Binary.createFromBase64('h/wZRrIKVHohCC7v+umKvg==', 0),
      asof: Binary.createFromBase64('AAAmA4rj1q4=', 0)
    }
  }
]
 
But not on traditional views…
 
demo> db.EMPVIEW_VW.find({"_id":100});
 
demo> db.EMPVIEW_VW.find();
 
 

Oracle Database API for MongoDB - Part VI

In the pervious blogpost we saw about how to transform the documents from Mongo collections into Relational format, all that can be done using DBMS_JSON_DUALITY a new API introduced in Oracle database 23ai. In this blogpost we will see about how to safely remove the JSON collection from the database and bring the duality view inter-operability between SQL and MongoDB commands.
 
First let’s drop the JSON Collection and recreate the Duality View (DV) in the same name as collections.
 
 
mdb-test2@FREEPDB1> drop table CONF_SCHEDULE purge;
 
Table CONF_SCHEDULE dropped.
 
mdb-test2@FREEPDB1>
mdb-test2@FREEPDB1> CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW CONF_SCHEDULE AS
  2  conf_schedule_root @insert @update @delete
  3  {
  4    "_id"
  5    name
  6    schedule: conf_schedule_schedule @insert @update @delete
  7    {
  8      name
  9      speaker
 10      location
 11      sessionId: session_id
 12      speakerId: speaker_id
 13      scheduleId: schedule_id
 14    }
 15* } ;
 
View CONF_SCHEDULE created.
 
 
Then REST enable the DV like this
 
mdb-test2@FREEPDB1> declare
  2    pragma autonomous_transaction;
  3  begin
  4      ords.enable_object(p_enabled => TRUE,
  5                         p_schema => user,
  6                         p_object => 'CONF_SCHEDULE',
  7                         p_object_type => 'VIEW',
  8                         p_object_alias => 'CONF_SCHEDULE',
  9                         p_auto_rest_auth => FALSE);
 10
 11      commit;
 12  end;
 13* /
 
PL/SQL procedure successfully completed.
 
And verify if the DV is accessible over the API end points, doing a GET request like this.
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/DECS/VSCODE-DOCS/git
$ curl --request GET \
  --url http://localhost:8080/ords/mdb_test2/CONF_SCHEDULE/1
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   843    0   843    0     0    652      0 --:--:--  0:00:01 --:--:--   652{"_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":"73245692311D9B3DA9F43F1CB4DC58B7","asof":"0000000000D80514"},"links":[{"rel":"self","href":"http://localhost:8080/ords/mdb_test2/CONF_SCHEDULE/1"},{"rel":"describedby","href":"http://localhost:8080/ords/mdb_test2/metadata-catalog/CONF_SCHEDULE/item"},{"rel":"collection","href":"http://localhost:8080/ords/mdb_test2/CONF_SCHEDULE/"}]}
 
Similarly, we can update the documents over API call like this.
 
mdb-test2@FREEPDB1> select * from conf_schedule_root where "_id" = 1;
 
       _id NAME
---------- -------------------------
         1 Beda
 
mdb-test2@FREEPDB1> select name,speaker,location,session_id,speaker_id,schedule_id
  2  from conf_schedule_schedule
  3* where "_id_conf_schedule_root" = 1;
 
NAME                      SPEAKER                   LOCATION   SESSION_ID SPEAKER_ID SCHEDULE_ID
------------------------- ------------------------- ---------- ---------- ---------- -----------
JSON and SQL              Tirthankar                Room 1              1          1           1
PL/SQL or Javascript      Tirthankar                Room 2              2          1           2
Oracle on IPhone          Jenny                     Room 1              3          2           4
MongoDB API Internals     Julian                    Room 4              5          4           6
 
mdb-test2@FREEPDB1>
mdb-test2@FREEPDB1>
 
Rajeshwaran@rajeyaba-3WH3DK3 MINGW64 /c/DECS/VSCODE-DOCS/git
$ curl --request PUT \
  --url http://localhost:8080/ords/mdb_test2/CONF_SCHEDULE/1 \
  --header 'Content-Type: application/json' \
  --data '{
        "_id": 1,
        "name": "Beda2",
        "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": "Tkyte",
                        "location": "Room 4",
                        "sessionId": 5,
                        "speakerId": 4,
                        "scheduleId": 6
                }
        ]
}'
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  1488    0   843  100   645   2401   1837 --:--:-- --:--:-- --:--:--  4239{"_id":1,"name":"Beda2","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":"Tkyte","location":"Room 4","sessionId":5,"speakerId":4,"scheduleId":6}],"_metadata":{"etag":"2A83625487FC53369D5B4C80B3FB5895","asof":"0000000000D80778"},"links":[{"rel":"self","href":"http://localhost:8080/ords/mdb_test2/CONF_SCHEDULE/1"},{"rel":"describedby","href":"http://localhost:8080/ords/mdb_test2/metadata-catalog/CONF_SCHEDULE/item"},{"rel":"collection","href":"http://localhost:8080/ords/mdb_test2/CONF_SCHEDULE/"}]}
 
The highlighted parts of the payload were the changes introduced as part of the API call to see those changes back into the relational tables.
 
After the API call, the data in the relation table looks like this
 
mdb-test2@FREEPDB1> select * from conf_schedule_root where "_id" = 1;
 
       _id NAME
---------- -------------------------
         1 Beda2
 
mdb-test2@FREEPDB1> select name,speaker,location,session_id,speaker_id,schedule_id
  2  from conf_schedule_schedule
  3* where "_id_conf_schedule_root" = 1;
 
NAME                      SPEAKER                   LOCATION   SESSION_ID SPEAKER_ID SCHEDULE_ID
------------------------- ------------------------- ---------- ---------- ---------- -----------
JSON and SQL              Tirthankar                Room 1              1          1           1
PL/SQL or Javascript      Tirthankar                Room 2              2          1           2
Oracle on IPhone          Jenny                     Room 1              3          2           4
MongoDB API Internals     Tkyte                     Room 4              5          4           6
 
  
Now let’s do some changes to the DV (aka JSON collection tables) from Mongo Shell either using MongoDB commands or SQL Commands.
 
mdb_test2> db.CONF_SCHEDULE.find({_id:1});
[
  {
    _id: 1,
    name: 'Beda2',
    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: 'Tkyte',
        location: 'Room 4',
        sessionId: 5,
        speakerId: 4,
        scheduleId: 6
      }
    ],
    _metadata: {
      etag: Binary.createFromBase64('KoNiVIf8UzadW0yAs/tYlQ==', 0),
      asof: Binary.createFromBase64('AAAAAADYDlk=', 0)
    }
  }
]
 
mdb_test2> db.aggregate([{$sql:` update CONF_SCHEDULE cs
...             set cs.data = json_transform( cs.data,
...             PREPEND '$.schedule' =  json('{ "name": "Optimizer Fundamentals",
...                     "speaker": "Nigel" ,
...                     "location": "Room 17"}') )
...             where cs.DATA."_id".number() = 1 `}]);
[ { result: 1 } ]
mdb_test2>
mdb_test2> db.CONF_SCHEDULE.find({_id:1});
[
  {
    _id: 1,
    name: 'Beda2',
    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: 'Tkyte',
        location: 'Room 4',
        sessionId: 5,
        speakerId: 4,
        scheduleId: 6
      },
      {
        name: 'Optimizer Fundamentals',
        speaker: 'Nigel',
        location: 'Room 17',
        sessionId: null,
        speakerId: null,
        scheduleId: 8
      }
    ],
    _metadata: {
      etag: Binary.createFromBase64('GDXomloGR4q9Dv0nBqVG/w==', 0),
      asof: Binary.createFromBase64('AAAAAADYEME=', 0)
    }
  }
]
mdb_test2>
 
Highlighted above are the changes introduced as part of recent execution.
 
By this way we can start using Duality view as a replacement for Mongo Collections and can be accessible from all over the place as REST API’s or MongoDB command from Mongo shell or SQL commands from Oracle clients.
 

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.