Mongodb aggregate using multiple filters

Question

I am learning Mongodb aggregate function and I'm working on a query. My document looks like below.

[
    {
        "_id": 17,
        "members": [{
                "email": "test@gmail.com",
                "status": "pending",
                "joined": ISODate("2020-05-20T02:04:00Z")
            },
            {
                "email": "javatutorials2016@gmail.com",
                "status": "pending",
                "joined": ISODate("2020-05-20T02:36:00Z")
            }
        ],
        "messages": [{
                "c": "m1",
                "ts": ISODate("2020-05-20T02:04:15Z")
            },
            {
                "c": "m2",
                "ts": ISODate("2020-05-20T02:36:31Z")
            }
    
        ]
    }
]

Each document has 2 arrays: members and messages. I need to filter to one element in members (using email) and filter messages based on "members.joined" property matched against "messages.ts" property.

I tried different ways, couldn't achieve it yet. In the below query, I have hardcoded date ISODate("2020-05-20T02:36:00Z") instead of using members.joined property. How can I write an optimized query to achieve the same?

db.coll.aggregate([
  {
    $match: {
      _id: 17,
      "members.email": "javatutorials2016@gmail.com"
    }
  },
  {
    $project: {
      messages: {
        $filter: {
          input: "$messages",
          as: "messs",
          cond: {
            $gte: [
              "$$messs.ts",
              ISODate("2020-05-20T02:36:00Z") // supposed to have members.$.joined property here
            ]
          }
        }
      }
    }
  }
])

The expected result is the second element from messages that should be printed.

Answer 1

You can try anyone of below aggregation queries :

db.collection.aggregate([
    {
      $match: { _id: 17, "members.email": "javatutorials2016@gmail.com" }
    },
    /** If you need `members` array as is i.e; unfiltered in response, So instead of expensive iteration on `members` array we can get `joined` value as like below */
    {
      $addFields: {
        messages: {
          $let: {
            vars: {
              messagesArr: {
                $reduce: {
                  input: "$messages",
                  initialValue: { data: [], joinedTime: { $arrayElemAt: [ "$members.joined", { $indexOfArray: [ "$members.email", "javatutorials2016@gmail.com" ] } ] } },
                  in: {
                    data: {
                      $cond: [ { $gte: [ "$$this.ts", "$$value.joinedTime" ] },
                               { $concatArrays: [ "$$value.data", [ "$$this" ] ] }, // If condition is met concat holding array with new object
                               "$$value.data" // If not just return the holding array, doesn't add current object to array
                      ]
                    },
                    joinedTime: "$$value.joinedTime" // maintaining joined value
                  }
                }
              }
            },
            in: "$$messagesArr.data" // return newly created array in `$reduce` using `$let`
          }
        }
      }
    }
  ])

Test : mongoplayground

Ref : aggregation

So above query will return filtered messages array & original members array. Just in case if you need members as well to be filtered then add below $addFields stage after $match ( where we're assuming email is unique ) doing this can help to avoid iteration on huge arrays :

{
    $addFields: {
      members: {
        $arrayElemAt: [
          "$members",
          {
            $indexOfArray: [
              "$members.email",
              "javatutorials2016@gmail.com"
            ]
          }
        ]
      }
    }
  }

When you do above, members will be an filtered object. So in $reduce you can just do joinedTime: "$members.joined".

Test : mongoplayground

Answer 2

There is no state passed between aggregation pipeline stages other than the fields in the document.

The initial $match will ensure that some user in the members array has the email address, but in order to pull out the joined date for use in the later stage, you would need to either $unwind the array, use $reduce, or $filter to select the matching member, then you could reference that date in the $filter for the messages.

One possibility:

db.coll.aggregate([
  {$match: {
      _id: 17,
      "members.email": "javatutorials2016@gmail.com"
  }},
  {$addFields: {
      member: {
        $arrayElemAt: [
          {$filter: {
              input: "$members",
              cond: {
                $eq: [
                  "$$this.email",
                  "javatutorials2016@gmail.com"
                ]
              },

          }},
          0
        ]
      }
  }},
  {$project: {
      messages: {
        $filter: {
          input: "$messages",
          cond: {
            $gte: [
              "$$this.ts",
              "$member.joined"
            ]
          }
        }
      }
  }}
])

Playground