Convert SELECT DISTINCT to MongoDB distinct() or Aggregate

From DISTINCT to $group

SQL's SELECT DISTINCT eliminates duplicate rows from the result set. In MongoDB, this is achieved through the aggregation pipeline using a $group stage that groups by the selected fields.

Example SQL

SELECT DISTINCT status FROM orders

Generated MongoDB Query

db.orders.aggregate([
  {
    $group: {
      _id: "$status",
      status: { $first: "$status" }
    }
  }
])

Alternative: db.collection.distinct()

For single-field distinct queries, MongoDB also offers a simpler method:

db.orders.distinct("status")

This returns a plain array of unique values rather than documents. However, distinct() is limited to a single field and has a 16 MB result size limit, so the aggregate approach is more general.

Multi-Field DISTINCT

When DISTINCT applies to multiple columns, the $group stage uses a composite _id:

SELECT DISTINCT category, status FROM products

db.products.aggregate([
  {
    $group: {
      _id: { category: "$category", status: "$status" },
      category: { $first: "$category" },
      status: { $first: "$status" }
    }
  }
])

When to Use Each Approach

Use distinct() for quick single-field uniqueness checks. Use the aggregate pipeline when you need distinct combinations of multiple fields, or when you want to chain additional stages like $sort or $match after deduplication.