I'm using MongoDB 3.4.15, I want to find all documents from the Rating collection where the resultid is not in the Result collection.

let's say that there is only one document in each collection

Rating collection

     {
            "_id" : ObjectId("6255818aaaaa7f47c79eba01"),
            "_class" : "rating",
            "ratingid" : "b6885038-17ef-437c-b9c8-be7336a09744",
            "value" : 5,
            "components" : [
                {
                    "resultid" : "2285e41f-ebe4-4372-8cf8-64dfe91a9733"
                },
                {
                    "resultid" : "69053352-d933-46a0-a20c-3863e01b87d6"
                }
            ]
        }

Result collection

    {
        "_id" : ObjectId("62558188aaaa7f47c79eb9ff"),
        "_class" : "normal_results",
        "resultid" : "2285e41f-ebe4-4372-8cf8-64dfe91a9733",
        "totalTime" : Double("10"),
        "lastModified" : NumberLong("1649770887268"),
        "creationDate" : NumberLong("1649770887268"),
    }

In this case, the Rating collection's document should be returned, because "resultid" : "69053352-d933-46a0-a20c-3863e01b87d6" is not into Result collection.

Please help me to write the query.

CodePudding user response：

You can simply perform the $lookup and compare whether the $size of the lookup result is equal to $size of components array.

In Mongo v3.4, you can do below:

db.Rating.aggregate([
  {
    "$lookup": {
      "from": "Result",
      "localField": "components.resultid",
      "foreignField": "resultid",
      "as": "resultLookup"
    }
  },
  {
    "$addFields": {
      "dedupeComponents": {
        "$reduce": {
          "input": "$components",
          "initialValue": [],
          "in": {
            "$cond": {
              "if": {
                "$in": [
                  "$$this.resultid",
                  "$$value"
                ]
              },
              "then": "$$value",
              "else": {
                "$concatArrays": [
                  "$$value",
                  [
                    "$$this.resultid"
                  ]
                ]
              }
            }
          }
        }
      },
      "resultLookup": {
        "$reduce": {
          "input": "$resultLookup",
          "initialValue": [],
          "in": {
            "$cond": {
              "if": {
                "$in": [
                  "$$this.resultid",
                  "$$value"
                ]
              },
              "then": "$$value",
              "else": {
                "$concatArrays": [
                  "$$value",
                  [
                    "$$this.resultid"
                  ]
                ]
              }
            }
          }
        }
      }
    }
  },
  {
    $addFields: {
      matched: {
        $eq: [
          {
            $size: "$dedupeComponents"
          },
          {
            $size: "$resultLookup"
          }
        ]
      }
    }
  },
  {
    "$match": {
      matched: false
    }
  },
  {
    "$project": {
      // cosmetics
      matched: false,
      dedupeComponents: false,
      resultLookup: false
    }
  }
])

Here is the Mongo playground for your reference.