I have 2 collections: post and comment. post has many comment.

post

• _id
• title: String
• description: String

comment

• post: ObjectId of post
• content: String

For example, I want to query for posts that have at least 1 comment with the string "foo" in the content. How to do it?

I can join the comments by using $lookup by the following code:

db.collection('post').aggregate(['$lookup': {
    from: 'comment',
    localField: '_id',
    foreignField: 'post',
    as: 'comments',
    pipeline: [{
      "content": /foo/
    }]
}])

But don't know how to limit the results that at least 1 comment for post collection.

CodePudding user response：

Add a match stage with regex. As you just need a post with at least 1 comment containing a certain string, there's is no need to check if there are X number of comments.

db.post.aggregate([
      {
        "$lookup": {
          "from": "comments",
          "localField": "_id",
          "foreignField": "post",
          "as": "comments"
        }
      },
      {
        "$match": {
          "comments.content": {
            "$regex": "product"
          }
        }
      },
      {
        "$project": {
          "comments": 0
        }
      }
    ])

MongoDB Playground