Django pymongo search, sort, limit on inner array and count them

Question

I am learning Django with MongoDb and have a collection to search into. Here is a sample document in the collection:

{ 
    "_id": {    "$oid": "62615907568ddfca4fef3a25"  }, 
    "word": 'entropy, 
    "count": 4,
    "occurrence": [
        {"book": "62615907568ddfca4fef3a23",  "year": 1942, "sentence": 0 },
        {"book": "62615907568ddfca4fef3a23",  "year": 1942, "sentence": 5 },
        {"book": "62615907568ddfca4fef3a75",  "year": 1928, "sentence": 0 },
        {"book": "62615907568ddfca4fef3a90",  "year": 1959, "sentence": 8 } 
    ]
}

I want to retrieve the array elements of 'occurrence' field of a specific document (word):

1. Sorted by year
2. Within an year range
3. Limited with offset
4. count the total results (without limit/offset)

What I have done so far is:

offset= 0
limit= 10
search= {'$and': [{"_id": word_id_obj, "occurrence": {"$elemMatch": {"year": {"$gte": 1940, "$lte": 1960}}}}]}
word_docs= wordcollec.aggregate([
{"$match": search},
{"$project":
    {"occurrence":
        {"$slice": ['$occurrence', offset, limit]}
    }
},
{"$sort": {'occurrence.year': -1}}
])

# Count total results
recnt= wordcollec.aggregate([{"$match": search}, {'$group' : {"_id": "$_id", "sno" : {"$sum" : {"$size": "$occurrence"}}}}])

The year range, count are not working and I am unable to sort them. How can I rewrite my query for this?

Thanks in advance.

Answer 1

Use $unwind then $sort

db.collection.aggregate([
  {
    $match: {
      _id: { "$oid": "62615907568ddfca4fef3a25" },
      occurrence: { $elemMatch: { year: { $gte: 1940, $lte: 1960 } } }
    }
  },
  {
    $set: { totalWithoutLimitOrOffset: { $size: "$occurrence" } }
  },
  {
    $unwind: "$occurrence"
  },
  {
    $match: { "occurrence.year": { $gte: 1940, $lte: 1960 } }
  },
  {
    $sort: { "occurrence.year": -1 }
  },
  {
    $skip: 1
  },
  {
    $limit: 2
  },
  {
    $group: {
      _id: "$_id",
      word: { $first: "$word" },
      count: { $first: "$count" },
      totalWithoutLimitOrOffset: { $first: "$totalWithoutLimitOrOffset" },
      occurrence: { $push: "$occurrence" }
    }
  }
])

mongoplayground