lookup with pipeline without specifying localfield and foreignfield

November 24, 2022

db.Products.aggregate([
  {
    $lookup: {
      from: "Products_History",
      localField: "_fid",
      foreignField: "_fid",
      as: "joins",
      pipeline: [
        {
          "$sort": {
            "date": -1
          }
        }
      ]
    }
  },
  {
    "$project": {
      "_fid": 1,
      "field1": 1,
      "field2": 1,
      "field3": 1,
      "last_version": {
        $first: "$joins.version"
      }
    }
  },
  {
    $match: {
      "last_version": {
        $exists: true
      }
    }
  }
])

This works well when MongoDB is version 5 or higher.

However, on my current version I am getting: "$lookup with ‘pipeline’ may not specify ‘localField’ or ‘foreignField’"

Is there a way to fix the query while still joining them. I don’t know of a different way to do this.

https://mongoplayground.net/p/SYsmjYjOdNJ

>Solution :

You can use the old school "let and match" syntax.

db.Products.aggregate([
  {
    $lookup: {
      from: "Products_History",
      "let": {
        f: "$_fid"
      },
      as: "joins",
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$$f",
                "$_fid"
              ]
            }
          }
        },
        {
          "$sort": {
            "date": -1
          }
        }
      ]
    }
  },
  {
    "$project": {
      "_fid": 1,
      "field1": 1,
      "field2": 1,
      "field3": 1,
      "last_version": {
        $first: "$joins.version"
      }
    }
  },
  {
    $match: {
      "last_version": {
        $exists: true
      }
    }
  }
])

Mongo Playground