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": "[email protected]",
                "status": "pending",
                "joined": ISODate("2020-05-20T02:04:00Z")
            },
            {
                "email": "[email protected]",
                "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": "[email protected]"
    }
  },
  {
    $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.

What do you've ISODate("2020-05-20T02:04:15Z") isn't it ISODate("2020-05-20T02:36:00Z") taken from object where "email": "[email protected]" ? Also do you want to filter members array as well to keep only object where "email": "[email protected]" - do you need it or members array needs to be as is original ? Can you edit question with required o/p from given sample doc.. — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented May 20, 2020 at 8:19
thanks. corrected the ISODate("2020-05-20T02:36:00Z") in the question. — javapedia.net
– javapedia.net, Commented May 20, 2020 at 11:36

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-05-20 09:13:27Z

You can try anyone of below aggregation queries :

db.collection.aggregate([
    {
      $match: { _id: 17, "members.email": "[email protected]" }
    },
    /** 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", "[email protected]" ] } ] } },
                  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",
              "[email protected]"
            ]
          }
        ]
      }
    }
  }

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

Test : mongoplayground

Joe · Accepted Answer · 2020-05-20 08:47:24Z

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": "[email protected]"
  }},
  {$addFields: {
      member: {
        $arrayElemAt: [
          {$filter: {
              input: "$members",
              cond: {
                $eq: [
                  "$$this.email",
                  "[email protected]"
                ]
              },

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

Playground

Collectives™ on Stack Overflow

Mongodb aggregate using multiple filters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related