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

Question

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.

can you share a sample dataset maybe using mongoplayground.net. the result you want is also not very clear — cmgchess
– cmgchess, Commented Feb 17, 2022 at 14:53
so if the array has at least 1 FAILD it should count? or does it have to be last element in array — cmgchess
– cmgchess, Commented Feb 17, 2022 at 15:36
@cmgchess only if the last element in the internal array ("statusList") is not FAILD. — dev3dev
– dev3dev, Commented Feb 17, 2022 at 15:44

cmgchess · Accepted Answer · 2022-02-17 16:40:59Z

1

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

answered Feb 17, 2022 at 16:40

cmgchess

10.3k42 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

dev3dev Over a year ago

Thank you. but I just found out this solution is not accurate. mongoplayground.net/p/7tJIEZ7693M Here you can see that it’s also counts element with “target == admin” if the same “targetList” is also contain target “user”. (See example in the second element). And we need to count only elements with “target == user”.

cmgchess Over a year ago

@dev3dev i added size 1 to the first match. are you looking for something like mongoplayground.net/p/LkuDvh_5cnK

dev3dev Over a year ago

Now the query is only looking for a "targetLis" with the size of 1. What I need instead is that it will also look at "targetList" with multiply targets (like the second document there), but it will only count the target "user" and will ignore other targets. Here for example, the count result should be 1: mongoplayground.net/p/LkuDvh_5cnK

cmgchess Over a year ago

mongoplayground.net/p/eRbI0gArOnM check with this. i brought the unwind to the front so it first seperates into individual objects and then matched users

cmgchess Over a year ago

but now if targetlist has two users with no FAILD it will count as 2. is that what you want or?

|

Ben Brookes · Accepted Answer · 2022-02-17 15:43:43Z

0

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

answered Feb 17, 2022 at 15:43

Ben Brookes

4394 silver badges17 bronze badges

1 Comment

dev3dev Over a year ago

Thanks, but I don't want to know if it's in, I want to know if it's in the last element of the array.

rickhg12hs · Accepted Answer · 2022-02-20 00:51:40Z

0

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.

answered Feb 20, 2022 at 0:51

rickhg12hs

12k6 gold badges31 silver badges50 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related