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.