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

CodePudding user response：

You can use the old school "let and match" syntax. In the $let clause, f is set as an variable to refer to the _fid field in the Products documents. So the query is matching Products._fid with the Products_History._fid(referenced by $_fid).

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