🚣‍♀️ MongoDB — SQL ROWNUM pseudo column

Photo by Vincent Botta on Unsplash

Oracle has a ROWNUM Pseudocolumn. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

Does MongoDB have something like Oracle SQL ROWNUM?

As of MongoDB version 5.0, there is!

Definition
$setWindowFields
New in version 5.0.

Performs operations on a specified span of documents in a collection, known as a window, and returns the results based on the chosen window operator.

Definition
New in version 5.0.

$documentNumber
Returns the position of a document (known as the document number) in the $setWindowFields stage partition.

Example

I have a dataset which is a list of the top largest articles (pages) in Wikipedia :

{
  "Article": "2022 United States House of Representatives elections",
  "Size": 534174
}
{
  "Article": "COVID-19 pandemic in New South Wales",
  "Size": 526730
}
{
  "Article": "Ricky Martin",
  "Size": 470596
}
...

I would like to add a “ranking” by size field to each document that represents an index from the smallest to the largest.

For example, in the documents above:

• Rank #1 — 2022 United States House of Representatives elections with a size of 534174
• Rank #2 — COVID-19 pandemic in New South Wales with a size of 526730
• Rank #3 — Ricky Martin with a size of 470596

Rank #1 is the largest article and Rank #3 is the smallest in this example.

In Oracle SQL we would simply use ORDER BY Size and add the pseudo column ROWNUM

In MongoDB we can use an aggregation pipeline with $setWindowFields and $documentNumber in the following manner:

[{$setWindowFields: {
 sortBy: {
  Size: -1
 },
 output: {
  rank: {
   $documentNumber: {}
  }
 }
}}]

• The documents are sorted by size in descending order
• a new field named “rank” is added to each document with the index value in the sorted document list

So now the collection looks like this:

{
  "Article": "COVID-19 pandemic in New South Wales",
  "Size": 526730,
  "rank": 2
}
{
  "Article": "List of Falcon 9 and Falcon Heavy launches",
  "Size": 502423,
  "rank": 4
}
{
  "Article": "Presidency of Donald Trump",
  "Size": 491556,
  "rank": 5
}
...

This is a very useful feature that was added to MongoDB 5.0 and I’m utilizing it a lot when building charts in MongoDB Charts.

⚘