Downloaded 19 times
![Document vs. Relational Model
RDBMS MongoDB
{
_id: ObjectId("4c4ba5e5e8aabf3"),
employee_name: {First: "Billy",
Last: "Fish"},
department: "Engineering",
title: "Aquarium design",
pay_band: "C",
benefits: [
{ type: "Health",
plan: "PPO Plus" },
{ type: "Dental",
plan: "Standard" }
]
}](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-5-2048.jpg)
![Existing Alternatives to Joins
{ "_id": 10000,
"items": [
{
"productName": "laptop",
"unitPrice": 1000,
"weight": 1.2,
"remainingStock": 23
},
{
"productName": "mouse",
"unitPrice": 20,
"weight": 0.2,
"remainingStock": 276
}
],
…
}
• Option 1: Include all data for an order in
the same document
– Fast reads
• One find delivers all the required data
– Captures full description at the time of the
event
– Consumes extra space
• Details of each product stored in many order
documents
– Complex to maintain
• A change to any product attribute must be
propagated to all affected orders
orders](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-6-2048.jpg)
![Existing Alternatives to Joins
{
"_id": 10000,
"items": [
12345,
54321
],
...
}
• Option 2: Order document
references product documents
– Slower reads
• Multiple trips to the database
– Space efficient
• Product details stored once
– Lose point-in-time snapshot of full
record
– Extra application logic
• Must iterate over product IDs in
the order document and find the
product documents
• RDBMS would automate through
a JOIN
orders
{
"_id": 12345,
"productName": "laptop",
"unitPrice": 1000,
"weight": 1.2,
"remainingStock": 23
}
{
"_id": 54321,
"productName": "mouse",
"unitPrice": 20,
"weight": 0.2,
"remainingStock": 276
}
products](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-7-2048.jpg)
![Aggregation Pipeline
$match $project $lookup
{★ds}
{★ds}
{★ds}
{★ds}
{★ds}
{★ds}
{★ds}
{★ds}
{★ds}
{★ds}
{★ds} {}
{★ds}
{★ds}
{★ds}
{★}
{★}
{★}
{★}
{★}
{★}
{★}
{=d+s}
{★[]}
{★[]}
{★}](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-15-2048.jpg)
![Aggregation Pipeline
$match $project $lookup $group
{★ds}
{★ds}
{★ds}
{★ds}
{★ds}
{★ds}
{★ds}
{★ds}
{★ds}
{★ds}
{★ds} {}
{★ds}
{★ds}
{★ds}
{★}
{★}
{★}
{★}
{★}
{★}
{★}
{=d+s}
{
Σ λ σ}
{
Σ λ σ}
{
Σ λ σ}
{★[]}
{★[]}
{★}](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-16-2048.jpg)
![$lookup
db.leftCollection.aggregate(
[{
$lookup:
{
from: “rightCollection”,
localField: “leftVal”,
foreignField: “rightVal”,
as: “embeddedData”
}
}])
leftCollection rightCollection](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-19-2048.jpg)
![Worked Example – Data Set
db.postcodes.findOne()
{
"_id": ObjectId("5600521e50fa77da54dfc0d2"),
"postcode": "SL6 0AA",
"location": {
"type": "Point",
"coordinates": [
51.525605,
-0.700974
]
}
}
db.homeSales.findOne()
{
"_id": ObjectId("56005dd980c3678b19792b7f"),
"amount": 9000,
"date": ISODate("1996-09-19T00:00:00Z"),
"address": {
"nameOrNumber": 25,
"street": "NORFOLK PARK COTTAGES",
"town": "MAIDENHEAD",
"county": "WINDSOR AND MAIDENHEAD",
"postcode": "SL6 7DR"
}
}](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-21-2048.jpg)
![Reduce Data Set First
db.homeSales.aggregate([
{$match: {
amount: {$gte:3000000}}
}
])
…
{
"_id": ObjectId("56005dda80c3678b19799e52"),
"amount": 3000000,
"date": ISODate("2012-04-19T00:00:00Z"),
"address": {
"nameOrNumber": "TEMPLE FERRY PLACE",
"street": "MILL LANE",
"town": "MAIDENHEAD",
"county": "WINDSOR AND MAIDENHEAD",
"postcode": "SL6 5ND"
}
},…](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-22-2048.jpg)
![Join (left-outer-equi) Results With Second
Collection
db.homeSales.aggregate([
{$match: {
amount: {$gte:3000000}}
},
{$lookup: {
from: "postcodes",
localField:
"address.postcode",
foreignField: "postcode",
as: "postcode_docs"}
}
])
...
"county": "WINDSOR AND MAIDENHEAD",
"postcode": "SL6 5ND"
},
"postcode_docs": [
{
"_id": ObjectId("560053e280c3678b1978b293"),
"postcode": "SL6 5ND",
"location": {
"type": "Point",
"coordinates": [
51.549516,
-0.80702
]
}}]}, ...](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-23-2048.jpg)
![Refactor Each Resulting Document
...},
{$project: {
_id: 0,
saleDate: ”$date",
price: "$amount",
address: 1,
location:
{$arrayElemAt:
["$postcode_docs.location",
0]}}
])
{ "address": {
"nameOrNumber": "TEMPLE FERRY PLACE",
"street": "MILL LANE",
"town": "MAIDENHEAD",
"county": "WINDSOR AND MAIDENHEAD",
"postcode": "SL6 5ND"
},
"saleDate": ISODate("2012-04-19T00:00:00Z"),
"price": 3000000,
"location": {
"type": "Point",
"coordinates": [
51.549516,
-0.80702
]}},...](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-24-2048.jpg)
![Sort on Sale Price & Write to Collection
...},
{$sort:
{price: -1}},
{$out: "hotSpots"}
])
…{"address": {
"nameOrNumber": "2 - 3",
"street": "THE SWITCHBACK",
"town": "MAIDENHEAD",
"county": "WINDSOR AND MAIDENHEAD",
"postcode": "SL6 7RJ"
},
"saleDate": ISODate("1999-03-15T00:00:00Z"),
"price": 5425000,
"location": {
"type": "Point",
"coordinates": [
51.536848,
-0.735835
]}},...](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-25-2048.jpg)
![Aggregated Statistics
db.homeSales.aggregate([
{$group:
{ _id:
{$year: "$date"},
higestPrice:
{$max: "$amount"},
lowestPrice:
{$min: "$amount"},
averagePrice:
{$avg: "$amount"},
amountStdDev:
{$stdDevPop: "$amount"}
}}
])
...
{
"_id": 1995,
"higestPrice": 1000000,
"lowestPrice": 12000,
"averagePrice": 114059.35206869633,
"amountStdDev": 81540.50490801703
},
{
"_id": 1996,
"higestPrice": 975000,
"lowestPrice": 9000,
"averagePrice": 118862,
"amountStdDev": 79871.07569783277
}, ...](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-26-2048.jpg)
![Clean Up Output
...,
{$project:
{
_id: 0,
year: "$_id",
higestPrice: 1,
lowestPrice: 1,
averagePrice:
{$trunc: "$averagePrice"},
priceStdDev:
{$trunc: "$amountStdDev"}
}
}
])
...
{
"higestPrice": 1000000,
"lowestPrice": 12000,
"averagePrice": 114059,
"year": 1995,
"priceStdDev": 81540
},
{
"higestPrice": 2200000,
"lowestPrice": 10500,
"averagePrice": 307372,
"year": 2004,
"priceStdDev": 199643
},...](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-27-2048.jpg)
![Postal Code & Location for Each Year’s
Highest Priced Sale
{$project: {
_id: 0,
Year: "$_id",
PostCode:
"$priciestPostCode",
Location:{$arrayElemAt: [
"$locationData.location”,
0]}
}
}
])
...
{
"Year": 2014,
"PostCode": "SL6 1UP",
"Location”: {
"type": "Point",
"coordinates": [
51.51407,
-0.704414
]
}
},
...](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-29-2048.jpg)
![Aggregation Options
db.cData.aggregate([
<pipeline stages>
],
{
'allowDiskUse': true,
'cursor’:
{
'batchSize': 5
}
}
)
• explain
– Information on execution plan
• allowDiskUse
– Enable use of disk to store
intermediate results
• cursor.batchsize
– Specify the size of the initial
result set](https://image.slidesharecdn.com/joinsandotheraggregationenhancements-151130171436-lva1-app6891/75/Joins-and-Other-MongoDB-3-2-Aggregation-Enhancements-30-2048.jpg)
Uploaded byAndrew Morgan
Joins and Other MongoDB 3.2 Aggregation Enhancements
The document outlines the enhancements introduced in MongoDB 3.2, particularly focusing on the aggregation pipeline and the new $lookup operator for performing left outer joins. It discusses various methods of data modeling in comparison to relational databases, and provides practical examples of using the aggregation framework for data analysis. Additionally, it addresses the limitations and considerations for using features like $lookup and $out in sharded environments.
More Related Content
Similar to Joins and Other MongoDB 3.2 Aggregation Enhancements
Joins and Other MongoDB 3.2 Aggregation Enhancements
- 1. MongoDB 3.2 –$lookup and OtherAggregation Enhancements AndrewMorgan @clusterdb clusterdb.com [email protected] 17rd November2015
- 2. DISCLAIMER: MongoDB's product plansare for informational purposes only. MongoDB's plans may change and you should not rely on them for delivery of a specific feature at a specific time.
- 4. Agenda Document vs. RelationalModel Analytics on MongoDB data 60,000 feet – what is the aggregation pipeline Aggregation pipeline operators $lookup (Left Outer Equi Joins) in MongoDB 3.2 Other aggregation enhancements Worked examples
- 5. Document vs. RelationalModel RDBMS MongoDB { _id: ObjectId("4c4ba5e5e8aabf3"), employee_name: {First: "Billy", Last: "Fish"}, department: "Engineering", title: "Aquarium design", pay_band: "C", benefits: [ { type: "Health", plan: "PPO Plus" }, { type: "Dental", plan: "Standard" } ] }
- 6. Existing Alternatives toJoins { "_id": 10000, "items": [ { "productName": "laptop", "unitPrice": 1000, "weight": 1.2, "remainingStock": 23 }, { "productName": "mouse", "unitPrice": 20, "weight": 0.2, "remainingStock": 276 } ], … } • Option 1: Include all data for an order in the same document – Fast reads • One find delivers all the required data – Captures full description at the time of the event – Consumes extra space • Details of each product stored in many order documents – Complex to maintain • A change to any product attribute must be propagated to all affected orders orders
- 7. Existing Alternatives toJoins { "_id": 10000, "items": [ 12345, 54321 ], ... } • Option 2: Order document references product documents – Slower reads • Multiple trips to the database – Space efficient • Product details stored once – Lose point-in-time snapshot of full record – Extra application logic • Must iterate over product IDs in the order document and find the product documents • RDBMS would automate through a JOIN orders { "_id": 12345, "productName": "laptop", "unitPrice": 1000, "weight": 1.2, "remainingStock": 23 } { "_id": 54321, "productName": "mouse", "unitPrice": 20, "weight": 0.2, "remainingStock": 276 } products
- 8. The Winner? • Ingeneral, Option 1 wins – Performance and containment of everything in same place beats space efficiency of normalization – There are exceptions • e.g. Comments in a blog post -> unbounded size • However, analytics benefit from combining data from multiple collections – Keep listening...
- 9.
- 10.
- 11.
- 12. Aggregation Pipeline $match $project {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds}{} {★ds} {★ds} {★ds} {=d+s}
- 13. Aggregation Pipeline $match $project {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds}{} {★ds} {★ds} {★ds} {★} {★} {★} {=d+s}
- 14. Aggregation Pipeline $match $project$lookup {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {} {★ds} {★ds} {★ds} {★} {★} {★} {★} {★} {★} {★} {=d+s}
- 15. Aggregation Pipeline $match $project$lookup {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {} {★ds} {★ds} {★ds} {★} {★} {★} {★} {★} {★} {★} {=d+s} {★[]} {★[]} {★}
- 16. Aggregation Pipeline $match $project$lookup $group {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {★ds} {} {★ds} {★ds} {★ds} {★} {★} {★} {★} {★} {★} {★} {=d+s} { Σ λ σ} { Σ λ σ} { Σ λ σ} {★[]} {★[]} {★}
- 17. Aggregation Pipeline Stages •$match Filter documents • $geoNear Geospherical query • $project Reshape documents • $lookup New – Left-outer equi joins • $unwind Expand documents • $group Summarize documents • $sample New – Randomly selects a subset of documents • $sort Order documents • $skip Jump over a number of documents • $limit Limit number of documents • $redact Restrict documents • $out Sends results to a new collection
- 18. $lookup • Left-outer join –Includes all documents from the left collection – For each document in the left collection, find the matching documents from the right collection and embed them Left Collection Right Collection
- 19. $lookup db.leftCollection.aggregate( [{ $lookup: { from: “rightCollection”, localField: “leftVal”, foreignField:“rightVal”, as: “embeddedData” } }]) leftCollection rightCollection
- 20. New Aggregation Operators •Array operations – $slice, $arrayElemAt, $concatArrays, $isArray, $filter, $min, $max, $avg and $sum • Standard Deviations – $stdDevSamp (sample) and $stdDevPop (complete) • Square Root – $sqrt • Absolute (make +ve) value – $abs • Rounding numbers – $trunc, $ceil, $floor • Logarithms – $log, $log10, $ln • Raise to power – $pow • Natural Exponent – $exp
- 21. Worked Example –Data Set db.postcodes.findOne() { "_id": ObjectId("5600521e50fa77da54dfc0d2"), "postcode": "SL6 0AA", "location": { "type": "Point", "coordinates": [ 51.525605, -0.700974 ] } } db.homeSales.findOne() { "_id": ObjectId("56005dd980c3678b19792b7f"), "amount": 9000, "date": ISODate("1996-09-19T00:00:00Z"), "address": { "nameOrNumber": 25, "street": "NORFOLK PARK COTTAGES", "town": "MAIDENHEAD", "county": "WINDSOR AND MAIDENHEAD", "postcode": "SL6 7DR" } }
- 22. Reduce Data SetFirst db.homeSales.aggregate([ {$match: { amount: {$gte:3000000}} } ]) … { "_id": ObjectId("56005dda80c3678b19799e52"), "amount": 3000000, "date": ISODate("2012-04-19T00:00:00Z"), "address": { "nameOrNumber": "TEMPLE FERRY PLACE", "street": "MILL LANE", "town": "MAIDENHEAD", "county": "WINDSOR AND MAIDENHEAD", "postcode": "SL6 5ND" } },…
- 23. Join (left-outer-equi) ResultsWith Second Collection db.homeSales.aggregate([ {$match: { amount: {$gte:3000000}} }, {$lookup: { from: "postcodes", localField: "address.postcode", foreignField: "postcode", as: "postcode_docs"} } ]) ... "county": "WINDSOR AND MAIDENHEAD", "postcode": "SL6 5ND" }, "postcode_docs": [ { "_id": ObjectId("560053e280c3678b1978b293"), "postcode": "SL6 5ND", "location": { "type": "Point", "coordinates": [ 51.549516, -0.80702 ] }}]}, ...
- 24. Refactor Each ResultingDocument ...}, {$project: { _id: 0, saleDate: ”$date", price: "$amount", address: 1, location: {$arrayElemAt: ["$postcode_docs.location", 0]}} ]) { "address": { "nameOrNumber": "TEMPLE FERRY PLACE", "street": "MILL LANE", "town": "MAIDENHEAD", "county": "WINDSOR AND MAIDENHEAD", "postcode": "SL6 5ND" }, "saleDate": ISODate("2012-04-19T00:00:00Z"), "price": 3000000, "location": { "type": "Point", "coordinates": [ 51.549516, -0.80702 ]}},...
- 25. Sort on SalePrice & Write to Collection ...}, {$sort: {price: -1}}, {$out: "hotSpots"} ]) …{"address": { "nameOrNumber": "2 - 3", "street": "THE SWITCHBACK", "town": "MAIDENHEAD", "county": "WINDSOR AND MAIDENHEAD", "postcode": "SL6 7RJ" }, "saleDate": ISODate("1999-03-15T00:00:00Z"), "price": 5425000, "location": { "type": "Point", "coordinates": [ 51.536848, -0.735835 ]}},...
- 26. Aggregated Statistics db.homeSales.aggregate([ {$group: { _id: {$year:"$date"}, higestPrice: {$max: "$amount"}, lowestPrice: {$min: "$amount"}, averagePrice: {$avg: "$amount"}, amountStdDev: {$stdDevPop: "$amount"} }} ]) ... { "_id": 1995, "higestPrice": 1000000, "lowestPrice": 12000, "averagePrice": 114059.35206869633, "amountStdDev": 81540.50490801703 }, { "_id": 1996, "higestPrice": 975000, "lowestPrice": 9000, "averagePrice": 118862, "amountStdDev": 79871.07569783277 }, ...
- 27. Clean Up Output ..., {$project: { _id:0, year: "$_id", higestPrice: 1, lowestPrice: 1, averagePrice: {$trunc: "$averagePrice"}, priceStdDev: {$trunc: "$amountStdDev"} } } ]) ... { "higestPrice": 1000000, "lowestPrice": 12000, "averagePrice": 114059, "year": 1995, "priceStdDev": 81540 }, { "higestPrice": 2200000, "lowestPrice": 10500, "averagePrice": 307372, "year": 2004, "priceStdDev": 199643 },...
- 28. Postal Code &Location for Each Year’s Highest Priced Sale db.homeSales.aggregate([ {$sort: {amount: -1}}, {$group: { _id: {$year: "$date"}, priciestPostCode: {$first: "$address.postcode"} } }, {$lookup: { from: "postcodes", localField: "priciestPostCode", foreignField: "postcode", as: "locationData" } }, {$sort: {_id: -1}},
- 29. Postal Code &Location for Each Year’s Highest Priced Sale {$project: { _id: 0, Year: "$_id", PostCode: "$priciestPostCode", Location:{$arrayElemAt: [ "$locationData.location”, 0]} } } ]) ... { "Year": 2014, "PostCode": "SL6 1UP", "Location”: { "type": "Point", "coordinates": [ 51.51407, -0.704414 ] } }, ...
- 30. Aggregation Options db.cData.aggregate([ <pipeline stages> ], { 'allowDiskUse':true, 'cursor’: { 'batchSize': 5 } } ) • explain – Information on execution plan • allowDiskUse – Enable use of disk to store intermediate results • cursor.batchsize – Specify the size of the initial result set
- 31. Aggregation With aSharded Database • Workload split between shards – Client works through mongos as with any query – Shards execute pipeline up to a point – A single shard merges cursors and continues processing – Use explain to analyze pipeline split – Early $match on shard key may exclude shards – Potential CPU and memory implications for primary shard host – $lookup & $out performed within Primary shard for the database ?
- 32. Tableau + MongoDBConnector for BI
- 33. Restrictions • $lookup onlysupport equality for the match • $lookup can only be used in the aggregation pipeline (e.g. not for find) • The pipeline is linear; no forks. Can remove data at each stage and can only add new raw data through $lookup • Right collection for $lookup cannot be sharded • Indexes are only used at the beginning of the pipeline (and right tables in subsequent $lookups), before any data transformations • $out can only be used in the final stage of the pipeline • $geoNear can only be the first stage in the pipeline • The BI Connector for MongoDB is part of MongoDB Enterprise Advanced – Not in community
- 34. Next Steps • Documentation –https://docs.mongodb.org/manual/release-notes/3.2/#aggregation-framework-enhancements • Not yet ready for production but download and try! – https://www.mongodb.org/downloads#development • Detailed blog – https://www.mongodb.com/blog/post/joins-and-other-aggregation-enhancements-coming-in-mongodb-3-2- part-1-of-3-introduction • Webinars – Tomorrow: What's New in MongoDB 3.2 https://www.mongodb.com/webinar/whats-new-in-mongodb-3-2 – Replay: 3.2 $lookup & aggregation https://www.mongodb.com/presentations/webinar-joins-and-other- aggregation-enhancements-coming-in-mongodb-3-2 • Feedback – MongoDB 3.2 Bug Hunt • https://www.mongodb.com/blog/post/announcing-the-mongodb-3-2-bug-hunt – https://jira.mongodb.org/ DISCLAIMER: MongoDB's product plans are for informational purposes only. MongoDB's plans may change and you should not rely on them for delivery of a specific feature at a specific time.
- 35. MongoDB Days 2015 October6, 2015 October 20, 2015 November 5, 2015 December 2, 2015 France Germany UK Silicon Valley