MongoDB query filter

Question

I have a collection Restaurant, Products, and Reviews

restaurants: [
    {
      _id: 1,
      name: "Burger King",
      location: {
        type: "Point",
        coordinates: [
          11.111,
          11.111
        ]
      },
      isOpen: true
    },
    {
      _id: 2,
      name: "McDonald's",
      location: {
        type: "Point",
        coordinates: [
          22.222,
          22.222
        ]
      },
      isOpen: true
    },
    {
      _id: 3,
      name: "Chick-fil-A",
      location: {
        type: "Point",
        coordinates: [
          33.333,
          33.333
        ]
      },
      isOpen: true
    }
  ],
products: [
    {
      _id: 1,
      name: "Breakfast Whopper Jr.",
      price: "$1.29",
      isAvailable: true,
      isApproved: true,
      quantitySold: 50,
      restaurant: ObjectId("1")
      
    },
    {
      _id: 2,
      name: "Big Mac",
      price: "$4.35",
      isAvailable: true,
      isApproved: true,
      quantitySold: 59,
      restaurant: ObjectId("2")
    },
    {
      _id: 3,
      name: "Spicy Chicken Sandwich",
      price: "$3.29",
      isAvailable: true,
      isApproved: true,
      quantitySold: 60,
      restaurant: ObjectId("3")
    },
    {
      _id: 4,
      name: "Chicken Sandwich",
      price: "$2.29",
      isAvailable: true,
      isApproved: true,
      quantitySold: 58,
      restaurant: ObjectId("3")
    }
  ],
reviews: [
    {
      _id: 1,
      message: "Big burger even if it's junior size.",
      restaurant: ObjectId("1"),
      product: ObjectId("1")
    },
    {
      _id: 2,
      message: "Big Mac is really the best burger in town.",
      restaurant: ObjectId("2"),
      product: ObjectId("2")
      
    },
    {
      _id: 3,
      message: "Spicy Chicken Sandwich rocks!",
      restaurant: ObjectId("3"),
      product: ObjectId("3")
    },
    {
      _id: 4,
      message: "Chicken Sandwich is the best sandwich of Chick-fil-A!",
      restaurant: ObjectId("3"),
      product: ObjectId("4")
    },
    {
      _id: 5,
      message: "Chicken Sandwich is the best!",
      restaurant: ObjectId("3"),
      product: ObjectId("4")
    }
  ]

My implementation

db.products.aggregate([
    { 
        $lookup: { 
            "from": "restaurant", 
            "localField": "restaurant", 
            "foreignField": "_id", 
            "as": "restaurant" 
        } 
    },
    {
        $match: {
            "restaurant.isOpen": true,
            "isApproved": true,
            "isAvailable": true
        }
    },
    {
        $project: {
            "restaurant.isOpen": 1,
            "isApproved": 1,
            "isAvailable": 1,
            "restaurant.location": 1,
            "quantitySold": 1
        }
    },
    {
        $match: {
            "restaurant.location": {
                $geoWithin: {
                    $centerSphere: [[222.22, 222.33], 10/6378.1] // sample coordinates
                }
            }
        }
    },
    {
        $sort: {
            // best seller
            "quantitySold": -1
        }
    }

In my implementation. I'm currently getting the 10km of restaurant that isOpen: true and its products that isAvailable: true and isApproved: true. I also sorted the best selling by the quantitySold field.

Now, I want to query also the most reviewed products and I want to show only 1 product per restaurant in my app based on best seller and most reviewed. Then, after that the remaining products will randomize below the sorted best seller and most reviewed.

Example. I'm in 10km of Burger King, MCDO, Chick-fil-A and I will see their products based on best seller and most reviewed (1 product). Then after, I will see all of their products randomize below.

Answer 1

Currently you have OPEN (restaurant), APPROVED and AVAILABLE list of items. (You have to adjust the existing query's $project section to get few more fields accordingly).

To get the reviews you can use $lookup, where

{
     $lookup:
       {
         from: "reviews",
         localField: "_id",
         foreignField: "product",
         as: "product_reviews"
       }
  }

you will get product_reviews array, for each product. You can then perform $groupby and $count to get the total count per product in the next pipeline stage.

After getting the above list in the aggregation pipeline use GROUP BY as follows

{ $group : { _id : "$restaurant_id", items: { $push : "$$ROOT" } }}

(Get restaurant id and name for products in $project stage in existing query). Now you have a list with restaurants id/names and their product arrays. You can refer to below links to know more about $$ROOT

Mongo group and push: pushing all fields

As your list is already sorted you will have the best seller item at top.

Regarding the other food items do you really need to randomize every time? you can then try to work with $sample (refer :https://docs.mongodb.com/manual/reference/operator/aggregation/sample/) or follow this mongo db aggregate randomize ( shuffle ) results as per your need.