How to do mongodb inner join with nested array?

Question

Warehouses schema:

{_id: 1, name: 'A'}
{_id: 2, name: 'B'}
{_id: 3, name: 'C'}

Stocks schema:

{_id: 11, productId: 1, instock: [{warehouse: 'A', qty: 20}, {warehouse: 'B', qty: 5}, {warehouse: 'C', qty: 8}]
{_id: 12, productId: 2, instock: [{warehouse: 'A', qty: 30}]

I am new to MongoDB, but will like to have one row per record to show products' available qty in each of A,B,C warehouses:

Desired array output:

instock: [
           {_id: 11, productId: 1, warehouse: 'A', qty: 20},
           {_id: 11, productId: 1, warehouse: 'B', qty: 5},
           {_id: 11, productId: 1, warehouse: 'C', qty: 8},
           {_id: 12, productId: 2, warehouse: 'A', qty: 30},
           {_id: 12, productId: 2, warehouse: 'B', qty: 0},
           {_id: 12, productId: 2, warehouse: 'C', qty: 0}
]

I read about $lookup, $unwind, $project, and tried something like below but no where near to what I want:

    Warehouse.aggregate([
    {
      $lookup:
      {
        from: "stocks",
        pipeline: [ 
          { $project: { _id: 0, instock: {qty: 1, warehouse: 1} }},  
          { $replaceRoot: { newRoot: { newStock : '$instock' } } } 
        ],
        as: "instock"
      }
    } ,
 ]);

---

hi, Anothony Winzlet, your advise works partially, for example:

{_id: 12, productId: 2, instock: [{warehouse: 'A', qty: 30}]

From your solution: Result show only for warehouse A:

[{_id: 12, productId: 2, warehouse: 'A', qty: 30}] 

Can I get for warehouse B & C as well? (will default qty to 0 if not defined)

[{_id: 12, productId: 2, warehouse: 'A', qty: 30},
 {_id: 12, productId: 2, warehouse: 'B', qty: 0},
 {_id: 12, productId: 2, warehouse: 'C', qty: 0}]

Not sure if above is possible to achieve ... thank you

Answer 1

Solution from Anthony Winzlet:

Warehouse.aggregate([
  { "$unwind": "$instock" },
  { "$replaceRoot": { "newRoot": { "$mergeObjects": ["$$ROOT", "$instock"] } }},
  { "$project": { "instock": 0 } }
])