Gallery
Share
Explore

Lab Workbook: Join Collections

Learning outcomes:

Create JSON Schema to make a join of collections analogous to predicate join in SQL using the example of Singers and Albums

Suppose we have two collections: Singers and Albums.

The Singers collection has documents with the following schema:


{
"_id": ObjectId,
"name": String,
"gender": String,
"age": Number,
"band": String
}


The Albums collection has documents with the following schema:
{
"_id": ObjectId,
"title": String,
"year": Number,
"genre": String,
"singer_id": ObjectId
}


To join these collections, we can use the $lookup aggregation pipeline stage in MongoDB. Here's an example of how to do this in the MongoDB shell:

db.singers.aggregate([
{
$lookup: {
from: "albums",
localField: "_id",
foreignField: "singer_id",
as: "albums"
}
}
])


This will return documents from the Singers collection, each with an additional albums field that is an array of all the matching documents from the Albums collection.


Here's an example of what the output might look like:

[
{
"_id": ObjectId("60c8d48dcf1a3dcee3d89759"),
"name": "John Lennon",
"gender": "male",
"age": 40,
"band": "The Beatles",
"albums": [
{
"_id": ObjectId("60c8d4d8cf1a3dcee3d8975a"),
"title": "Imagine",
"year": 1971,
"genre": "rock",
"singer_id": ObjectId("60c8d48dcf1a3dcee3d89759")
},
{
"_id": ObjectId("60c8d4e7cf1a3dcee3d8975b"),
"title": "Mind Games",
"year": 1973,
"genre": "rock",
"singer_id": ObjectId("60c8d48dcf1a3dcee3d89759")
}
]
},
{
"_id": ObjectId("60c8d4f0cf1a3dcee3d8975c"),
"name": "Paul McCartney",
"gender": "male",
"age": 78,
"band": "The Beatles",
"albums": [
{
"_id": ObjectId("60c8d502cf1a3dcee3d8975d"),
"title": "Ram",
"year": 1971,
"genre": "rock",
"singer_id": ObjectId("60c8d4f0cf1a3dcee3d8975c")
},
{
"_id": ObjectId("60c8d50fcf1a3dcee3d8975e"),
"title": "Band on the Run",
"year": 1973,
"genre": "rock",
"singer_id": ObjectId("60c8d4f0cf1a3dcee3d8975c")
}
]
}
]


In this example, we have two singers, John Lennon and Paul McCartney, and each has multiple albums. The albums field for each singer contains an array of all the matching documents from the Albums collection, where the singer_id field in the Albums collection matches the _id field in the Singers collection.


student lab work for JSON Schema showing how to make a join of collections analogous to predicate join in SQL using the example of Singers and Albums:


Suppose we have two collections: Singers and Albums. The Singers collection has documents with the following schema:


{
"_id": ObjectId,
"name": String,
"gender": String,
"age": Number,
"band": String
}



The Albums collection has documents with the following schema:


{
"_id": ObjectId,
"title": String,
"year": Number,
"genre": String,
"singer_id": ObjectId
}



To join these collections, we can use the $lookup aggregation pipeline stage in MongoDB. Here's an example of how to do this in the MongoDB shell:


db.singers.aggregate([
{
$lookup: {
from: "albums",
localField: "_id",
foreignField: "singer_id",
as: "albums"
}
}
])



This will return documents from the Singers collection, each with an additional albums field that is an array of all the matching documents from the Albums collection.


Here's an example of what the output might look like:


[
{
"_id": ObjectId("60c8d48dcf1a3dcee3d89759"),
"name": "John Lennon",
"gender": "male",
"age": 40,
"band": "The Beatles",
"albums": [
{
"_id": ObjectId("60c8d4d8cf1a3dcee3d8975a"),
"title": "Imagine",
"year": 1971,
"genre": "rock",
"singer_id": ObjectId("60c8d48dcf1a3dcee3d89759")
},
{
"_id": ObjectId("60c8d4e7cf1a3dcee3d8975b"),
"title": "Mind Games",
"year": 1973,
"genre": "rock",
"singer_id": ObjectId("60c8d48dcf1a3dcee3d89759")
}
]
},
{
"_id": ObjectId("60c8d4f0cf1a3dcee3d8975c"),
"name": "Paul McCartney",
"gender": "male",
"age": 78,
"band": "The Beatles",
"albums": [
{
"_id": ObjectId("60c8d502cf1a3dcee3d8975d"),
"title": "Ram",
"year": 1971,
"genre": "rock",
"singer_id": ObjectId("60c8d4f0cf1a3dcee3d8975c")
},
{
"_id": ObjectId("60c8d50fcf1a3dcee3d8975e"),
"title": "Band on the Run",
"year": 1973,
"genre": "rock",
"singer_id": ObjectId("60c8d4f0cf1a3dcee3d8975c")
}
]
}
]



In this example, we have two singers, John Lennon and Paul McCartney, and each has multiple albums. The albums field for each singer contains an array of all the matching documents from the Albums collection, where the singer_id field in the Albums collection matches the _id field in the Singers collection.

Extend the above lab to include 5 singers and their albums. Use Spanish Female Latin Pop Singers like Shakira, Thalia, Paulina Rubio, and others in the cohort

Here's an example of how to extend the previous lab to include 5 Spanish female Latin pop singers and their albums:
// Insert sample data for singers
db.singers.insertMany([
{
"name": "Shakira",
"gender": "female",
"age": 44,
"band": null
},
{
"name": "Thalía",
"gender": "female",
"age": 49,
"band": null
},
{
"name": "Paulina Rubio",
"gender": "female",
"age": 50,
"band": null
},
{
"name": "Gloria Trevi",
"gender": "female",
"age": 53,
"band": null
},
{
"name": "Natalia Lafourcade",
"gender": "female",
"age": 37,
"band": null
}
]);

// Insert sample data for albums
db.albums.insertMany([
{
"title": "Pies Descalzos",
"year": 1995,
"genre": "Latin pop",
"singer_id": ObjectId("60c8e3b2143f8f4e4e4d4c3b")
},
{
"title": "Dónde Están los Ladrones?",
"year": 1998,
"genre": "Latin pop",
"singer_id": ObjectId("60c8e3b2143f8f4e4e4d4c3b")
},
{
"title": "Laundry Service",
"year": 2001,
"genre": "Latin pop",
"singer_id": ObjectId("60c8e3b2143f8f4e4e4d4c3b")
},
{
"title": "El Sexto Sentido",
"year": 2005,
"genre": "Latin pop",
"singer_id": ObjectId("60c8e3d5143f8f4e4e4d4c3c")
},
{
"title": "Primera Fila",
"year": 2009,
"genre": "Pop",
"singer_id": ObjectId("60c8e3e2143f8f4e4e4d4c3d")
},
{
"title": "Habítame Siempre",
"year": 2012,
"genre": "Latin pop",
"singer_id": ObjectId("60c8e3e2143f8f4e4e4d4c3d")
},
{
"title": "Deseo",
"year": 2018,
"genre": "Latin pop",
"singer_id": ObjectId("60c8e3e2143f8f4e4e4d4c3d")
},
{
"title": "Pau-Latina",
"year": 2004,
"genre": "Latin pop",
"singer_id": ObjectId("60c8e3f3143f8f4e4e4d4c3e")
},
{
"title": "Ananda",
"year": 2006,
"genre": "Latin pop",
"singer_id": ObjectId("60c8e3f3143f8f4e4e4d4c3e")
},
{
"title": "Gran City Pop",
"year": 2009,
"genre": "Latin pop",
"singer_id": ObjectId("60c8e3f3143f8f4e4e4d4c3e")
},
{
"title": "Gloria",
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.