$addFields and how it is similar to $project
// reassign ``gravity`` field value
db.solarSystem.aggregate([{"$project": { "gravity": "$gravity.value" } }]);
// adding ``name`` and removing ``_id`` from projection
db.solarSystem.aggregate([{"$project": { "_id": 0, "name": 1, "gravity": "$gravity.value" } }]);
// adding more fields to the projected document
db.solarSystem.aggregate([
{"$project":{
"_id": 0,
"name": 1,
"gravity": "$gravity.value",
"meanTemperature": 1,
"density": 1,
"mass": "$mass.value",
"radius": "$radius.value",
"sma": "$sma.value" }
}]);
// using ``$addFields`` to generate the new computed field values
db.solarSystem.aggregate([
{"$addFields":{
"gravity": "$gravity.value",
"mass": "$mass.value",
"radius": "$radius.value",
"sma": "$sma.value"}
}]);
// combining ``$project`` with ``$addFields``
db.solarSystem.aggregate([
{"$project": {
"_id": 0,
"name": 1,
"gravity": 1,
"mass": 1,
"radius": 1,
"sma": 1}
},
{"$addFields": {
"gravity": "$gravity.value",
"mass": "$mass.value",
"radius": "$radius.value",
"sma": "$sma.value"
}}]);
geoNear Stage
geoNear must be first stage of the pipeline.
// using ``$geoNear`` stage
db.nycFacilities.aggregate([
{
"$geoNear": {
"near": {
"type": "Point",
"coordinates": [-73.98769766092299, 40.757345233626594]
},
"distanceField": "distanceFromMongoDB",
"spherical": true
}
}
]).pretty();
// include ``limit`` to results
db.nycFacilities.aggregate([
{
$geoNear: {
near: {
type: "Point",
coordinates: [-73.98769766092299, 40.757345233626594]
},
distanceField: "distanceFromMongoDB",
spherical: true,
query: { type: "Hospital" },
}
},
{
$limit: 5
}
]).pretty()
Cursor-like stages
// project fields ``numberOfMoons`` and ``name``
db.solarSystem.find({}, {"_id": 0, "name": 1, "numberOfMoons": 1}).pretty();
// count the number of documents
db.solarSystem.find({}, {"_id": 0, "name": 1, "numberOfMoons": 1}).count();
// skip documents
db.solarSystem.find({}, {"_id": 0, "name": 1, "numberOfMoons": 1}).skip(5).pretty();
// limit documents
db.solarSystem.find({}, {"_id": 0, "name": 1, "numberOfMoons": 1}).limit(5).pretty();
// sort documents
db.solarSystem.find({}, { "_id": 0, "name": 1, "numberOfMoons": 1 }).sort( {"numberOfMoons": -1 } ).pretty();
// ``$limit`` stage
db.solarSystem.aggregate([{
"$project": {
"_id": 0,
"name": 1,
"numberOfMoons": 1
}
},
{ "$limit": 5 }]).pretty();
// ``skip`` stage
db.solarSystem.aggregate([{
"$project": {
"_id": 0,
"name": 1,
"numberOfMoons": 1
}
}, {
"$skip": 1
}]).pretty()
// ``$count`` stage
db.solarSystem.aggregate([{
"$match": {
"type": "Terrestrial planet"
}
}, {
"$project": {
"_id": 0,
"name": 1,
"numberOfMoons": 1
}
}, {
"$count": "terrestrial planets"
}]).pretty();
// removing ``$project`` stage since it does not interfere with our count
db.solarSystem.aggregate([{
"$match": {
"type": "Terrestrial planet"
}
}, {
"$count": "terrestrial planets"
}]).pretty();
// ``$sort`` stage
db.solarSystem.aggregate([{
"$project": {
"_id": 0,
"name": 1,
"numberOfMoons": 1
}
}, {
"$sort": { "numberOfMoons": -1 }
}]).pretty();
// sorting on more than one field
db.solarSystem.aggregate([{
"$project": {
"_id": 0,
"name": 1,
"hasMagneticField": 1,
"numberOfMoons": 1
}
}, {
"$sort": { "hasMagneticField": -1, "numberOfMoons": -1 }
}]).pretty();
// setting ``allowDiskUse`` option
db.solarSystem.aggregate([{
"$project": {
"_id": 0,
"name": 1,
"hasMagneticField": 1,
"numberOfMoons": 1
}
}, {
"$sort": { "hasMagneticField": -1, "numberOfMoons": -1 }
}], { "allowDiskUse": true }).pretty();
Another example
db.customers.aggregate({$project: { _id:0, accountType:1 }}, {$group: {_id: "$accountType", "count": {$sum: 1}}}, {$sort: {"count": -1}}, {$limit: 3})
$sample Stage
// sampling 200 documents of collection ``nycFacilities``
db.nycFacilities.aggregate([{"$sample": { "size": 200 }}]).pretty();
Lab: Using Cursor-like Stages
Problem:
MongoDB has another movie night scheduled. This time, we polled employees for their favorite actress or actor, and got these results
favorites = [
"Sandra Bullock",
"Tom Hanks",
"Julia Roberts",
"Kevin Spacey",
"George Clooney"]
For movies released in the USA with a tomatoes.viewer.rating greater than or equal to 3, calculate a new field called num_favs that represets how many favorites appear in the cast field of the movie.
Sort your results by num_favs, tomatoes.viewer.rating, and title, all in descending order.
What is the title of the 25th film in the aggregation result?
var favorites = [
"Sandra Bullock",
"Tom Hanks",
"Julia Roberts",
"Kevin Spacey",
"George Clooney"]
db.movies.aggregate([
{
$match: {
"tomatoes.viewer.rating": { $gte: 3 },
countries: "USA",
cast: {
$in: favorites
}
}
},
{
$project: {
_id: 0,
title: 1,
"tomatoes.viewer.rating": 1,
num_favs: {
$size: {
$setIntersection: [
"$cast",
favorites
]
}
}
}
},
{
$sort: { num_favs: -1, "tomatoes.viewer.rating": -1, title: -1 }
},
{
$skip: 24
},
{
$limit: 1
}
])
Lab - Bringing it all together
Problem:
Calculate an average rating for each movie in our collection where English is an available language, the minimum imdb.rating is at least 1, the minimum imdb.votes is at least 1, and it was released in 1990 or after. You’ll be required to rescale (or normalize) imdb.votes. The formula to rescale imdb.votes and calculate normalized_rating is included as a handout.
What film has the lowest normalized_rating?
db.movies.aggregate([
{
$match: {
year: { $gte: 1990 },
languages: { $in: ["English"] },
"imdb.votes": { $gte: 1 },
"imdb.rating": { $gte: 1 }
}
},
{
$project: {
_id: 0,
title: 1,
"imdb.rating": 1,
"imdb.votes": 1,
normalized_rating: {
$avg: [
"$imdb.rating",
{
$add: [
1,
{
$multiply: [
9,
{
$divide: [
{ $subtract: ["$imdb.votes", 5] },
{ $subtract: [1521105, 5] }
]
}
]
}
]
}
]
}
}
},
{ $sort: { normalized_rating: 1 } },
{ $limit: 1 }
])