Microsoft PowerPoint – DS9
Discussion Session on
MongoDB
Amin Javari
Aggregation
• A MongoDB aggregation is a series of operators applied to a collection or a
set of collections
• Some operations in aggregation
• Project
• Lookup
• Match
• Group
• Sort
• Limit
• Unwind
• Skip
• Count
$lookup
Yelp Database
• Reviews
• Businesses
Project
db.review.aggregate([
{ $project: {
useful: 0, // eliminate from the output
ratings: “$stars” // use rating as source
} }]);
db.business.aggregate(
{
$group : {_id : “$state”, total : { $sum : 1 }}
}
);
SELECT id, stars AS ratings FROM review
Group
db.business.aggregate(
{
$group : {_id : “$state”, total : { $sum : 1 }}
}
);
SELECT state, SUM(state) AS total FROM
business
GROUP BY state
Group and Project
db.reviews.aggregate([
{ $group: {
_id: “$user_id”,
nbReviews: { $sum: 1 } } },
{ $project: {
_id: 0, reviewerId: “$_id”,
nbReviews: 1 } } ]);
SELECT user_id AS reviwerId, COUNT(*) AS nbReviews
FROM reviews
GROUP BY user_id;
Group and sort
db.business.aggregate(
{
$group : {_id : “$state”, total : { $sum : 1 }}
},
{ $sort : {total : -1} } );
SELECT state, SUM(state) AS total FROM
business
GROUP BY state
Avg, max, sum
db.reviews.aggregate([
{ $group: {
_id: “$user_id”,
avgScore: { $avg: “$score” },
maxScore: { $max: “$score” },
nbReviews: { $sum: 1 }
} } ]);
SELECT user_id,
AVG(score) as avg_score,
MAX(score) as max_score,
COUNT(*) as nb_reviews
FROM review
GROUP BY user_id ;
Group and Match
db.reviews.aggregate([
{ $match : {
date: { $gte: new Date(“2012-07-11”) }
} }, { $group: {
_id: “$user_id”,
avgScore: { $avg: “$score” }
} } ]);
SELECT user_id, AVG(score)
FROM review
WHERE review.date > “2012-07-11”
GROUP BY user_id ;
Match and group
db.reviews.aggregate([
{ $group: {
_id: “$businessId”,
avgScore: { $avg: “$score” }
} },
{ $match : {
avgScore: { $gt: 3 } } } ]);
SELECT business_id, AVG(score) AS avg_score
FROM review
GROUP BY businessId
HAVING avg_score > 3;
Unwind
db.business.aggregate([
{ $unwind: “$categories” }
]);
db.business.aggregate([
{ $unwind: “$categories” },
{ $group: { _id: “$categories”, nbBusiness: { $sum: 1 } } } ]);
db.business.aggregate([
Left Join in Sql
Unwind and addToSet
db.business.aggregate([
{ $unwind: “$categories” },
{ $group: {
_id: “$businessid”,
businesses: { $addToSet: “$_id” } } } ]);
Left Join in Sql
Find and Match
var list = db.business.find({“state”:“GA”},{business_id:1})
var alist = new Array();
while(list.hasNext()){ alist.push(list.next().business_id); }
db.review.aggregate([
{$match:{business_id:{$in:alist}}},
])
Lookup
db.business.aggregate([
{
$lookup:
{
from: “reviews”,
localField: “businessid”,
foreignField: “businessid”,
as: “business-review”
}
}
])
Match, unwind, group, match
db..aggregate([
{ $match: { “lattitute”: {“$lt”: 33.5059283 }}},
{ $unwind: “$categories”},
{ $group: { “_id”: “$categories”,
“n”: {$sum: 1},
“who”: {$push: “$businessid”},
}},
{ $match: { “n”: {“$gt”: 1}} },
{ $sort: { “n”: -1, “_id”: 1} }
]);
Questions?