How to $filter where condition is matched against an array?

Question

after $lookup i will get a join named lastViewed like this:

{
  "_id" : "5955ea4fd8099718330ab191"

    lastViewed: [
        {
           "_id" : ObjectId("595218a7d346d27fb0bc1705"),
           "userId" : ObjectId("58c796d4344b9da4dfbe027b"),
           "groupId" : ObjectId("5955ea4fd8099718330ab191"),
           "lastViewedTime" : ISODate("2017-06-19T09:39:07.374Z")
        },
        {
           "_id" : ObjectId("595218a7d346d27fb0bc1764"),
           "userId" : ObjectId("58c796d4344b9da4dfbe027b"),
           "groupId" : ObjectId("5955ea4fd8099718330ab162"),
          "lastViewedTime" : ISODate("2017-05-11T09:39:07.374Z")
        }
      ]
}

I want to use $filter and then $project values , my query is below where the userId is getting from params

$project: { "viewedDetails": {
                        "$filter": {
                            "input": "$lastViewed",
                            "as": "lastViewed",
                            "cond": { $and: [{ "$eq": ["$$lastViewed.userId", mongoose.Types.ObjectId(userId)] }, { "$eq": ["$$lastViewed.groupId", '$_id'] }] }
                        }
                    }
                }

I got the exact output as below for userId 58c796d4344b9da4dfbe027b

{
  "_id" : "5955ea4fd8099718330ab191"

    viewedDetails: [
        {
           "_id" : ObjectId("595218a7d346d27fb0bc1705"),
           "userId" : ObjectId("58c796d4344b9da4dfbe027b"),
           "groupId" : ObjectId("5955ea4fd8099718330ab191"),
           "lastViewedTime" : ISODate("2017-06-19T09:39:07.374Z")
        }
      ]
}

Now i changed my schema into and got $lookup result as

{
  "_id" : "5955ea4fd8099718330ab191"

    lastViewed: [
        {
           "_id" : ObjectId("595218a7d346d27fb0bc1705"),
           "userId" : ObjectId("58c796d4344b9da4dfbe027b"),
           "members":[
                  {
                    "groupId" : ObjectId("5955ea4fd8099718330ab162"),
                    "lastViewedTime" : ISODate("2017-05-11T09:39:07.374Z")
                  }
                  {
                    "groupId" : ObjectId("5955ea4fd8099718330ab191"),
                    "lastViewedTime" : ISODate("2016-05-19T09:39:07.374Z")
                  }
              ]               
        }
      ]
}

then my filter for array of objects is not working , my query is

 $project: { "viewedDetails": {
                        "$filter": {
                            "input": "$lastViewed",
                            "as": "lastViewed",
                            "cond": { $and: [{ "$eq": ["$$lastViewed.userId", mongoose.Types.ObjectId(userId)] }, 
                                             { "$eq": ["$$lastViewed.members.groupId", '$_id'] }] }
                        }
                    }
                },

How can i get this without using $unwind, then using $project.

Answer 1

There is no need to $unwind purely for comparison, since there are various operators which can determine a logical result by comparing to a list. You really only need change the cond on the $filter here;

Filtering the "members" requires wrapping with $map to reflect the change though:

For MongoDB 3.4 you can use $in:

"viewedDetails": {
  "$map": {
    "input": {
      "$filter": {
        "input": "$lastViewed",
        "as": "lastViewed",
        "cond": { 
          "$and": [
            { "$eq": ["$$lastViewed.userId", mongoose.Types.ObjectId(userId)] }, 
            { "$in": [ "$_id", "$$lastViewed.members.groupId" ] }
          ]
        }
      }
    },
    "as": "v",
    "in": {
      "_id": "$$v._id",
      "userId": "$$v.userId",
      "members": { 
        "$filter": {
          "input": "$$v.members",
          "as": "m",
          "cond": { "$eq": [ "$$m.groupId", "$_id" ] }
        }
      }
    }
  }
}

Which compares the value against each of the values in the array returned by "$$lastViewed.members.groupId" and returns true or false depending on if it is a match.

If you cannot use $in then only the initial $filter need change to one of the following:

Eariler than tat use $setIsSubset

   "cond": { 
     "$and": [
       { "$eq": ["$$lastViewed.userId", mongoose.Types.ObjectId(userId)] }, 
       { "$setIsSubset": [ ["$_id"], "$$lastViewed.members.groupId" ] }
     ]
   }

or even $setIntersection and $size

   "cond": { 
     "$and": [
       { "$eq": ["$$lastViewed.userId", mongoose.Types.ObjectId(userId)] }, 
       { "$gt": [ 
         { "$size": { 
           "$setIntersection": [
             ["$_id"], 
             "$$lastViewed.members.groupId"
           ]
         }},
         0
       ] }
     ]
   }

Where the "intersection" resulting in a "set" that has "more than" 0 elements means that the value was present within the same referenced array.

Note that in those forms we make the "$_id" value an array as ["$_id"] since the "set comparison" is between "sets" and not an individual field.

---

Proof

This is the document you provided with corrections to syntax:

{
    "_id" : ObjectId("5955ea4fd8099718330ab191"),
    "lastViewed" : [ 
        {
            "_id" : ObjectId("595218a7d346d27fb0bc1705"),
            "userId" : ObjectId("58c796d4344b9da4dfbe027b"),
            "members" : [ 
                {
                    "groupId" : ObjectId("5955ea4fd8099718330ab162"),
                    "lastViewedTime" : ISODate("2017-05-11T09:39:07.374Z")
                }, 
                {
                    "groupId" : ObjectId("5955ea4fd8099718330ab191"),
                    "lastViewedTime" : ISODate("2016-05-19T09:39:07.374Z")
                }
            ]
        }
    ]
}

Here is the pipeline stage being run:

db.collection.aggregate([
  { "$project": {
    "viewedDetails": {
      "$map": {
        "input": {
          "$filter": {
            "input": "$lastViewed",
            "as": "lastViewed",
            "cond": { 
              "$and": [
                { "$eq": [ "$$lastViewed.userId", ObjectId("58c796d4344b9da4dfbe027b") ] }, 
                { "$in": [ "$_id", "$$lastViewed.members.groupId" ] }
              ]
            }
          }
        },
        "as": "v",
        "in": {
          "_id": "$$v._id",
          "userId": "$$v.userId",
          "members": { 
            "$filter": {
              "input": "$$v.members",
              "as": "m",
              "cond": { "$eq": [ "$$m.groupId", "$_id" ] }
            }
          }
        }
      }
    }
  }}
])

And here is the output:

{
    "_id" : ObjectId("5955ea4fd8099718330ab191"),
    "viewedDetails" : [ 
        {
            "_id" : ObjectId("595218a7d346d27fb0bc1705"),
            "userId" : ObjectId("58c796d4344b9da4dfbe027b"),
            "members" : [ 
                {
                    "groupId" : ObjectId("5955ea4fd8099718330ab191"),
                    "lastViewedTime" : ISODate("2016-05-19T09:39:07.374Z")
                }
            ]
        }
    ]
}