CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Pinky Promise
Pinky Promise

Reputation: 223

MongoDB $lookup and $match

I have a restaurants collection and products collections I want to get shops that isOpen: true and its products that isAvailable and isApproved: true and isArchived: false.

Collection restaurants

id: 1  
name: "Burger King"
location: Object
    type: "Point",
    coordinates: Array
       0: 11.111 //sample coordinates
       1: 11.111 //sample coordinates
isOpen: true

id: 2
name: "McDonald's"
location: Object
    type: "Point",
    coordinates: Array
       0: 22.222 //sample coordinates
       1: 22.222 //sample coordinates
isOpen: true

id: 3  
name: "Chick-fil-A"
location: Object
    type: "Point",
    coordinates: Array
       0: 22.333 //sample coordinates
       1: 22.333 //sample coordinates
isOpen: true

Collection products

id: 1
name: "Breakfast Whopper Jr."
price: "$1.29"
isAvailable: true
isApproved: true
createdAt: Tues Dec 01 2020 09:15:19 GMT+0800
updatedAt: Tues Dec 01 2020 09:15:19 GMT+0800
isArchived: false
shop: ObjectId('1')

id: 2
name: "Big Mac"
price: "$4.35"
isAvailable: true
isApproved: true
createdAt: Tues Dec 01 2020 09:15:19 GMT+0800
updatedAt: Tues Dec 01 2020 09:15:19 GMT+0800
isArchived: false
shop: ObjectId('2')

id: 3
name: "Spicy Chicken Sandwich"
price: "$3.29"
isAvailable: true
isApproved: true
createdAt: Tues Dec 01 2020 09:15:19 GMT+0800
updatedAt: Tues Dec 01 2020 09:15:19 GMT+0800
isArchived: false
restaurant: ObjectId('3')

My current implementation is like this

db.products.aggregate([ 
{ 
  $lookup: { 
    from: 'restaurants', 
    localField: 'restaurants', 
    foreignField: '_id', 
    as: 'restaurant' 
  } 
}, { 
    $match: 
        { 
            'restaurant.isOpen': true } 
        } 
])

Right now, I'm getting the shop that is open. how can I get the products that isAvailable and isApproved: true and isArchived: false

Upvotes: 0

Views: 233

Answers (1)

ray
ray

Reputation: 15227

I notice that the lookup key from your example is restaurants, but some of your products documents are missing the field (i.e. id 1 and 2), which may cause some unexpected behaviour.

Other than that, this could be what you are looking for:

db.getCollection("products").aggregate([
    { 
        $lookup: { 
            from: "restaurants", 
            localField: "shop", 
            foreignField: "_id", 
            as: "restaurant" 
        } 
    },
    {
        $unwind: "$restaurant"
    },
    {
        $match : {
            "restaurant.isOpen" : true,
            "isAvailable" : true,
            "isApproved" : true,
            "isArchived": false
        }
    }
])

Upvotes: 1

Related Questions