Aggregate Populate array of ids with Their Documents

Question

I'm Strugling with some aggregation functions in mongodb. I want to get books Documents in author's document that has just books ids as array of strings ids like this :

Author Document

{
  "_id" : "10",
  "full_name" : "Joi Dark",
  "books" : ["100", "200", "351"],
}

And other documents (books) :

{
    "_id" : "100",
    "title" : "Node.js In Action",
    "ISBN" : "121215151515154",
    "date" : "2015-10-10"
}

So in result i want this :

{
  "_id" : "10",
  "full_name" : "Joi Dark",
  "books" : [
       {
           "_id" : "100",
           "title" : "Node.js In Action",
           "ISBN" : "121215151515154",
           "date" : "2015-10-10"
       },
       {
           "_id" : "200",
           "title" : "Book 2",
           "ISBN" : "1212151454515154",
           "date" : "2015-10-20"
       },
       {
           "_id" : "351",
           "title" : "Book 3",
           "ISBN" : "1212151454515154",
           "date" : "2015-11-20"
       }
  ],
}

Answer 1

Use $lookup which retrieves data from the nominated collection in from based on the matching of the localField to the foreignField:

db.authors.aggregate([
  { "$lookup": {
    "from": "$books",
    "foreignField": "_id",
    "localField": "books",
    "as": "books"
  }}
])

The as is where in the document to write an "array" containing the related documents. If you specify an existing property ( such as is done here ) then that property is overwritten with the new array content in output.

If you have a MongoDB before MongoDB 3.4 then you may need to $unwind the array of "books" as the localField first:

db.authors.aggregate([
  { "$unwind": "$books" },
  { "$lookup": {
    "from": "$books",
    "foreignField": "_id",
    "localField": "books",
    "as": "books"
  }}
])

Which creates a new document for each array member in the original document, therefore use $unwind again and $group to create the original form:

db.authors.aggregate([
  { "$unwind": "$books" },
  { "$lookup": {
    "from": "$books",
    "foreignField": "_id",
    "localField": "books",
    "as": "books"
  }},
  { "$unwind": "$books" },
  { "$group": {
    "_id": "$_id",
    "full_name": { "$first" "$full_name" },
    "books": { "$push": "$books" }
  }}
])

If in fact your _id values in the foreign collection of of ObjectId type, but you have values in the localField which are "string" versions of that, then you need to convert the data so the types match. There is no other way.

Run something like this through the shell to convert:

var ops = [];
db.authors.find().forEach(doc => {
  doc.books = doc.books.map( book => new ObjectId(book.valueOf()) );

  ops.push({
    "updateOne": {
      "filter": { "_id": doc._id },
      "update": {
        "$set": { "books": doc.books }
      }
    }
  });

  if ( ops.length >= 500 ) {
    db.authors.bulkWrite(ops);
    ops = [];
  }
});

if ( ops.length > 0 ) {
  db.authors.bulkWrite(ops);
  ops = [];
}

That will convert all the values in the "books" array into real ObjectId values that can actually match in a $lookup operation.