mongodb_lookup.js

use lookup_experiment
db.items.insert({"name":"ink","price":5.55})
db.items.insert({"name":"pen","price":29.95, 
                 "description": {"color":"purple", "manufacturer":"Lamy", "model": "Safari" }})
db.items.insert({"name":"paper","price":5.00})
db.items.insert({"name":"pencil","price":10.00})

db.orders.insert({_id:1, "customer":"inkguy","items":["ink","pen","paper"]})
db.orders.insert({_id:2, "customer":"pencilgal","items":["pencil","paper"]})

// list of items with quantity embedded
db.orders.insert({_id:3, "customer":"designer","items":[
{"name":"pencil", "quantity":1},
{"name":"paper", "quantity": 2}
]})

// multiple rows combined together form the order
db.orders.insert({_id:4, "orderNumber":"ABC123","item":"ink","quantity":2})
db.orders.insert({_id:5, "orderNumber":"ABC123","item":"paper","quantity":3})

// simple item lookup
db.orders.aggregate([
    { $match: {_id : 1}},
    { $unwind: "$items" },
    { $lookup:
        {
          from: "items",
          localField: "items",
          foreignField: "name",
          as: "order_items"
        }
    },
    {
      $group: {
        _id : {orderid: "$_id"},
        "order_items": { $addToSet: "$order_items" }
      }
    }
])

// for the order that has a list of items with quantity embedded
db.orders.aggregate([
    { $match: {_id : 3}},
    { $unwind: "$items" },
    { $lookup:
        {
          from: "items",
          localField: "items.name",
          foreignField: "name",
          as: "order_items"
        }
    },
    {
      $group: {
        _id : {orderid: "$_id"},
        "order_items": { $addToSet: "$order_items" },
        "quantities": { $addToSet: "$items"}
      }
    }
])

// for the order that is made up of multiple documents
db.orders.aggregate([
    { $match: {"orderNumber" : "ABC123"}},
    { $lookup:
        {
          from: "items",
          localField: "item",
          foreignField: "name",
          as: "order_items"
        }
    },
    {
      $group: {
        _id : {customer: "$customer"},
        "order_items": { $addToSet: "$order_items" },
        "quantities": { $addToSet: {"item": "$item", "quantity": "$quantity" }}
      }
    }
])