CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
JahangirAhmad
JahangirAhmad

Reputation: 174

Aggregate Populate array of ids with Their Documents

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"
       }
  ],
}

Upvotes: 6

Views: 11924

Answers (2)

stevenluongo
stevenluongo

Reputation: 11

Just adding on top of the previous answer. If your input consists of an array of strings and you want to convert them to ObjectIds, you can achieve this by using a projection, followed by a map and the $toObjectId method.

db.authors.aggregate([
 { $project: {
      books: {
         $map: {
            input: '$books',
            as: 'book',
            in: { $toObjectId: '$$book' },
         },
      },
  },},
  { $lookup: {
       from: "$books",
       foreignField: "_id",
       localField: "books",
       as: "books"
     }
  },
])

Ideally, your database would be formatted in such a manner that your aggregates are stored as ObjectIds, but in the case where that is not an option, this poses as a viable solution.

Upvotes: 1

Neil Lunn
Neil Lunn

Reputation: 151122

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.

Upvotes: 12

Related Questions