Add a field to $lookup result in MongoDB

Question

I'm trying to add a field to a lookup result with the node.js MongoDB driver. A user has two kinds of feeds 1. His or her own feeds and 2. Shared feeds. So i need to get both and then combine them. This is my original query which works fine:

client.db('atlas').collection('users').aggregate([
    { $match: { user_id: userId } },
    {
      $lookup: {
        from: 'feeds',
        localField: "user_id",
        foreignField: "user_id",
        as: "feeds"
      }
    },
    {
      $lookup: {
        from: 'shares',
        localField: 'email',
        foreignField: "share",
        as: "shares"
      }
    },
    {
      $lookup: {
        from: 'feeds',
        localField: 'shares.feed_id',
        foreignField: "_id",
        as: "shared_feeds"
      }
    },
    {
      $addFields: { 'shared_feeds.share': true }
    },
    {
       $project: {
         user_id: 1,
         first_name: 1,
         last_name: 1,
         email: 1,
         feeds: { $concatArrays: [ '$feeds', '$shared_feeds' ] }
       }
    }
])

But I want to add a field to the second lookup on "feeds" so I tried using a pipeline like this:

{
  $lookup: {
    from: 'feeds',
    let: { shareId: '$shares.feed_id' },
    pipeline: [
      { $match: { '_id': ObjectId('$$shareId') } }
    ],
    as: "shared_feeds"
  }
}

But I can't get the match to work with ObjectId. The variable doesn't appear to be working. What am I doing wrong?

Sample collections: users:

[
    {
    "_id" : ObjectId("5e970a1f797c08e70ce4e2f6"),
    "user_id" : "testuserid",
    "first_name" : "Jacopo",
    "last_name" : "Belbo",
    "email" : "test@test.com"
   }
]

feeds:

 [
  {
      "_id" : ObjectId("5f29b02318ecbf0083f01f1e"),
      "name" : "test csv",
      "user_id" : "auth0|5eac6faa1cc1ac0c147b5b16",
      "type" : "feed",
      "created_at" : ISODate("2020-08-04T18:59:47.640Z"),
      "updated_at" : ISODate("2020-08-04T18:59:47.640Z")
  },
  {
    "_id" : ObjectId("5f29b05d18ecbf0083f01f1f"),
    "name" : "typtap-small",
    "user_id" : "auth0|5f1b2da0e60194003d714680",
    "url" : "http://localstack:4572/atlas/usertes",
    "type" : "feed",
    "created_at" : ISODate("2020-08-04T19:00:45.886Z"),
    "updated_at" : ISODate("2020-08-04T19:00:45.886Z")
  }
]

shares:

[
    {
    "_id" : ObjectId("5f29b07318ecbf0083f01f20"),
    "feed_id" : ObjectId("5f29b05d18ecbf0083f01f1f"),
    "share" : "test@test.com",
    "type" : "share",
    "recipient" : "email",
    "created_at" : ISODate("2020-08-04T19:01:07.668Z"),
    "updated_at" : ISODate("2020-08-04T19:01:07.668Z")
}
]

user_7975771 · Answer

In let: { shareId: '$shares.feed_id' }, your are passing an ObjectId to shareId. So you can't wrap with ObjectId() in match stage. When you use pipeline inside a lookup for multiple conditions Just use $expr

    {
      $match: {
        $expr: {
          $eq: [
            "$$shareId",
            "$_id"
          ]
        }
      }
    }

Update 1.

when you use pipeline inside $lookup, you need to use $expr, Refer $expr in lookup. Once you get the out put, you will have an array, so $unwind it and do the join. Then group it to avoid duplicates.

Working Mongo playground

Update 2

The mongo script

[
  {
    $match: {
      user_id: "testuserid"
    }
  },
  {
    $lookup: {
      from: "feeds",
      localField: "user_id",
      foreignField: "user_id",
      as: "feeds"
    }
  },
  {
    $lookup: {
      from: "shares",
      localField: "email",
      foreignField: "share",
      as: "shares"
    }
  },
  {
    $unwind: {
      path: "$shares",
      preserveNullAndEmptyArrays: false
    }
  },
  {
    $lookup: {
      from: "feeds",
      let: {
        shareId: "$shares.feed_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$$shareId",
                "$_id"
              ]
            }
          }
        }
      ],
      as: "shared_feeds"
    }
  },
  {
    $group: {
      _id: "$_id",
      user_id: {
        $first: "$user_id"
      },
      first_name: {
        $first: "$first_name"
      },
      last_name: {
        $first: "$last_name"
      },
      email: {
        $first: "$email"
      },
      feeds: {
        $first: "$feeds"
      },
      shares: {
        $addToSet: "$shares"
      },
      shared_feeds: {
        $addToSet: "$shared_feeds"
      }
    }
  },
  {
    $addFields: {
      shared_feeds: {
        $reduce: {
          input: "$shared_feeds",
          initialValue: [],
          in: {
            $setUnion: [
              "$$this",
              "$$value"
            ]
          }
        }
      }
    }
  },
  {
    $addFields: {
      "shared_feeds.share": true
    }
  },
  {
    $project: {
      user_id: 1,
      first_name: 1,
      last_name: 1,
      email: 1,
      feeds: {
        $concatArrays: [
          "$feeds",
          "$shared_feeds"
        ]
      }
    }
  }
]

user_8987128 · Answer

You need to use $expr with $eq in match condition, because without expression it will not match reference fields, there is one example in MongoDB Lookup with Pipeline,

here need to use $_id instead of _id, because expression will not find field without $ and $eq will not match condition,

{
  $lookup: {
    from: 'feeds',
    let: { shareId: '$shares.feed_id' },
    pipeline: [
      { 
        $match: { 
          $expr: {
            $eq: ['$$shareId', '$_id'] 
          }
        }
      }
    ],
    as: "shared_feeds"
  }
}

I am not sure what the type of this shares.feed_id field, if both the collection have different type, either one is in string or one is in object id then you can use toObjectId,

You can convert string to object id like this,

{
  $lookup: {
    from: 'feeds',
    let: { shareId: '$shares.feed_id' },
    pipeline: [
      { 
        $match: { 
          $expr: {
            $eq: [
              { '$toObjectId': '$$shareId' }, 
              '$_id'
            ] 
          }
        }
      }
    ],
    as: "shared_feeds"
  }
}

Add a field to $lookup result in MongoDB

2 Answers