🦸‍♂️ Advanced lookups

info

Extra activity! Do it if you have extra time or are following along at home. It won't be covered during the hands-on lab.

We get this request: Write a $lookup to get name and bio from author's information inside each book document. To get this done, we need to review several things:

• Each book can have several authors. This many-to-many relationship (as an author can also write many books) is modeled using two different arrays: a books array in the authors collection and an authors array in the books collection.
• So we'll need to get a separate document for each book that has more than one author. If a book has three authors, we'll use $unwind to get three documents with the same data except for the author, which will be each of the three authors.

You can try this with this aggregation pipeline:

Atlas UI

[
  // as a book can have many authors, we get one doc per book's author
  {$unwind: "$authors"},
  // remove some noisy fields
  {$project: {attributes: 0, reviews: 0}}
]

MongoDB Shell

db.books.aggregate([
  // as a book can have many authors, we get one doc per book's author
  {$unwind: "$authors"},
  // remove some noisy fields
  {$project: {attributes: 0, reviews: 0}}
])

• Now, we need to get the authors' information. For that, we'll use $lookup, linking the _id in the authors collection with the _id we have in each book's authors array. But as we can see here, these have a different type: The ones inside our array are strings, while the author collection _id are ObjectId.

  authors: {
    _id: '64cc2db4830ba29148da64a2',
    name: 'Timothy Findley'
  },

So we need to convert from String into ObjectId. We can do that using $toObjectId. This will add a new field, authorId, converting it into ObjectId:

Atlas UI

[
  // as a book can have many authors, we get one doc per book's author
  {$unwind: "$authors"},
  // convert it to an objectId
  {"$set":{"authorId":{"$toObjectId":"$authors._id"}}},
  // remove some noisy fields
  {$project: {attributes: 0, reviews: 0}}
]

MongoDB Shell

db.books.aggregate([
  // as a book can have many authors, we get one doc per book's author
  {$unwind: "$authors"},
  // convert it to an objectId
  {"$set":{"authorId":{"$toObjectId":"$authors._id"}}},
  // remove some noisy fields
  {$project: {attributes: 0, reviews: 0}}
])

• Now, we're ready to do the $lookup: We want all documents from authors that have the same _id as the authorId we just created. We use a pipeline to get just authors name and bio.

Atlas UI

[
  // as a book can have many authors, we get one doc per book's author
  {$unwind: "$authors"},
  // convert it to an objectId
  {"$set":{"authorId":{"$toObjectId":"$authors._id"}}},
  {$lookup: {
      from: "authors",
      localField: "authorId",
      foreignField: "_id",
      pipeline: [
          {$project: {name: 1, bio: 1}},
      ],
      as: "bookAuthorDetails"
    } 
  },
  // remove some noisy fields
  {$project: {attributes: 0, reviews: 0}}
]

MongoDB Shell

db.books.aggregate([
  // as a book can have many authors, we get one doc per book's author
  {$unwind: "$authors"},
  // convert it to an objectId
  {"$set":{"authorId":{"$toObjectId":"$authors._id"}}},
  {$lookup: {
      from: "authors",
      localField: "authorId",
      foreignField: "_id",
      pipeline: [
          {$project: {name: 1, bio: 1}},
      ],
      as: "bookAuthorDetails"
    } 
  },
  // remove some noisy fields
  {$project: {attributes: 0, reviews: 0}}
])