Course Hero Logo

23mongodbLookupV2.pdf - COMP 430/533 Introduction to...

Course Hero uses AI to attempt to automatically extract content from documents to surface to you and others so you can study better, e.g., in search results, to enrich docs, and more. This preview shows page 1 - 9 out of 43 pages.

COMP 430/533Introduction toDatabase SystemsMongoDB "joins"Comp 430/533, Rice University1
Read/Find: Between collections$lookupLeft outer joinUnsharded collectionSame databaseEquality matchingResultsAdds to the input documentsArray field with matching elementsComp 430/533, Rice University2
$lookup: Syntaxdb.myCol.aggregate([ {$lookup:{from:joinToThisCollection,localField:myColKeyName,foreignField:joinToThisCollectionKeyName,as:<output array field>}}Comp 430/533, Rice University3SELECT myCol.*,outputArrayFieldValue.*FROM myCol LEFT OUTER JOINjoinToThisCollection dONmyCol. myColKeyName=d.joinToThisCollectionKeyName
$lookup: Example - datadb.orders.insert([{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },{ "_id" : 3 } ])db.inventory.insert([{ "_id" : 1, "sku" : "almonds", description: "tree fresh almonds", "instock" : 120 },{ "_id" : 2, "sku" : "bread", description: "freshly baked", "instock" : 80 },{ "_id" : 3, "sku" : "cashews", description: "best ever", "instock" : 60 },{ "_id" : 4, "sku" : "pecans", description: "delicious", "instock" : 70 },{ "_id" : 5, "sku":null, description: "Incomplete" },{ "_id" : 6 },{ "_id" : 7, "sku" : "almonds", description: "organic almonds", "instock" : 10 } ])Comp 430/533, Rice University4
$lookup: Example querydb.orders.aggregate([{ $lookup: { from: "inventory",localField: "item",foreignField: "sku",as: "inventory_docs"} } ]).pretty()Comp 430/533, Rice University51.Start at orders2.Match with inventoryon orders.item = inventory.sku3.Put results in arrayinventory_docs
$lookup: Example resultsWhat should we get?Look at the dataComp 430/533, Rice University6
$lookup: Example results1.{"_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2,"inventory_docs" :[ { "_id" : 1, "sku" : "almonds", "description" : "tree fresh almonds","instock" : 120 }, { "_id" : 7, "sku" : "almonds","description" : "organic almonds", "instock" : 10 } ] }1.{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1,"inventory_docs" :[ { "_id" : 4, "sku" : "pecans", "description" : "delicious","instock" : 70 } ]}1.{ "_id" : 3,"inventory_docs" :[ { "_id" : 5, "sku" :null, "description" : "Incomplete" },{ "_id" : 6 } ]}Comp 430/533, Rice University7
$lookup: Example - $unwinddb.orders.aggregate([{ $lookup: { from: "inventory", localField: "item", foreignField: "sku", as:"inventory_docs" } },{$unwind: "$inventory_docs"}])Comp 430/533, Rice University8{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2, "inventory_docs" : { "_id" : 1, "sku" : "almonds", "description" : "tree fresh almonds", "instock" : 120 } }{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2, "inventory_docs" : { "_id" : 7, "sku" : "almonds", "description" : "organic almonds", "instock" : 10 } }{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1, "inventory_docs" : { "_id" : 4, "sku" : "pecans", "description" : "delicious", "instock" : 70 } }{ "_id" : 3, "inventory_docs" : { "_id" : 5, "sku" : null, "description" : "Incomplete" } }{ "_id" : 3, "inventory_docs" : { "_id" : 6 } }Deconstructs an array field – denormalizes the document for each array elementNotice anything unusual about the results?

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 43 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
Spring
Professor
ChrisM
Tags
Array, Rice University, Rice Owls baseball

Newly uploaded documents

Show More

Newly uploaded documents

Show More

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture