CODEWITHSUNDEEP
mongodbaggregation-framework
maikelinhas
maikelinhas

Reputation: 81

How do I use mongodb $lookup with a nested array?

I'm trying to perform a search query which would combine two collections.

Collection 1 is called stock collection.

{ "fruit_id" : "1", "name" : "apples", "stock": 100 }
{ "fruit_id" : "2", "name" : "oranges", "stock": 50 }
{ "fruit_id" : "3", "name" : "plums", "stock": 60 }

Collection 2 is called orders collection

{ "order_id" : "001", "ordered_fruits":
    [
     {"fruit_id" : "1", "name" : "apples", "ordered" : 5},
     {"fruit_id" : "3", "name" : "plums", "ordered" : 20}
    ]
}
{ "order_id" : "002", "ordered_fruits":
    [
     {"fruit_id" : "2", "name" : "oranges", "ordered" : 30},
     {"fruit_id" : "3", "name" : "plums", "ordered" : 20}
    ]
}

I am trying to code a query that returns the stock collection with an additional key pair which represents the total amount of ordered fruits.

{ "fruit_id" : "1", "name" : "apples", "stock": 100, "ordered": 5 }
{ "fruit_id" : "2", "name" : "oranges", "stock": 50, "ordered": 30 }
{ "fruit_id" : "3", "name" : "plums", "stock": 60, "ordered": 40 }

I have tried to use $lookup from the aggregation framework but it becomes complicated with nested arrays. I'm pretty stuck now.

Upvotes: 2

Views: 230

Answers (1)

Ashh
Ashh

Reputation: 46441

You can use below aggregation

db.stock.aggregate([
  { "$lookup": {
    "from": "orders",
    "let": { "fruitId": "$fruit_id" },
    "pipeline": [
      { "$match": { "$expr": { "$in": ["$$fruitId", "$ordered_fruits.fruit_id"] }}},
      { "$unwind": "$ordered_fruits" },
      { "$match": { "$expr": { "$eq": ["$ordered_fruits.fruit_id", "$$fruitId"] }}}
    ],
    "as": "orders"
  }},
  { "$project": {
    "ordered": { "$sum": "$orders.ordered_fruits.ordered" },
    "fruit_id" : 1, "name" : 1, "stock": 1
  }}
])

MongoPlayground

Upvotes: 2

Related Questions