What is the best way to $lookup / "join" one-to-many collections with array of references in MongoDB?

[Theodorus_Andi_Gunawan]

Hi guys, I’m quite new to MongoDB. I read a blog post about best practices in designing schema in MongoDB. One way to handle one-to-many relationship is to reference it and this is the example from the blog post.

{
    "name": "left-handed smoke shifter",
    "manufacturer": "Acme Corp",
    "catalog_number": "1234",
    "parts": ["ObjectID('AAAA')", "ObjectID('BBBB')", "ObjectID('CCCC')"]
}

I checked out the $lookup documentation but I couldn’t get that to work with an array of references. Would really appreciate if someone could help me.

[Yilmaz_Durmaz (Yılmaz Durmaz)]

you may have confused local and foreign fields.

try localField:"parts" and foreignField:"_id" and see if it resolves correct.

if not, from your given data, it is possible your parts array does not hold actual IDs but just some strings that look like some ID. "ObjectID('AAA')" is a string as is it surrounded by double-quotes.

in this second case solution of this post may help. $lookup foreignField is ObjectId - Working with Data - MongoDB Developer Community Forums . Be careful though, if your IDs are stored as this quoted string "ObjectID('AAA')" you need to extract 'AAA' from them.

PS: I believe the author of that blog post has typos for some reason. He uses "_id": "ObjectId('AAA')" for a while and then "_id": ObjectID("AAAB"). note the double quotes around ObjectId function. The first one is just a string but the second is the representation of an Id value.

[Yilmaz_Durmaz (Yılmaz Durmaz)]

If you like to watch video courses along with written ones, you may want to watch this MongoDB Schema Design Best Practices video from that very same author on that very same topic explained in the blog post.

[steevej (Steeve Juneau)]

Please share documents from bothe collections.

Please share what you have try with $lookup. Since it should work we can help you better finding what is wrong if we see what you did.

Also indicate how it fails to provide the desired results.

[Theodorus_Andi_Gunawan]

@Yilmaz_Durmaz

:"parts

That did not work

      const parts = await productCollection.aggregate([
        {
          $match: {
            _id: ObjectId(productId),
          },
          $lookup: {
            from: "parts",
            localField: "parts",
            foreignField: "_id",
            as: "parts",
          },
        },
      ]);
      return parts.toArray();

MongoServerError: A pipeline stage specification object must contain exactly one field.

[Theodorus_Andi_Gunawan]

How do I edit the post?

[Yilmaz_Durmaz (Yılmaz Durmaz)]

there is a pen image near the bottom of each post, along with like/link/reply, visible to the author of a post and replies.

[Yilmaz_Durmaz (Yılmaz Durmaz)]

@Theodorus_Andi_Gunawan

That did not work

you need to be careful with opening and closing of curly and square brackets. square brackets [] are for arrays, curlies {} are for objects. your $lookup is inside the brackets of $match hence the error “must contain one field”

each pipeline is a separate object, so it should look like this:

[ 
{ $match: {...} },
{ $lookup: {...} }
]

PS: do not forget ObjectID('AAAA') is an object ID, but "ObjectID('AAAA')" is not. the second is in double quotes making it a string.

[Yilmaz_Durmaz (Yılmaz Durmaz)]

I have made a mistake. I have forgotten a feature of mongodb

“ObjectID(‘AAAA’)” is not normally an object id in the sense of an id mongodb uses; it is a value represented by 24 char hexadecimal value such as “5d505646cf6d4fe581014ab2”. but you can still overwrite _id field with something else like a number or a string.

if take those 2 data from the blog post and put them into a test database, 1 for products and 1 for parts, you will see the product will get assigned a proper _id but the part will have "ObjectID('AAAA')" as its id.

however, the $lookup will still hold true and fill in the details of the part into the product as it also stores that string in the parts array.

also, beware if you use as:"parts", you will override the original parts array with this new filled-in details array.

[Theodorus_Andi_Gunawan]

thanks for your reply! its working now. To extend the question a little bit, lets say each part has its own parts, are those sub-parts usually nested?

[Yilmaz_Durmaz (Yılmaz Durmaz)]

@Theodorus_Andi_Gunawan

To extend the question a little bit, lets say each part has its own parts, are those sub-parts usually nested?

nesting levels depend on your database design mentioned in the blog post and video. You can continue relational or switch to embedded models.

I suggest you take M320: Data Modeling in MongoDB University.

All MDBU courses are free so feel free to check other courses to extend your knowledge.