Likes table

{
    likedId: 'xxx'
    userId: '123'
}
{
    likedId: 'aaa'
    userId: '123'
}

Posts table

{
    postId: 'xxx'
    ...
}

Comments table

{
    commentId: 'aaa'
    ...
}

The Likes table records 'likes' which may be either Posts or Comments, stored in Posts and Comments collections respectively. I would like to do a lookup that only returns where the id in the Likes table only matches an id in the Posts table, and excludes returning any records where there is no match with the Posts table e.g. the 'aaa' id in the example which corresponds to an entry in the Comments table

The only way I can think to do it would be to use 'match' as follows, but I'm getting an empty array. The problem is, when I run the query I'm getting empty records where the 'likedId' in the Likes table doesn't match any documents in the Posts table

const pipeline = [
                {
                    '$lookup': {
                        'from': 'posts',
                        'localField': 'likedThing',
                        'foreignField': '_id',
                        'as': 'postData'
                    }
                }, {
                    '$match': {
                        'likedThing': 'postData._id'
                    }
                }, {
                    '$sort': {
                        'createdAt': -1
                    }
                }
            ]

I'm trying to tell it to only give me records where the likedId matches a _id in the Posts collection. Is this possible please?

CodePudding user response：

Your issue comes from your match stage, if you want to filter the records(Likes) that do not have any Posts liked, you can for example check if the array is empty:

db.likes.aggregate([
  {
    "$lookup": {
      "from": "posts",
      "localField": "likedId",
      "foreignField": "postId",
      "as": "postData"
    }
  },
  {
    "$match": {
      "postData": {
        "$ne": []
      }
    }
  },
])

Working example: https://mongoplayground.net/p/WSfe7gBkXkG

If you want to match further by a field in the array you can use $elemMatch: https://docs.mongodb.com/manual/reference/operator/query/elemMatch/