CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
yasar karaca
yasar karaca

Reputation: 3

How to filter Mongodb $lookup results to get only the matched nested objects?

I have a customers collection such as;

{ 
  "_id" : ObjectId("5de8c07dc035532b489b2e23"),
  "name" : "sam",
  "orders" : [{"ordername" : "cola"},{"ordername" : "cheesecake"}]
}

And waiters collection such as;

{   
    "_id" : ObjectId("5de8bc24c035532b489b2e20"),
    "waiter" : "jack",
    "products" : [{"name" : "cola", "price" : "4"}, 
                  {"name" : "water", "price" : "2"}, 
                  {"name" : "coffee", "price" : "8" }]
}
{   
    "_id" : ObjectId("5de8bdc7c035532b489b2e21"),
    "waiter" : "susan",
    "products" : [{"name" : "cheesecake", "price" : "12" }, 
                  {"name" : "apple pie", "price" : "14" }]
}

I want to join the objects from waiters collection into the customers collection by matching "products.name" and "orders.ordername". But, the result includes the whole document from the waiters collection, however, I want only the matched objects inside the document. Here is what I want;

ordered:[ 
  {"name" : "cola", "price" : "4"},
  {"name" : "cheesecake", "price" : "12" },
]

I tried $lookup with and without pipeline, and filter but could not get this result. Thanks in advance.

Upvotes: 0

Views: 440

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22276

You had the right idea, we just have to "massage" the data a bit due to its structure like so:

db.collection.aggregate([
    {
        $addFields: {
            "orderNames":
                {
                    $reduce: {
                        input: "$orders",
                        initialValue: [],
                        in: {$concatArrays: [["$$this.ordername"], "$$value"]}
                    }
                }
        }
    },
    {
      $lookup:
          {
            from: "waiters",
            let: {orders: "$orderNames"},
            pipeline: [
                {
                    $unwind: "$products"
                },
                {
                    $match:
                        {
                            $expr:{$in: ["$products.name", "$$orders"]},
                        }
                },
                {
                    $group: {
                        _id: "$products.name",
                        price: {$first: "$products.price"}
                    }
                },
                {
                    $project: {
                        _id: 0,
                        price: 1,
                        name: "$_id"
                    }
                }
            ],
            as: "ordered"
        }
    }
])
  • It feels like you could benefit from a new collection of mapping items to prices. Could potentially save you a lot of time.

Upvotes: 1

Related Questions