Mongoose - How to query field in the last object of an array of objects-CodePudding

I have MongoDB documents structured like this:

{
    "_id": "5d8b987f9f8b9f9c8c8b9f9",
    "targetsList": [
        {
            "target": "user",
            "statusList": [
                {
                    "date": "2018-01-01",
                    "type": "OK"
                },
                {
                    "date": "2018-01-02",
                    "type": "FAILD"
                }
            ]
        }
    ]
}

And I want to count all documents that in their "targetList" array, there is an object with "target"=="user" - and also that object conatin on the last element of its "statusList" array, an object with "type" != "FAILD".

Any ideas on how to implement this kind of query?

Mongo playground: https://mongoplayground.net/p/3bCoHRnh-KQ In this example, I expected the count to be 1, because only the second object meets the conditions.

CodePudding user response：

An aggregation pipeline
1st step - Filtering out where "targetsList.target": "user"
2nd step - $unwind on targetsList to get it out of array
3rd step - getting the last element of the targetsList.statusList array using $arrayElemAt
4th step - getting the results where that last element is not FAILD
5th step - getting the count

demo - you can try removing parts of the pipeline to see what the intermediate results are

db.collection.aggregate([
  {
    $match: {
      "targetsList.target": "user"
    }
  },
  {
    $unwind: "$targetsList"
  },
  {
    $project: {
      "targetsList.statusList": {
        $arrayElemAt: [
          "$targetsList.statusList",
          -1
        ]
      },
      
    }
  },
  {
    $match: {
      "targetsList.statusList.type": {
        $ne: "FAILD"
      }
    }
  },
  {
    $count: "withoutFailedInLastElemCount"
  }
])

CodePudding user response：

Unless it's crucial that the element be the last index, this should work for your case.

db.collection.find({
  "targetsList.statusList.type": {
    $in: [
      "FAILD"
    ]
  }
})

This will retrieve documents where the type value is FAILD. To invert this you can swap $in for $nin.

Updated playground here