I have two collections:

Product

{ id: "1", model: "Light1", category: "Light"},
{ id: "2", model: "Light3", category: "Light"},
{ id: "3", model: "Lock1", category: "Lock"},

Item

{ id: "1", model: "Light1", category: "Light", color: "Blue"},
{ id: "2", model: "Light2", category: "Light", color: "Blue"},
{ id: "3", model: "Lock1", category: "Lock", color: "Blue"},
{ id: "4", model: "Light3", category: "Light", color: "Blue"}
{ id: "5", model: "Lock2", category: "Lock", color: "Blue"},

I want to find documents from the Item collection containing both model and category from the product collection.

From the example above, I want to get this so called new collection:

{ id: "1", model: "Light1", category: "Light", color: "Blue"},
{ id: "3", model: "Lock1", category: "Lock", color: "Blue"},
{ id: "4", model: "Light3", category: "Light", color: "Blue"}

CodePudding user response：

You can try this aggregation query:

• First $lookup from Item collection to join collections. This lookup uses a pipeline where you match the desired values: Local model is equal to foreign model and local category is equal to foreign category. This produces an array as output: if there is not any match the array will be empty.
• So you can $match to not shown empty result array.
• And use $project to output the values you want.

db.Item.aggregate([
  {
    "$lookup": {
      "from": "Product",
      "let": {
        "model": "$model",
        "category": "$category"
      },
      "pipeline": [
        {
          "$match": {
            "$and": [
              {
                "$expr": {
                  "$eq": [
                    "$model",
                    "$$model"
                  ]
                }
              },
              {
                "$expr": {
                  "$eq": [
                    "$category",
                    "$$category"
                  ]
                }
              }
            ]
          }
        }
      ],
      "as": "result"
    }
  },
  {
    "$match": {
      "result": {
        "$ne": []
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "result": 0
    }
  }
])

Example here