Is it possible to filter nested array objects by their fields in MongoDB $match aggregation stage?

Question

I have a user review collection in MongoDB which stores product reviews by users. In addition I have a user reply collection which are replies to a user review.

User review has the following document structure:

{
    username: 'John',
    content: 'I liked the product',
}

User reply has the following document structure:

{
    username: 'John',
    content: 'I liked the product',
    userReviewId: ObjectID('blabla...'),
    status: 'REJECTED' // or can be 'APPROVED'
}

I want to make an aggregation which would look up all user reviews with replies with the status APPROVED only. I tried the following aggregation in MongoDB shell:

db.getCollection('UserReview').aggregate(
[
  {
    "$lookup": {
      "from": "UserReply",
      "localField": "_id",
      "foreignField": "userReview",
      "as": "replies"
    }
  },
  {
    "$match": {
      "replies": {
        "$elemMatch": {"status":"REJECTED"}
      }
    }
  }
]
)

If a certain user review has replies that are both approved and rejected, the above query will fetch a user review with both types of replies like so:

{
    username: 'John',
    content: 'I liked the product',
    replies: [
        {
            content: 'reply1'
            userReviewId: ObjectID('blabla...'),
            status: 'REJECTED'
        },
        {
            content: 'reply1'
            userReviewId: ObjectID('blabla...'),
            status: 'APPROVED'
        }
    ]
    
}

However I expected the result to be as follows, that is to include only approved replies in replies field of user review:

{
    username: 'John',
    content: 'I liked the product',
    replies: [
        {
            content: 'reply1'
            userReviewId: ObjectID('blabla...'),
            status: 'APPROVED' // or can be 'APPROVED'
        }
    ]
    
}

The only way I found to actually get only approved replies is to use $filter operation in projection stage:

$addFields: {
  replies: {
    $filter: {
      input: '$replies',
      as: 'reply',
      cond: { $in: ['$$reply.status', ['APPROVED']] }
    }
  }
}

Is it possible to performing the filtering of replies under $match stage or this is only possible within projections/addFields?

turivishal · Accepted Answer · 2020-12-21 19:15:00Z

1

You can use lookup with aggregation pipeline,

let pass localField to match with foreignField in pipeline,
$expr to match id and match status is approved inside $and condition

db.UserReview.aggregate([
  {
    "$lookup": {
      "from": "UserReply",
      "let": { id: "$_id" },
      "pipeline": [
        {
          $match: {
            $and: [
              { $expr: { $eq: ["$$id", "$userReviewId"] } },
              { status: "APPROVED" }
            ]
          }
        }
      ],
      "as": "replies"
    }
  }
])

Playground

answered Dec 21, 2020 at 19:15

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

6 Comments

hitchhiker Over a year ago

Is there any added value from $and? According to this link docs.mongodb.com/manual/reference/operator/query/and "MongoDB provides an implicit AND operation when specifying a comma separated list of expressions."

hitchhiker Over a year ago

Also let's say replies was a nested field in UserReview collection so no lookup was needed. Would there be any other option except for $filter?

turivishal Over a year ago

Is there any added value from $and? both expression are different, here we are checking both fields (id) are equal or not that are already exists in document so that is and expression match, and second we are matching status with external value so that is not an expression, but the {status: "APPROVED"} full condition is a expression for the $and.

turivishal Over a year ago

Also let's say replies was a nested field yes that is good approach to manage comments in same collection and no need to lookup, $filter is the beast option.

turivishal Over a year ago

yes of course you are right, it will work without $and, this is just for easy to understand conditions.

|

Collectives™ on Stack Overflow

Is it possible to filter nested array objects by their fields in MongoDB $match aggregation stage?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related