Clash Royale CLAN TAG#URR8PPP

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

This question already has an answer here:

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:

Shop

products

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

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

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

Products

productId

name

productId: "a", "name": "Spanner"

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.)

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer
1

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

productId