Complex mongodb query with arrays operations

I have got a collection of documents like:

{
    _id: ObjectId("63f4c47a6edd646542c4a2c9"),
    title: 'ABCDEF',
    counter: 230,
    values: [
      {
        a: 4,
        b: 4,
        date: ISODate("2023-02-21T00:00:00.000Z")
      },
      {
        a: 2,
        b: 4,
        date: ISODate("2023-02-25T00:00:00.000Z")
      }
    ]
}

I would like to find all documents which have counter less than 201 and which value with the highest date has 'b' greater than 'a' by some minimum ratio: let's say 2.

How to accomplish that?

BR

If I've understood correctly you can try this query:

• First get only documents with counter less than 201.
• Then create an aux field with the highest date element from the array. Sorting array by date and getting the first will give desired one.
• Then $match again to filter elements where the ratio between a and b is greater than 2 (i.e. b > a * 2.).
• And last step is $project to not output the aux value and only show the document itself.

db.collection.aggregate([
  {
    "$match": {
      "counter": { "$lt": 201 }
    }
  },
  {
    "$set": {
      "auxvalue": {
        "$arrayElemAt": [
          {
            "$sortArray": {
              "input": "$values",
              "sortBy": { "date": -1 }
            }
          },
          0
        ]
      }
    }
  },
  {
    "$match": {
      "$expr": {
        "$gte": [
          "$auxvalue.b",
          {
            "$multiply": [ "$auxvalue.a", 2 ]
          }
        ]
      }
    }
  },
  {
    "$project": { "auxvalue": 0 }
  }
])

Example here