Mongodb $lookup on array of objects/documents

Question

The document schema for my orders collection is given below:

{
"_id" : ObjectId("Id of this record"),
    "location_data" : {
        "type" : "main",
        "location_id" : ObjectId("this location's id")
    },
    "item_details" : [
        {
            "item_id" : ObjectId("this item's id"),
            "qty" : 950
        },
        {
            "item_id" : ObjectId("this item's id"),
            "qty" : 200
        }
    ]
}

I have items stored in a different collection whose schema looks like this:

{
    "_id": ObjectId("this record's id"),
    "title": "this item's title",
    "description": "this item's description"
}

My desired output would be:

{
"_id" : ObjectId("Id of this record"),
    "location_data" : {
        "type" : "main",
        "location_id" : ObjectId("this location's id")
    },
    "item_details" : [
        {
            "title": "this item's title",
            "item_id" : ObjectId("this item's id"),
            "qty" : 950
        },
        {
            "title": "this item's title",
            "item_id" : ObjectId("this item's id"),
            "qty" : 200
        }
    ]
}

In Mongodb doc I haven't found anything to work with array of objects. Any help would be appreciable.

Thanks

Arsen Davtyan · Accepted Answer · 2019-11-16 03:47:11Z

1

Try the following Aggregate query:

db.orders.aggregate([
    {$unwind : "$item_details"},  // Since the item_details is an array, unwind it first
    {$lookup:     // run the $lookup to join with `items` collections
        {
            from: "items", 
            localField: "item_details.item_id",
            foreignField: "_id",
            as: "item_detailsTemp"
        }
    },
    { 
        $addFields: { "item_details" : { $mergeObjects: [ { $arrayElemAt: [ "$item_detailsTemp", 0 ] }, "$item_details" ] } } // Replace the existing item_details with new information 
    },
    {
        $group : { // Since you've ran the $unwind at early stage, group it to make the item_details as an array again
           "_id" : "$_id", 
           "location_data" : {$first : "$location_data"}, 
           "item_details" : {$push : "$item_details"}}
    }
])
// Voila! Hope this helps!

edited Nov 16, 2019 at 3:47

answered Nov 15, 2019 at 23:39

Arsen Davtyan

1,9678 gold badges24 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

Mongodb $lookup on array of objects/documents

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related