MongoDB query nearby

Question

I have a Collection User, Restaurants, Products I want to query that User will see in his/her app the nearby Restaurants(5km) which isOpen: true and show products of each restaurants which isAvailable and isApproved = true and isArchived: false.

let kmToRadian = function(miles){
 var earthRadiusInMiles = 6378
 return miles / earthRadiusInMiles
}

var user = dbo.collection('users').findOne({_id: id})
var query = {
  "location": {
      $geoWithin: {
          $centerSphere: [ [22.222, 22.222], 5 / 6378.1] //sample coordinates
      }
  }
}


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

Here, I'm getting the products isAvailable, isApproved :true and isArchived false. Now, I want to get the 5km restaurant nearby.

Collection Users

id: 1
name: "Robert"
location: Object
    type: "Point",
    coordinates: Array
     0: 11.111 //sample coordinates
     1: 11.111 //sample coordinates

id: 2
    name: "Jason"
    location: Object
        type: "Point",
        coordinates: Array
         0: 22.222 //sample coordinates
         1: 22.222 //sample coordinates

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

Output: if Robert is in the coordinates [ 22.222, 22.222 ]. Robert will see the restaurants McDonald's and Chick-fil-A because isOpen: true. Show their products which is Big Mac and Spicy Chicken Sandwich because isAvailable, isApproved: true and isArchived: false.

Answer 1

Not an answer, just some ideas, that you should evaluate yourself. The following pipeline:

1. Matches isApproved:true, isAvailable:true, isArchived:true So it assumes and suggests to use this index:

db.products.createIndex({
  isAvailable: 1, isApproved: 1, isArchived: 1
})

2. Once the number of documents is well narrowed down, lookup shouldn't take that long. Here no index is needed as long as foreignField:_id is used, otherwise, I would create an index on that field.
3. $match isOpen:true will be slow, but improve performance later on, if this works. pipeline in $lookup could help to achieve better performance, adding this index (IMHO) db.restaurants.createIndex({ _id: 1, isOpen: 1 })
4. I commented out fields we filter off in match, because we know the value, and could be set, probably improving performance (can't prove this).
5. We project location in because we need that field
6. Use geo to locate what is near, that was almost correct. This step will be rather slow, because there is no index in "geo", and it's not possible to create one in this query.

db.products.aggregate([
  {
    $match: {
      "isApproved": true,
      "isAvailable": true,
      "isArchived": false
    }
  },
  {
    $lookup: {
      from: "restaurants",
      foreignField: "_id",
      localField: "restaurant",
      as: "restaurant"
    }
  },
  {
    $match: {
      "restaurant.isOpen": true
    }
  },
  {
    $project: {
      "restaurant.isOpen": 1,
      //"isApproved": 1,
      //"isAvailable": 1,
      //"isArchived": 1,
      "name": 1,
      "restaurant.location": 1
    }
  },
  {
    $match: {
      "restaurant.location": {
        $geoWithin: {
          $centerSphere: [
            [
              22.222,
              22.222
            ],
            5/6378.1] 
      }
  }
  }}
])

I must say I'm no expert, so it's up to you to test and improve. Also, please only include JSON format of documents, so we can test right away. I had to do it manually for the LIVE VERSION.

( Also good to remember, something not so useful but snappy and well terminated is best than a very complex laggy app. Again, you'll find out how it works, and I'm no expert at all. )