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

Question

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.

Ashh · Accepted Answer · 2019-03-26 16:04:54Z

2

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

answered Mar 26, 2019 at 16:04

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

1 Comment

maikelinhas Over a year ago

Worked perfectly!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related