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

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, Aug 12 '18 at 01:07

Duong Nguyen · Answer 1 · 2018-08-12T02:07:30.507

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

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. — Matt, Aug 12 '18 at 01:30

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

1 Answers1