Using $lookup on an array of objects to join two documents in MongoDB [duplicate]

Question

I have many Shop documents that each contain a field products which is an array of objects where the key is the product ID and the value is the quantity in stock:

{ products: [{"a": 3}, {"b": 27}, {"c": 4}] }

I have a collection Products where each product has a document containing productId, name, etc:

{ productId: "a", "name": "Spanner"}

I would like to pull in/aggregate/join the product information for each of those items. I know how to do it when the field is a single ID, and I have seen this answer which describes how to do it for an array of IDs. But I am having a bit of trouble wrapping my head around what to do with an array of objects containing IDs.

Desired output:

{
    products: [
        { {productId: "a", "name": "Spanner"}: 3 }
    ]
}

(And no, it is not within my control to switch to a relational database.)

If the downvoter could please explain how I can improve this question, that would be appreciated! — Matt
– Matt, Commented Aug 12, 2018 at 1:07

Duong Nguyen · Accepted Answer · 2018-08-12 02:07:30Z

2

I think if you want to using ID for reference, try to avoid place it as object keys, instead make it as object property like { products: [{"productId": $_id, "quantity": 3}]}, that could be a reason for downvote.

But if you cant change it, you can using $objectToArray in aggregation to convert your array.

One more thing, your desire output is unreal because object property in js cant not be an object.

Try it:

db.Shop.aggregate(

// Pipeline
[
    // Stage 1
    {
        $unwind: {
            path : "$products"
        }
    },

    // Stage 2
    {
        $project: {
            products: { $objectToArray: "$products" }
        }
    },

    // Stage 3
    {
        $unwind: {
            path : "$products"
        }
    },

    // Stage 4
    {
        $project: {
            productId: "$products.k",
            productQuantity: "$products.v"
        }
    },

    // Stage 5
    {
        $lookup: {
            "from" : "products",
            "localField" : "productId",
            "foreignField" : "productId",
            "as" : "products"
        }
    },

    // Stage 6
    {
        $unwind: {
            path : "$products"
        }
    },

    // Stage 7
    {
        $project: {
            productId: "$productId",
            productQuantity: "$productQuantity",
            productName: "$products.name"
        }
    },
]);

Good luck

edited Aug 12, 2018 at 2:07

answered Aug 12, 2018 at 1:19

Duong Nguyen

1445 bronze badges

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

2 Comments

Matt Over a year ago

Thank you. However, even if I change to the format you suggest, my problem is that I am not sure how to access the properties inside each object (so, the productId). Sorry if that I was not clear.

Duong Nguyen Over a year ago

you can access object property by $products.productId

Collectives™ on Stack Overflow

Using $lookup on an array of objects to join two documents in MongoDB [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related