Skip to content

Instantly share code, notes, and snippets.

@k0emt
Last active March 23, 2016 03:53
Show Gist options
  • Save k0emt/2caecdba57cbb06f4f91 to your computer and use it in GitHub Desktop.
Save k0emt/2caecdba57cbb06f4f91 to your computer and use it in GitHub Desktop.
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" }}
}
}
])
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment