How to avoid duplicate results from lookup in mongodb aggregation?-CodePudding

I have two collections needs to lookup in aggregate. But the output results duplicated not as my expected at commentsLookup and likesLookup field. I am trying to replace $push by $addToSet in $group but $sort will be broken from this collection resulting in wrong sort result. How can I avoid duplicate results or another way to solve this problem?

Here is my Mongo Playground for my situation:

https://mongoplayground.net/p/TKs7bPU_cVJ

CodePudding user response：

There are a couple of things, you had the field names inverted for the likes join, and also, as you were doing two unwinds, there were multiple instances of the comments (eg, duplicates, as you've alluded to) BEFORE you did your group.

I would approach the problem as almost as two separate actions:

First get everything looking good for the comments (lookup comments, unwind, lookup users group)
Then get everything looking good for the likes (lookup likes, unwind, lookup users group)

Something like this: https://mongoplayground.net/p/JPX-q32HMjE

db.posts.aggregate([
  {
    $lookup: {
      from: "comments",
      localField: "id",
      foreignField: "post_id",
      as: "comments"
    }
  },
  {
    $unwind: "$comments"
  },
  {
    $lookup: {
      from: "users",
      localField: "comments.user_id",
      foreignField: "id",
      as: "comments.user"
    }
  },
  {
    $set: {
      "comments.user": {
        $arrayElemAt: [
          "$comments.user",
          0
        ]
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      id: {
        $first: "$id"
      },
      title: {
        $first: "$title"
      },
      content: {
        $first: "$content"
      },
      comments: {
        $push: "$comments"
      }
    }
  },
  {
    $lookup: {
      from: "likes",
      localField: "id",
      foreignField: "post_id",
      as: "likes"
    }
  },
  {
    $unwind: "$likes"
  },
  {
    $lookup: {
      from: "users",
      localField: "likes.user_id",
      foreignField: "id",
      as: "likes.user"
    }
  },
  {
    $set: {
      "likes.user": {
        $arrayElemAt: [
          "$likes.user",
          0
        ]
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      id: {
        $first: "$id"
      },
      title: {
        $first: "$title"
      },
      content: {
        $first: "$content"
      },
      comments: {
        $first: "$comments"
      },
      likes: {
        $push: "$likes"
      }
    }
  }
])