How do I create a MongoDB aggregate to lookup and add fields using ObjectIds in array objects

Question

Using Mongo 4.4

I'm looking to to lookups across collections and add a human readable value from the target collection to the source collection using a aggregate.

This works fine for individual values, but for some lookups the ObjectIds are in objects in arrays, and I can't get that work. I can pull all the values back, but not place the individual values in the array objects.

In this test case, I have a library database with a books collection and a subscribers collection. The subscribers have a checkouts entry with is an array of objects, containing a reference to a book, and the checkout date. I want to add the book title to each object in the array.

Test Database:

books collection:

[
  {
    "_id" : ObjectId("63208c9f0d97eff0cfbefde6"),
    "title" : "There and back again",
    "author" : "Bilbo Baggins",
    "publisher" : "Middle Earth Books"
  },
  {
    "_id" : ObjectId("63208cd10d97eff0cfbeff02"),
    "title" : "Two Towers",
    "author" : "JRR Tolkin",
    "publisher" : "Dude Books"
  },
  {
    "_id" : ObjectId("63208cf10d97eff0cfbeffa3"),
    "title" : "Dune",
    "author" : "Frank Herbert",
    "publisher" : "Classic Books"
  },
  {
    "_id" : ObjectId("63208d1d0d97eff0cfbf0087"),
    "title" : "Old Man's War",
    "author" : "John Scalzi",
    "publisher" : "Old Man Books"
  }
]

subscribers collection:

[
  {
    "_id" : ObjectId("63208c2e0d97eff0cfbefb46"),
    "name" : "Tom",
    "checkouts" : [ 
        {
            "bookId" : ObjectId("63208cd10d97eff0cfbeff02"),
            "checkoutDate" : ISODate("2022-01-01T21:21:20.202Z")
        }, 
        {
            "bookId" : ObjectId("63208d1d0d97eff0cfbf0087"),
            "checkoutDate" : ISODate("2022-01-02T21:22:20.202Z")
        }
    ],
    "address" : "123 Somewhere"
  },
  {
    "_id" : ObjectId("63208c4e0d97eff0cfbefc1f"),
    "name" : "Bob",
    "checkouts" : [],
    "address" : "123 Somewhere"
  }, 
  {
    "_id" : ObjectId("63208c640d97eff0cfbefc9a"),
    "name" : "Mary",
    "checkouts" : [],
    "address" : "123 Somewhere Else"
  }

Desired Output for user Tom:

{
    "_id" : ObjectId("63208c2e0d97eff0cfbefb46"),
    "name" : "Tom",
    "checkouts" : [ 
        {
            "bookId" : ObjectId("63208cd10d97eff0cfbeff02"),
            "checkoutDate" : ISODate("2022-01-01T21:21:20.202Z"),
            "title" :  "Two Towers"
        }, 
        {
            "bookId" : ObjectId("63208d1d0d97eff0cfbf0087"),
            "checkoutDate" : ISODate("2022-01-02T21:22:20.202Z"),
            "title" : "Old Man's War"
        }
    ],
    "address" : "123 Somewhere",
}

Using this aggregate:

db.getCollection('subscribers').aggregate([
  {$match: {_id: ObjectId("63208c2e0d97eff0cfbefb46") } },
  {$lookup: {from: "books", localField: "checkouts.bookId", foreignField: "_id", as: "book_tmp_field" }},
  {$addFields: { "checkouts.title": "$book_tmp_field.title"}},
  {$project: { book_tmp_field: 0}}

])

This is the closest I can get:

{
    "_id" : ObjectId("63208c2e0d97eff0cfbefb46"),
    "name" : "Tom",
    "checkouts" : [ 
        {
            "bookId" : ObjectId("63208cd10d97eff0cfbeff02"),
            "checkoutDate" : ISODate("2022-01-01T21:21:20.202Z"),
            "title" : [ 
                "Two Towers", 
                "Old Man's War"
            ]
        }, 
        {
            "bookId" : ObjectId("63208d1d0d97eff0cfbf0087"),
            "checkoutDate" : ISODate("2022-01-02T21:22:20.202Z"),
            "title" : [ 
                "Two Towers", 
                "Old Man's War"
            ]
        }
    ],
    "address" : "123 Somewhere"
}

Answer 1

Before performing the lookup, you should UNWIND the checkouts array. After all the processing is done, group the documents, to obtain the checkouts in the array. Finally, project your desired output document. Like this:

db.subscribers.aggregate([
  {
    $match: {
      _id: ObjectId("63208c2e0d97eff0cfbefb46")
    }
  },
  {
    "$unwind": "$checkouts"
  },
  {
    $lookup: {
      from: "books",
      localField: "checkouts.bookId",
      foreignField: "_id",
      as: "book_tmp_field"
    }
  },
  {
    $addFields: {
      "checkouts.title": "$book_tmp_field.title"
    }
  },
  {
    $project: {
      book_tmp_field: 0
    }
  },
  {
    "$group": {
      "_id": {
        _id: "$_id",
        address: "$address",
        name: "$name"
      },
      "checkouts": {
        "$push": "$checkouts"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$_id",
          {
            checkouts: "$checkouts"
          }
        ]
      }
    }
  }
])

Here's the playground link.