MongoDB: DML Operations

MongoDB: Create Collection, Insert, Select, Update and Delete with Oracle Equivalents

Table of Contents

1. Create a Collection (Table)

These MongoDB terms map to Oracle terms:

MongoDBOracle
DatabaseSchema (user)
CollectionTable
DocumentRow
FieldColumn
_id (added automatically)Primary key

First switch to your database, then create the collection. This step is optional, because MongoDB also creates the collection automatically when you insert the first document.

Connect to MongoDB first:

[mongop@recovery ~]$ mongosh -u admin -p --authenticationDatabase admin
Enter password: ******
Current Mongosh Log ID: 6ae9e8744b0e99873596e6f1
Connecting to:          mongodb://@127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&authSource=admin&appName=mongosh+2.12.0
Using MongoDB:          7.0.43
Using Mongosh:          2.12.0

Enterprise test>
MongoDBOracle
// Switch to the database
Enterprise test> use myFirstDB
switched to db myFirstDB
-- Switch to the schema
ALTER SESSION SET CURRENT_SCHEMA = myfirstdb;
// Create a collection
Enterprise myFirstDB> db.createCollection("students")
{ ok: 1 }
-- Create a table
CREATE TABLE students (
  name   VARCHAR2(50),
  age    NUMBER,
  course VARCHAR2(20)
);
// List the collections
Enterprise myFirstDB> show collections
students
-- List the tables
SELECT table_name FROM user_tables;

2. Insert Documents

Use insertOne for a single document and insertMany for several documents. MongoDB adds a unique _id to each document automatically.

MongoDBOracle
// Insert one document
Enterprise myFirstDB> db.students.insertOne({ name: "Ravi", age: 20, course: "CS" })
{
  acknowledged: true,
  insertedId: ObjectId('6ae9e9564b0e99873596e6f2')
}
-- Insert one row
INSERT INTO students (name, age, course)
VALUES ('Ravi', 20, 'CS');
// Insert many documents
Enterprise myFirstDB> db.students.insertMany([
  { name: "Anu", age: 22, course: "IT" },
  { name: "Kiran", age: 21, course: "CS" },
  { name: "Meena", age: 23, course: "ECE" }
])
{
  acknowledged: true,
  insertedIds: {
    '0': ObjectId('6ae9e97c4b0e99873596e6f3'),
    '1': ObjectId('6ae9e97c4b0e99873596e6f4'),
    '2': ObjectId('6ae9e97c4b0e99873596e6f5')
  }
}
-- Insert many rows
INSERT ALL
  INTO students (name, age, course) VALUES ('Anu', 22, 'IT')
  INTO students (name, age, course) VALUES ('Kiran', 21, 'CS')
  INTO students (name, age, course) VALUES ('Meena', 23, 'ECE')
SELECT * FROM dual;

COMMIT;

3. Select Documents (find)

The first argument of find is the filter (like a WHERE clause). The optional second argument chooses which fields to show: 1 shows a field and 0 hides it.

MongoDBOracle
// All documents
Enterprise myFirstDB> db.students.find()
[
  {
    _id: ObjectId('6ae9e9564b0e99873596e6f2'),
    name: 'Ravi',
    age: 20,
    course: 'CS'
  },
  {
    _id: ObjectId('6ae9e97c4b0e99873596e6f3'),
    name: 'Anu',
    age: 22,
    course: 'IT'
  },
  {
    _id: ObjectId('6ae9e97c4b0e99873596e6f4'),
    name: 'Kiran',
    age: 21,
    course: 'CS'
  },
  {
    _id: ObjectId('6ae9e97c4b0e99873596e6f5'),
    name: 'Meena',
    age: 23,
    course: 'ECE'
  }
]
-- All rows
SELECT * FROM students;
// With a condition
Enterprise myFirstDB> db.students.find({ course: "CS" })
[
  {
    _id: ObjectId('6ae9e9564b0e99873596e6f2'),
    name: 'Ravi',
    age: 20,
    course: 'CS'
  },
  {
    _id: ObjectId('6ae9e97c4b0e99873596e6f4'),
    name: 'Kiran',
    age: 21,
    course: 'CS'
  }
]
-- With a condition
SELECT * FROM students
WHERE course = 'CS';
// Comparison operator
Enterprise myFirstDB> db.students.find({ age: { $gt: 20 } })
[
  {
    _id: ObjectId('6ae9e97c4b0e99873596e6f3'),
    name: 'Anu',
    age: 22,
    course: 'IT'
  },
  {
    _id: ObjectId('6ae9e97c4b0e99873596e6f4'),
    name: 'Kiran',
    age: 21,
    course: 'CS'
  },
  {
    _id: ObjectId('6ae9e97c4b0e99873596e6f5'),
    name: 'Meena',
    age: 23,
    course: 'ECE'
  }
]
-- Comparison operator
SELECT * FROM students WHERE age > 20;
// Show only name and age
Enterprise myFirstDB> db.students.find({}, { name: 1, age: 1, _id: 0 })
[
  { name: 'Ravi', age: 20 },
  { name: 'Anu', age: 22 },
  { name: 'Kiran', age: 21 },
  { name: 'Meena', age: 23 }
]
-- Show only name and age
SELECT name, age FROM students;
// First matching document only
Enterprise myFirstDB> db.students.findOne({ name: "Ravi" })
{
  _id: ObjectId('6ae9e9564b0e99873596e6f2'),
  name: 'Ravi',
  age: 20,
  course: 'CS'
}
-- First matching row only
SELECT * FROM students
WHERE name = 'Ravi'
FETCH FIRST 1 ROW ONLY;
// Sort (-1 = descending) and limit
Enterprise myFirstDB> db.students.find().sort({ age: -1 }).limit(2)
[
  {
    _id: ObjectId('6ae9e97c4b0e99873596e6f5'),
    name: 'Meena',
    age: 23,
    course: 'ECE'
  },
  {
    _id: ObjectId('6ae9e97c4b0e99873596e6f3'),
    name: 'Anu',
    age: 22,
    course: 'IT'
  }
]
-- Sort and limit
SELECT * FROM students
ORDER BY age DESC
FETCH FIRST 2 ROWS ONLY;
// Count
Enterprise myFirstDB> db.students.countDocuments()
4
-- Count
SELECT COUNT(*) FROM students;

Operator equivalents:

MongoDBOracle
$gt>
$gte>=
$lt<
$lte<=
$ne!=
$inIN

4. Update Documents

The first argument is the filter and the second is the change. Always use an update operator such as $set, otherwise MongoDB returns an error. The result shows matchedCount (documents found) and modifiedCount (documents changed).

MongoDBOracle
// Update one document
Enterprise myFirstDB> db.students.updateOne({ name: "Ravi" }, { $set: { age: 21 } })
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
-- Update a row
UPDATE students SET age = 21
WHERE name = 'Ravi';
// Update many documents
Enterprise myFirstDB> db.students.updateMany({ course: "CS" }, { $set: { status: "active" } })
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 2,
  modifiedCount: 2,
  upsertedCount: 0
}
-- Update many rows
-- (the status column must already exist)
UPDATE students SET status = 'active'
WHERE course = 'CS';
// Increase a number
Enterprise myFirstDB> db.students.updateOne({ name: "Ravi" }, { $inc: { age: 1 } })
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
-- Increase a number
UPDATE students SET age = age + 1
WHERE name = 'Ravi';
// Remove a field
Enterprise myFirstDB> db.students.updateOne({ name: "Ravi" }, { $unset: { status: "" } })
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
-- Remove a value (set it to NULL)
UPDATE students SET status = NULL
WHERE name = 'Ravi';
// Insert if no match is found (upsert)
Enterprise myFirstDB> db.students.updateOne({ name: "Sita" }, { $set: { age: 20 } }, { upsert: true })
{
  acknowledged: true,
  insertedId: ObjectId('6aea051aa2de58124d45fd3e'),
  matchedCount: 0,
  modifiedCount: 0,
  upsertedCount: 1
}
-- Insert if no match is found (upsert)
MERGE INTO students s
USING (SELECT 'Sita' AS name, 20 AS age
       FROM dual) d
ON (s.name = d.name)
WHEN MATCHED THEN
  UPDATE SET s.age = d.age
WHEN NOT MATCHED THEN
  INSERT (name, age) VALUES (d.name, d.age);

COMMIT;

5. Delete Documents

Run find with the same filter first, to check which documents will be deleted.

MongoDBOracle
// Delete one document
Enterprise myFirstDB> db.students.deleteOne({ name: "Kiran" })
{ acknowledged: true, deletedCount: 1 }
-- Delete a row
DELETE FROM students WHERE name = 'Kiran';
// Delete many documents
Enterprise myFirstDB> db.students.deleteMany({ course: "IT" })
{ acknowledged: true, deletedCount: 1 }
-- Delete many rows
DELETE FROM students WHERE course = 'IT';
// Delete all documents
// (the collection stays)
Enterprise myFirstDB> db.students.deleteMany({})
{ acknowledged: true, deletedCount: 3 }
-- Delete all rows (the table stays)
DELETE FROM students;
COMMIT;

-- Faster, but cannot be rolled back
TRUNCATE TABLE students;
// Delete the whole collection
Enterprise myFirstDB> db.students.drop()
true
Enterprise myFirstDB> show collections

Enterprise myFirstDB>
-- Delete the whole table
DROP TABLE students PURGE;

Note: In Oracle, INSERT, UPDATE and DELETE need a COMMIT to save the change. In MongoDB, each of these commands is saved immediately.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you
Rajasekhar Amudala
Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala