Advanced Query Capabilities 👉🏻 aggregation pipelines

Although MongoDB has supported ACID transactions and sophisticated aggregation features for years, certain publications still promote outdated misconceptions, claiming that only SQL databases provide robust data consistency and powerful querying capabilities. The “Benefits of Migrating” section in a spreadsheet company’s article is a recent example. It's yet another chance to learn from—and correct—misleading claims.

The claims ignore MongoDB’s advanced querying and multi-document transaction support. Written to market migration tools, this overlooks that MongoDB’s simple CRUD API is efficient for single-document tasks, and as a general-purpose database, it also offers explicit transactions and strong aggregation queries like SQL.

Enhanced Data Consistency and Reliability

The migration tool company justifies migrating by stating:

PostgreSQL’s ACID compliance ensures that all transactions are processed reliably, maintaining data integrity even in the event of system failures. This is particularly important for applications that require strong consistency, such as financial systems or inventory management.

Yes, PostgreSQL does provide ACID transactions and strong consistency, but this is mainly true for single-node deployments. In high-availability and sharded settings, achieving strong consistency and ACID properties is more complicated (see an example, and another example).

Therefore, highlighting ACID compliance as a reason to migrate from another database—when that alternative also supports ACID transactions—is not correct. For instance, single-node MongoDB has offered ACID compliance for years, and since v4.2, it supports multi-document transactions across replica sets and sharded clusters. Let's provide some syntax examples for the domains they mentioned.

Example: Financial system

Transfer $100 from Alice’s account to Bob’s account

// Initialize data  
db.accounts.insertMany([  
  { account_id: "A123", name: "Alice", balance: 500 },  
  { account_id: "B456", name: "Bob", balance: 300 }  
]);  

// Start a transaciton in a session
const session = db.getMongo().startSession();

try {
  accounts = session.getDatabase(db.getName()).accounts
  session.startTransaction();

  // Deduct $100 from Alice
  accounts.updateOne(
    { account_id: "A123" },
    { $inc: { balance: -100 } }
  );

  // Add $100 to Bob
  accounts.updateOne(
    { account_id: "B456" },
    { $inc: { balance: 100 } }
  );

  session.commitTransaction();
} catch (error) {
  session.abortTransaction();
  console.error("Transaction aborted due to error:", error);
} finally {
  session.endSession();
}

Why ACID matters in MongoDB here:

• Atomicity: Deduct and credit, either both happen or neither happens.
• Consistency: The total balance across accounts remains accurate.
• Isolation: Other concurrent transfers won’t interfere mid-flight.
• Durability: Once committed, changes survive crashes.

Example: Inventory management

Selling a product and recording that sale.

try {
  inventory = session.getDatabase(db.getName()).inventory
  session.startTransaction();

  // Reduce inventory count
  inventory.updateOne(
    { product_id: "P100" },
    { $inc: { quantity: -1 } }
  );

  // Add a record of the sale
  sales.insertOne(
    { product_id: "P100", sale_date: new Date(), quantity: 1 }
  );

  session.commitTransaction();
} catch (error) {
  session.abortTransaction();
  console.error("Transaction aborted due to error:", error);
} finally {
  session.endSession();
}

ACID guarantees in MongoDB:

• No partial updates
• Inventory stays synchronized with sales records
• Safe for concurrent orders
• Durable once committed

---

Advanced Query Capabilities

The migration tool vendor justifies migrating by stating:

PostgreSQL offers powerful querying capabilities, including:

• Complex joins across multiple tables
• Advanced aggregations and window functions
• Full-text search with features like ranking and highlighting
• Support for geospatial data and queries These allow for more sophisticated data analysis and reporting compared to MongoDB’s more limited querying capabilities.

This completely overlooks MongoDB’s aggregation pipeline.

Complex joins

MongoDB’s $lookup stage joins collections, even multiple times if you want.

Example: Join orders with customers to get customer names.

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "_id",
      as: "customer_info"
    }
  },
  { $unwind: "$customer_info" },
  {
    $project: {
      order_id: 1,
      product: 1,
      "customer_info.name": 1
    }
  }
]);

Advanced aggregations

Operators like $group, $sum, $avg, $count handle numeric calculations with ease.

Example: Total sales amount per product.

db.sales.aggregate([
  {
    $group: {
      _id: "$product_id",
      totalRevenue: { $sum: "$amount" },
      avgRevenue: { $avg: "$amount" }
    }
  },
  { $sort: { totalRevenue: -1 } }
]);

Window-like functions

MongoDB has $setWindowFields for operations akin to SQL window functions.

Running total of sales, sorted by date:

db.sales.aggregate([
  { $sort: { sale_date: 1 } },
  {
    $setWindowFields: {
      sortBy: { sale_date: 1 },
      output: {
        runningTotal: {
          $sum: "$amount",
          window: { documents: ["unbounded", "current"] }
        }
      }
    }
  }
]);

Full-text search with ranking & highlighting

MongoDB supports both simple text indexes and Atlas Search (powered by Apache Lucene).

Example with Atlas Search: Search in articles and highlight matches.

db.articles.aggregate([
  {
    $search: {
      index: "default",
      text: {
        query: "machine learning",
        path: ["title", "body"]
      },
      highlight: { path: "body" }
    }
  },
  {
    $project: {
      title: "1,"
      score: { $meta: "searchScore" },
      highlights: { $meta: "searchHighlights" }
    }
  }
]);

Geospatial queries

Native geospatial indexing with operators like $near.

Example: Find restaurants within 1 km of a point.

db.restaurants.createIndex({ location: "2dsphere" });

db.restaurants.find({
  location: {
    $near: {
      $geometry: { type: "Point", coordinates: [-73.97, 40.77] },
      $maxDistance: 1000
    }
  }
});

---

Conclusion

MongoDB and PostgreSQL have equivalent capabilities for ACID transactions and “advanced” queries — the difference lies in syntax and data model.

Unlike systems that use two-phase locking, MongoDB transactions do not block. They detect conflicts and let the application handle waiting and retrying if needed. This may impact scenarios requiring pessimistic locking (like SELECT FOR UPDATE SKIP LOCKED), but for general transactions, like those defined in the SQL standard, MongoDB offers complete support.

Instead of SQL in text strings sent to the database server to be interpreted at runtime, MongoDB uses a staged aggregation pipeline, fully integrated in your application language.

Migrating to PostgreSQL doesn’t magically grant you ACID or advanced analytics — if you’re already using MongoDB’s features, you already have them.