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

Neil Lunn · Accepted Answer · 2017-06-28 09:47:59Z

12

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.

edited Jun 28, 2017 at 9:47

answered Jun 28, 2017 at 7:39

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JahangirAhmad Over a year ago

it is not working, return empty array. books document has _id typed ObjectId, but in authors document i saved just ids as string.

Neil Lunn Over a year ago

@JahangirAhmad Well you need to fix that then. Your question shows "strings" in both fields. So fix your data to make the "object id like" strings actually into ObjectId values. Do you need assistance with that? Please update your question to show the real data. And by "update" I mean "in addition" and do not overwrite the data you placed in your original question. So I just rolled back your revision. Don't make changes to the context of questions you ask once you get an answer.

Neil Lunn Over a year ago

@JahangirAhmad You cannot match a string to an ObjectId. Do you need help understanding how to convert them? If you do then ask. Stop editing the question.

JahangirAhmad Over a year ago

Yes please help me convert that

Neil Lunn Over a year ago

@JahangirAhmad Added a shell script for you to run a conversion.

stevenluongo · Accepted Answer · 2023-01-01 14:25:19Z

1

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.

answered Jan 1, 2023 at 14:25

stevenluongo

111 bronze badge

1 Comment

lwdthe1 Over a year ago

Thanks. This worked in $reduce, too: { $addFields: { firstSectionPostIds: { $reduce: { input: '$firstSection.items', initialValue: [], in: { $concatArrays: ['$$value', [{ $toObjectId: '$$this.postId' }]] }, }, }, }, },

Collectives™ on Stack Overflow

Aggregate Populate array of ids with Their Documents

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related