MongoDB join 2 tables and get ids on condition-CodePudding

We are really new to MongoDB query writing. We have 2 MongoDB tables Supplier1 & Supplier 2. Both have the same _id. But the version number of these objects can be different sometimes. We need to find out _id when the version of 2 collections are different (i.e. Suplier1.version != Supplier2.version)

Supplier1

    { 
        "_id" : ObjectId("60cd86b914dfed073d77300f"), 
        "companyName" : "Main Supplier", 
        "version" : NumberLong(246), 
    }

Supplier2

    { 
        "_id" : ObjectId("60cd86b914dfed073d77300f"), 
        "companyName" : "Main Supplier", 
        "version" : NumberLong(247), 
    }

What we have written up to now and no idea to move forward with this. Any help is highly appreciated.

    db.getCollection("Supplier1").aggregate([
        {
            $lookup: {
            from: "Supplier2",
            localField: "_id",
            foreignField: "_id",
            as: "selected-supplier"
        }
        },

CodePudding user response：

You can simply use a sub-pipeline in $lookup. Simply $unwind the result array to filter out unwanted result.

db.Supplier1.aggregate([
  {
    "$lookup": {
      "from": "Supplier2",
      "let": {
        id1: "$_id",
        version1: "$version"
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$$id1",
                    "$_id"
                  ]
                },
                {
                  $ne: [
                    "$$version1",
                    "$version"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "selected-supplier"
    }
  },
  {
    "$unwind": "$selected-supplier"
  }
])

Here is the Mongo playground for your reference.