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

Don't know why `$lookup` here. Simply try this `db.collection.aggregate([ { $unwind: "$instock" }, { $replaceRoot: { newRoot: { $mergeObjects: [ "$$ROOT", "$instock" ] } } }, { $project: { instock: 0 } } ])` — Ashh, Oct 14 '18 at 16:22
hi Anthony Winzlet, thanks for your great suggestion. Please see my edit above using your solution. It works partially, can please help again if I can get all rows, including other warehouse B & C as well? Thank you — elvin, Oct 15 '18 at 04:56
This should work `Warehouse.aggregate([ { $unwind: "$instock" }, { $replaceRoot: { newRoot: { $mergeObjects: [ "$$ROOT", "$instock" ] } } }, { $project: { instock: 0 } } ])` ? What's the trouble ? — Ashh, Oct 15 '18 at 16:33
hi Anthony Winzlet, yes your solution is working as LEFT OUTER JOIN. I am looking if it can be an INNER JOIN. Anyway, it helps me greatly and I am able to proceed next using this to get what I want using your solution. Thanks so much. I can't mark as answer on this comment. I will just post another one on your solution, and mark as ANSWER. Thank you :D — elvin, Oct 16 '18 at 14:42

score 0 · Accepted Answer · edited Oct 16 '18 at 15:58

0

Solution from Anthony Winzlet:

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

edited Oct 16 '18 at 15:58

Ashh

44,693
14
105
132

answered Oct 16 '18 at 14:44

elvin

55
7

How to do mongodb inner join with nested array?

1 Answers1