MongoDB aggregate with lookup on object with multiple arrays

Question

I have a collection containing objects that each contain multiple arrays. e.g.

assmemblies: { 
  mechanicalItems: [{ 
    mechanicalId: <ObjectId that references the mechanicalitems collection>,
    quantity: Number
  }],
  electricalItems: [{ 
    mechanicalId: <ObjectId that references the mechanicalitems collection>
    quantity: Number
}],

I'm trying to perform a lookup to get all the fields from the mechanicalitems and electricalitems into the assemblies collection but struggling to make the $lookup work.

When I try to unwind and group I lose the other array or it ends up nested inside each array object in the other one.

I've also tried to directly project back on the quantity property but I'm instead getting an Array of all the quantities:

{
    $lookup: {
      from: "mechanicalitems",
      let: {
        mechanicalItem: "$mechanicalItems",
        item: "$machanicalItems.mechanicalId",
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: ["$_id", "$$item"],
            },
          },
        },
        {
          $project: {
            "quantity": "$$mechanicalItem.quantity" // <-- this gives an array of all the quantities
          }
        },
      ],
      as: "mechanicalItems",
    },
  },

I've tried a few other options but can't quite get what I'm looking for.

*** Edit - Expected output

If my mechanicalItems fields are: 'size', 'material', 'style' and my electricalItems fields are: 'current', 'rating', 'insulation' then my expected output is:

assemblies: {
  mechanicalItems: [{
    item: {
      _id: <some ObjectId>
      size: '12',
      material: 'steel',
      style: 'thick'
    },
    quantity: Number
 }, ...
 ],
 electricalItems: [{
    item: {
      _id: <some ObjectId>
      current: '15',
      rating: 'low voltage',
      insulation: 'PVC'
    },
    quantity: Number
  }, ...
  ]
}

I'm hoping to swap out the mechanicalItemId for the actual objects from the mechanicalItems collection (and eventually rename the field to just "item" but I can do that with $addFields or $project later).

Answer 1

1. There is a typo error: $machanicalItems.mechanicalId, it should be $mechanicalItems.mechanicalId.

2. mechanicalItem.quantity returns an array of numbers as mechanicalItem is an array. Instead, you need to get the single quantity value for matching document of mechanicalItem array and set it to the quantity field.

db.assemblies.aggregate([
  {
    $lookup: {
      from: "mechanicalitems",
      let: {
        mechanicalItem: "$mechanicalItems",
        item: "$mechanicalItems.mechanicalId"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$item"
              ]
            }
          }
        },
        {
          $set: {
            "quantity": {
              $first: {
                $map: {
                  input: {
                    $filter: {
                      input: "$$mechanicalItem",
                      cond: {
                        $eq: [
                          "$_id",
                          "$$this.mechanicalId"
                        ]
                      }
                    }
                  },
                  in: "$$this.quantity"
                }
              }
            }
          }
        }
      ],
      as: "mechanicalItems"
    }
  }
])

The logic would be the same for the electricalItems array when you lookup join with the electricalItems collection.

Demo @ Mongo Playground