mongodb nested array element $lookup

Question

I have two collections, orders and products. I like to join all the order.items[] to products collection to add more fields to the items[]

Sample Data:
orders
[{ _id: 1, items: [ { product_id: 1, price: 1.99, qty: 2 }, { product_id: 2, price: 3.99, qty: 5 } ]}]
products
[{ _id: 1, name: "Product 1" }, { _id: 2, name: "Product 2 }]

Expected output:
[{ _id: 1, items: [ { product_id: 1, name: "Product 1", price: 1.99, qty: 2 }, { product_id: 2, name: "Product 2",, price: 3.99, qty: 5 } ]}]

I have tried using $lookup and pipeline (mongodb 3.6) and not getting the name value or even the match is not working.

Thanks for a help!

Answer 1

This query will help you, sorry if I didn't use v3.6.

db.orders.aggregate([
{
    $unwind: "$items"
},
{
     $lookup:
       {
         from: "products",
         localField: "items.product_id",
         foreignField: "_id",
         as: "tproduct"
       }
},
{ 
    $project: 
    {
        "_id" : 1,
        "items.product_id" : 1,
        "items.name" : { $arrayElemAt: ["$tproduct.name", 0] },
        "items.price" : 1,
        "items.qty" : 1
    }
},
{
    $group : 
    { 
        _id : "$_id", 
        items: { $push: "$items" } 
    }
}
])

They are 4 stages that I will explain:

1. $unwind will create a single object for each element in the array.
2. $lookup will find the correct product, keep in mind that Product._id should be unique.
3. $project will format my documents and in items.name I'm taking the first element of the lookup sentence.
4. $group will use the _id to group and push each item into a new array.

I'm pretty sure there are cleaner and easier ways to write this, but this should work without problems.