Lab Workbook: Join Collections
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",Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.