Add some kind of row number to a mongodb aggregate command / pipeline

Question

The idea is to return a kind of row number to a mongodb aggregate command/ pipeline. Similar to what we've in an RDBM.

It should be a unique number, not important if it matches exactly to a row/number.

For a query like:

[ { $match: { "author" : { $ne: 1 } } }, { $limit: 1000000 } ]

I'd like to return:

{ "rownum" : 0, "title" : "The Banquet", "author" : "Dante", "copies" : 2 }
{ "rownum" : 1, "title" : "Divine Comedy", "author" : "Dante", "copies" : 1 }
{ "rownum" : 2, "title" : "Eclogues", "author" : "Dante", "copies" : 2 }
{ "rownum" : 3, "title" : "The Odyssey", "author" : "Homer", "copies" : 10 }
{ "rownum" : 4, "title" : "Iliad", "author" : "Homer", "copies" : 10 }

Is it possible to generate this rownum in mongodb?

Answer 1

Not sure about the performance in big queries, but this is at least an option.

You can add your results to an array by grouping/pushing and then unwind with includeArrayIndex like this:

[
  {$match: {author: {$ne: 1}}},
  {$limit: 10000},
  {$group: {
    _id: 1,
    book: {$push: {title: '$title', author: '$author', copies: '$copies'}}
  }},
  {$unwind: {path: '$book', includeArrayIndex: 'rownum'}},
  {$project: {
    author: '$book.author',
    title: '$book.title',
    copies: '$book.copies',
    rownum: 1
  }}
]

Now, if your database contains a big amount of records, and you intend to paginate, you can use the $skip stage and then $limit 10 or 20 or whatever you want to display per page, and just add the number from the $skip stage to your rownum and you'll get the real position without having to push all your results to enumerate them.