Back to DocumentationMQL Documentation

$topN

The `$topN` operator sorts documents on one or more fields specified by the query and returns the first N documents matching the filtering criteria. It extends the functionality of `$top` by allowing you to retrieve multiple top elements.

Syntax

{
  $topN: {
    output: [listOfFields],
    sortBy: {
      <fieldName>: <sortOrder>
    },
    n: <numDocumentsToReturn>
  }
}

Parameters

listOfFieldsobjectrequired

The list of fields to be returned for the documents in the result set.

fieldNamestringrequired

The field to use for sorting the result set.

sortOrdernumberrequired

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

nnumberrequired

The number of documents to return from the top of the sorted result set.

Examples

Sample Data

{
  "_id": "0fcc0bf0-ed18-4ab8-b558-9848e18058f4",
  "name": "First Up Consultants | Beverage Shop",
  "sales": {
    "totalSales": 75670
  }
}

Get the two stores with the lowest total sales

Get the two lowest stores by sales by sorting in ascending order and taking the top two.

This query groups by company and uses $topN to find the two lowest-selling stores.

Query:

db.stores.aggregate([
  {
    $match: { company: { $in: ["First Up Consultants"] } }
  },
  {
    $group: {
      _id: "$company",
      topSales: {
        $topN: {
          output: ["$company", "$sales"],
          sortBy: { "sales.totalSales": 1 },
          n: 2
        }
      }
    }
  }
])

Output:

[
  {
    "_id": "First Up Consultants",
    "topSales": [
      [
        "First Up Consultants",
        {
          "salesByCategory": [
            { "categoryName": "Towel Sets", "totalSales": 520 }
          ],
          "revenue": 279183
        }
      ],
      [
        "First Up Consultants",
        {
          "salesByCategory": [
            { "categoryName": "Lavalier Microphones", "totalSales": 40000 }
          ],
          "revenue": 50000
        }
      ]
    ]
  }
]

Get the two most recent promotion events

Find the two most recent promotion events for each store by sorting by date.

This query groups by store and returns the top two promotions sorted by start date.

Query:

db.stores.aggregate([
  { $unwind: "$promotionEvents" },
  {
    $group: {
      _id: "$_id",
      storeName: { $first: "$name" },
      top2RecentPromotions: {
        $topN: {
          n: 2,
          sortBy: {
            "promotionEvents.promotionalDates.startDate.Year": -1,
            "promotionEvents.promotionalDates.startDate.Month": -1
          },
          output: {
            eventName: "$promotionEvents.eventName",
            startDate: "$promotionEvents.promotionalDates.startDate"
          }
        }
      }
    }
  }
])

Output:

[
  {
    "_id": "4a99546f-a1d2-4e61-ae9f-b8c7c1faf73c",
    "storeName": "Lakeshore Retail | Stationery Nook",
    "top2RecentPromotions": [
      {
        "eventName": "Crazy Markdown Madness",
        "startDate": { "Year": 2024, "Month": 9, "Day": 21 }
      },
      {
        "eventName": "Flash Sale Fiesta",
        "startDate": { "Year": 2024, "Month": 6, "Day": 23 }
      }
    ]
  }
]

Related