Back to DocumentationMQL Documentation

$bottom

The `$bottom` operator sorts documents on one or more fields specified by the query and returns the last document matching the filtering criteria.

Syntax

{
  $bottom: {
    output: [listOfFields],
    sortBy: {
      <fieldName>: <sortOrder>
    }
  }
}

Parameters

listOfFieldsobjectrequired

The list of fields to be returned from the last document in the result set.

fieldNamestringrequired

The field to use for sorting the result set.

sortOrdernumberrequired

1 or -1. 1 implies sorting in ascending order while -1 implies sorting in descending order.

Examples

Sample Data

{
  "_id": "0fcc0bf0-ed18-4ab8-b558-9848e18058f4",
  "name": "First Up Consultants | Beverage Shop",
  "sales": {
    "totalSales": 75670,
    "salesByCategory": [
      {
        "categoryName": "Wine Accessories",
        "totalSales": 34440
      }
    ]
  }
}

Find the store with lowest total sales

Determine the store with the lowest total sales by sorting in descending order and returning the last document.

This query groups by company and uses $bottom to find the store with the lowest sales.

Query:

db.stores.aggregate([
  {
    $match: {
      company: { $in: ["First Up Consultants"] }
    }
  },
  {
    $group: {
      _id: "$company",
      bottomSales: {
        $bottom: {
          output: ["$company", "$sales"],
          sortBy: { "sales.revenue": -1 }
        }
      }
    }
  }
])

Output:

[
  {
    "_id": "First Up Consultants",
    "bottomSales": [
      "First Up Consultants",
      {
        "totalSales": 119,
        "salesByCategory": [
          { "categoryName": "Skirts", "totalSales": 109 }
        ]
      }
    ]
  }
]

Find the category per store with the lowest sales

Find the lowest-selling category within each store by sorting categories and selecting the bottom one.

This query unwinds categories, groups by store, and finds the category with minimum sales.

Query:

db.stores.aggregate([
  { $unwind: "$sales.salesByCategory" },
  {
    $match: {
      "sales.salesByCategory.totalSales": { $exists: true }
    }
  },
  {
    $group: {
      _id: "$_id",
      storeName: { $first: "$name" },
      lowestCategory: {
        $bottom: {
          sortBy: { "sales.salesByCategory.totalSales": 1 },
          output: {
            categoryName: "$sales.salesByCategory.categoryName",
            totalSales: "$sales.salesByCategory.totalSales"
          }
        }
      }
    }
  }
])

Output:

[
  {
    "_id": "b1d86d1f-8705-4157-b64c-a9eda0df4921",
    "storeName": "VanArsdel, Ltd. | Baby Products Haven",
    "lowestCategory": { "categoryName": "Baby Monitors", "totalSales": 49585 }
  }
]

Related