Sort documents with a limit based on multiple properties inside array of object

I'm trying to sort documents based on a label and a level, with a limit.

Consider the following document structure

 {
      "_id" : ObjectId("660269c42c04edea870276d4"),
      "skills" : [
           {"label" : "painting", "level" : 10},
           {"label" : "singing", "level" : 5}
      ]
 }

I'm filtering the documents to only consider the ones with the painting skill. My query is

 {"skills.label" : "painting", "_id" : {"$nin" : ARRAY_OF_PREVIOUSLY_RETURNED_IDS }}

The problem I'm having is that I want to return the results from top to bottom based on level by batches of 50, so I'm passing a limit. I'll be saving the _id so I can skip the previously returned.

How can I do that ? Ideally my sort object would be

{"skills.label" : "painting", "skills.level" : -1}

but I cannot refilter inside the sorting object. How can I sort considering only the level of the painting skill ? The desired output is

 [
      {
           "_id" : ObjectId("660269c42c04edea870276d4"),
           "skills" : [
                {"label" : "painting", "level" : 10},
                {"label" : "singing", "level" : 5}
           ]
      },
      {
           "_id" : ObjectId("660269c42c04edea870276d5"),
           "skills" : [
                {"label" : "painting", "level" : 9},
                {"label" : "dancing", "level" : 15}
           ]
      },
      ...
 ]

Solution

Create a temporary field which has the value or object which you will be filtering on. In this case, the skills object where label = 'painting'. Sort on that field and then unset.

In this case, I've created the temporary field by filtering the skills array to only the 'painting' skill object and then using just the $first one, so that it's just that single object. Then sorting on the nested .level field.

db.collection.aggregate([
  {
    $set: {
      sortField: {
        $first: {
          $filter: {
            input: "$skills",
            cond: { $eq: ["$$this.label", "painting"] }
          }
        }
      }
    }
  },
  { $sort: { "sortField.level": -1 } },
  { $unset: "sortField" }
])

Mongo Playground

You could also use $reduce to create that with just the level field.

Edit: Additionally, since this sort is deterministic, you can use $skip and $limit per usual instead of needing to keep excluding previously returned ids. Otherwise, getting page 100 means first getting the results from pages 1-99 (99 queries) and then the 100th. (This can be optimised but is better to just avoid; and use skip & limit instead.)