Is there a mongo operation for getting the count of documents in a sorted (and or filtered) collection after and before a provided document (_id)?

Question

For example, with pymongo:

I'm adding new document like this with a sequential index.

DB_1 = mongo ["DB_1"]
collection_1 = DB_1 ["collection_1"]
collection_1.insert_one ({
    'emblem': collection_1.find ().sort ({ 
        "emblem": -1
    }).limit (1).next () ["emblem"] + 1,
    'name': 'W'
})

As you can see, the names have a sequential "emblem" based on the order they were inserted (with most likely sequential indexes missing if a document is removed)

[{
  "emblem": 1,
  "name": "Z"
},{
  "emblem": 2,
  "name": "H"
},{
  "emblem": 3,
  "name": "T"
},{
  "emblem": 4,
  "name": "Q"
},{
  "emblem": 6,
  "name": "L"
}]

Then, after the aggregate sort, listed below, the emblem order is 2, 6, 4, 3, 1

documents = collection_1.aggregate ([
    { 
        "$addFields": {
            "lowerName": { 
                "$toLower": "$name" 
            }
        }
    },
    { 
        #
        #   sort by name, and if names are same,
        #   then sort by emblem.
        #
        "$sort": { 
            "lowerName": 1,
            "emblem": 1
        }
    }
])

Then, after the aggregate sort is performed, this algorithm calculates the counts of document before and after the document with a provided emblem.

emblem = 6

before = 0
after = 0
counting_before = True
counting_after = False
for document in documents:
    if (document ["emblem"] == emblem):
        counting_before = False
        counting_after = True
    elif (counting_before):
        before += 1
    elif (counting_after):
        after += 1

#
# after equals 1
# before equals 3
#

Is it possible to calculate these counts, as part of the aggregator or with another mongo query?

For context, I'd like to show something like this in the browser, where the buttons would do a search with the same search string, filter, and sorting. However they would have an additional input. Next would also have the last document in the current page, and prev would have the first document in the current page.

[prev (32 documents)] [next (281 documents)] 

Any help would be awesome, thanks!

Answer 1

You can use $setWindowFields with $sum and document window of ["unbounded", -1] and [1, "unbounded"] to count the number of documents before/after the current document.

db.collection.aggregate([
  {
    "$addFields": {
      "lowerName": {
        "$toLower": "$name"
      }
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": null,
      "sortBy": {
        "lowerName": 1,
        "emblem": 1
      },
      "output": {
        "before": {
          "$sum": 1,
          "window": {
            "documents": [
              "unbounded",
              -1
            ]
          }
        },
        "after": {
          "$sum": 1,
          "window": {
            "documents": [
              1,
              "unbounded"
            ]
          }
        }
      }
    }
  }
])

Mongo Playground