CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-query
dev3dev
dev3dev

Reputation: 158

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

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.

Upvotes: 0

Views: 1952

Answers (3)

rickhg12hs
rickhg12hs

Reputation: 11912

Here's another way to do it with a leading monster "$match".

db.collection.aggregate([
  {
    "$match": {
      "targetsList.target": "user",
      "$expr": {
        "$reduce": {
          "input": "$targetsList",
          "initialValue": false,
          "in": {
            "$or": [
              "$$value",
              {
                "$ne": [
                  {
                    "$last": "$$this.statusList.type"
                  },
                  "FAILD"
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    "$count": "noFailedLastCount"
  }
])

Try it on mongoplayground.net.

Upvotes: 0

cmgchess
cmgchess

Reputation: 10247

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"
  }
])

Upvotes: 1

Ben Brookes
Ben Brookes

Reputation: 439

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

Upvotes: 0

Related Questions