Quick Start Guide for MongoDB Migrations

Moving from MongoDB to Oracle is easier than you think. You can continue using your existing Mongo tools, and often your application runs by simply changing the connection string. This Quick Start Guide for MongoDB Migrations shows you how. You’ll learn to create an Oracle Autonomous Database, connect your Mongo tools, migrate your data, and use SQL to query your data. Let’s get started.

Table of Contents

 

Note: If you already have an Autonomous Database setup for either JSON or transaction processing workloads, you can skip the Create your Oracle Autonomous Database step. Jump to retrieve your public access URL  

Create your Oracle Autonomous Database

  1. Login to Oracle Cloud Infrastructure (OCI) account for your user and tenancy. If you do not have an OCI account, sign-up for a free OCI account. For more information, see the Oracle Always Free Resources page.

    An example of the OCI console home page. This page varies from user to user based on the most recent tasks performed.

  2. Navigate to the Autonomous Database page and select the Create Autonomous Database button.

    An example of Autonomous Database page. This page will vary over time based on Autonomous Database resources used in the tenancy.

  3. In the Display name field, enter AJD1.
  4. In the Database name field, enter AJD1. The Database name must be unique for your tenancy.
  5. Accept the default value for Compartment.
  6. Select the Workload type as either JSON or Transaction Processing.
  7. Select the Choose database version as 23ai. Any supported version will work, but 23ai is preferred.

    An example of the first page of options for creating an Autonomous Database.

  8. Set the Password field. This password is used to connect via your Mongo tools.
  9. Re-enter the password into the Confirm password field.
  10. Select the Access type as Secure access for allowed IPs and VCNs only.
  11. Select the Add my IP address to IP value slider. The slider is disabled by default. When you enable it, it adds your current IP access to the Access control rule.
  12. Select the Create button.

    An example of the second page of options for creating an Autonomous Database.

Retrieve your public access URL

  1. Navigate to the Autonomous Database page.
  2. Select the link on the Display name for your Autonomous Database.

    An example showing the link that includes the Display name of the Autonomous Database. Select this link to open that database's details.

  3. Select Tool configuration from the menu tabs.

    An example showing the Tool configuration tab in the UI.

  4. Navigate (scroll) in the database details to the section on MongoDB API. Select the Copy link for the Public access URL. Save this copied URL to a text editor.

    An example of selecting the Copy link for the public access URL information for your database.

Connect with Mongo Compass

  1. Open Mongo Compass and select the Add new connection button.

    An example of adding a new connection in the Mongo Compass tool.

  2. Copy the public access URL that you retrieved and saved in the previous section into the URI field in Mongo Compass’ New Connection window. Edit the copied URL, replace the [user:password@] with ADMIN:<your database’s ADMIN password>@ in the connection string.

    An example of adding connection information in the Mongo Compass tool.

  3. In the Name field, enter AJD1.
  4. Select the Save & Connect button.
  5. Your connection will be saved and a connection will be established with your Oracle Database.

    An example of a connected Oracle Database in the Mongo Compass tool.

  6. You can create a database and import your collection(s). For test JSON data sets, see the Oracle Github repository.

    An example of using the Mongo Compass tool to navigate within your Oracle Database.

Connect with Mongo Shell

  1. From a terminal, command line, or shell, run mongosh with the public access URL. Replace the [user:password@] with ADMIN:<your database’s ADMIN password>@ in the connection string. Replace [user] as needed for your Oracle Database.

    mongosh 'mongodb://<your-oracle-OCID-and-DB-URL>.oraclecloudapps.com:27017/[user]?
authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'

      An example of connecting using the Windows Powershell and the Mongo Shell (mongosh) to connect to your Oracle Database.  

  2. If you do not want to include the password, you can use the following command from a terminal, command line, or shell, run mongosh with the public access URL. Replace [user] as needed for your Oracle Database.

    mongosh -u ADMIN 'mongodb://<your-oracle-OCID-and-DB-URL>.oraclecloudapps.com:27017/[user]?
authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'

      An example of connecting using macOS and the Mongo Shell (mongosh) to connect to your Oracle Database.  

Migrate your data using Mongo Compass

  1. Open Mongo Compass and connect to the database to export. If you want to use the data set used in these steps, see Sample AirBnB Listings Dataset for more information.

    An example of selecting a collection in Mongo Compass.

  2. Open the collection you want to export, and select the EXPORT DATA button and the Export the full collection menu item.

    An example of selecting the EXPORT DATA button followed by the Export the full collection menu item.

  3. In the Export dialog window, select the file name and location for your .json file and then select the the Export… button.

    An example of exporting your collection to a .json file in Mongo Compass.

  4. The export processes, and it shows you how many documents were written into your .json file.

    An example of a successful export of 5,555 documents from a collection using Mongo Compass.

  5. Using Mongo Compass, connect to your Oracle Database. Select the + (Create database) icon.

    An example of connecting to your Oracle Database using Mongo Compass. The connection should already exist in Mongo Compass. If it doesn't, repeat the Connect with the Mongo Compass tool steps previously shown. Then creating a new database in Mongo Compass.

  6. In the Create Database dialog window, enter a Database Name and a Collection Name and then select the Create Database button.

    An example of creating a new database and a new collection in your Oracle Database using Mongo Compass.

  7. From the newly creating collection, select the Import Data button.

    An example of selecting the Import Data button.

  8. Select the file in the file location where you saved the database export .json file, and then select the Import button.

    An example of importing your export .json file into a new collection using Mongo Compass.

  9. Once the import completes, it shows you how many documents were imported into your new collection.

    An example of a successful import of 5,555 documents into the collection using Mongo Compass.

  10. Login to Oracle Cloud Infrastructure (OCI) account for your user and tenancy. Navigate to the Autonomous Database page and select the link in Display Name for your Oracle Database.

    An example of login into the OCI console and getting to your Autonomous Database resources.

  11. Select the Tool configuration tab and the Database actions Copy button.

    An example of selecting Tool configuration tab and Database actions Copy button for the public access URL for Database actions.

  12. In your browser, paste the Database actions public access URL. Select the SQL tool and then select the Open button.

    An example of selecting and opening the SQL tool in Database actions.

  13. In the SQL Navigator, select the schema.

    An example of selecting a schema in the Database actions SQL tool.

  14. Run this query for the collection. Your count should match your expected value.

    SELECT COUNT(*) FROM MIGRATED_AIRBNB.LISTINGSANDREVIEWS;

      An example of the results of running the SQL query on the imported data.  

Create a view for data analysis

With your data stored in your Oracle Autonomous Database, you can create SQL views on top of your JSON collections which enables relational-style SQL queries on complex SQL-JSON views and hierarchies. Beyond this simple example, using the power of Oracle Database 23ai you can make your JSON data “AI ready” for use with new Oracle AI features such as AI Vector Search, RAG, JSON Relational Duality, and Select AI.

  1. Login to your database and go to the Database actions SQL tool.
  2. Select the schema (MIGRATED_AIRBNB) for your data.

    An example of logging into Database actions SQL tool for your database and selecting the MIGRATED_AIRBNB schema.

  3. Create a SQL view (AIRBNB_LISTINGS_VIEW) using JSON_VALUE to define columns.

    -- create the Oracle SQL view on top of the JSON collection
CREATE OR REPLACE VIEW MIGRATED_AIRBNB.AIRBNB_LISTINGS_VIEW AS
SELECT json_value(data, '$._id' RETURNING NUMBER) AS listing_id,
       json_value(data, '$.name' RETURNING VARCHAR2(200)) AS name,
       json_value(data, '$.price' RETURNING NUMBER) AS price,
       json_value(data, '$.address.government_area' RETURNING VARCHAR2(100)) AS city,
       json_value(data, '$.room_type' RETURNING VARCHAR2(50)) AS room_type,
       json_value(data, '$.availability.availability_365' RETURNING NUMBER) AS available_days
FROM MIGRATED_AIRBNB.LISTINGSANDREVIEWS;

      An example of creating the AIRBNB_LISTINGS_VIEW in the MIGRATED_AIRBNB schema.  

  4. Test your SQL view.

    --query the SQL view
select * from migrated_airbnb.airbnb_listings_view;

      An example of the results from the a SELECT * query of your new view.  

  5. Calculate the # of listings, average listing price, and total price of all listings by city.

    select
  v.city as "City",
  count(*) as "# of Listings",
  round(avg(v.price),1) as "Average Listing Price",
  sum(v.price) as "Total City Listings"
from migrated_airbnb.airbnb_listings_view v
group by v.city
order by 2 desc, 3 desc, 1

      An example of the results from the SELECT with calculations.