MongoDB aggregation double lookup and pipeline

user_4337622 0
Viewed 49

I have a issue with the second $lookup when I am m using pipeline,but with localField and foreignField it works fine.

My collections bellow:

FeedCategories: 
{"_id":{"$oid":"1"},"name":"XXX1","status":"active"}
{"_id":{"$oid":"2"},"name":"XXX2","status":"active"}

Feeds:
{ "_id": { "$oid": "1" }, "category": { "$oid": "1" }, "status": "published" }
{ "_id": { "$oid": "2" }, "category": { "$oid": "1" }, "status": "published" }
{ "_id": { "$oid": "3" }, "category": { "$oid": "2" }, "status": "published" }

FeedCategoriesUser
{ "_id": { "$oid": "1" }, "Feed_left_id": { "$oid": "1" }, "User_right_id": { "$oid": "2" }}
{ "_id": { "$oid": "2" }, "Feed_left_id": { "$oid": "2" }, "User_right_id": { "$oid": "2" }}

My mongoose code:

    FeedCategoriesModel.aggregate([{
        $lookup: {
          from: 'feeds',
          let: { categoryId: "$_id" },
          pipeline: [
            { "$match": {
                "$expr": { "$eq": [ "$category", "$$categoryId" ] },
                "status": "published"
              }}
          ],
          as: 'feedsByCategory'
        }
      }, {
        $lookup: {
          from: 'feed_categories_user',
          localField: 'feedsByCategory._id',
          foreignField: 'Feed_left_id',
          as: 'feedsByCategoryCompleted'
        }
      }]);

but when I am using pipeline in this way, I dont have any results in feedsByCategoryCompleted:

FeedCategoriesModel.aggregate([{
    $lookup: {
      from: 'feeds',
      let: { categoryId: "$_id" },
      pipeline: [
        { "$match": {
            "$expr": { "$eq": [ "$category", "$$categoryId" ] },
            "status": "published"
          }}
      ],
      as: 'feedsByCategory'
    }
  }, {
    $lookup: {
      from: 'feed_categories_user',
      let: {feedid: "$feedsByCategory._id"},
      pipeline:[{
        "$match":{
          "$expr": { "$eq": [ "$Feed_left_id", "$$feedid" ] }
        }
      }],
      as: 'feedsByCategoryUsed'
    }
  }])

How to use piepeline for second $lookup in the above example?

mongoose mongodb
1 Answers

In the second lookup, you need to use $in insted of $eq inside $match. The reason is, feedsByCategory is an array from the first lookup, you are assigning its _id to feedid. So its also an array. When you check possible values inside the match within the array, you need to use $in

{
    $lookup: {
      from: "FeedCategoriesUser",
      let: {
        feedid: "$feedsByCategory._id"
      },
      pipeline: [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$Feed_left_id",
                "$$feedid"
              ]
            }
          }
        }
      ],
      as: "feedsByCategoryUsed"
    }
  }

Working Mongo playground

user_79757710
Related