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

user_9914653 0
Viewed 337

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?

mongodb aggregation
1 Answers

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

user_89871280
Related