Mongo query practice – part 1 of 2

Use the coffee database on the cpsc server’s Mongo instance for this assignment. Answer the following questions with both a query and the actual answer:

1. How many coffee shops are in this little database?

db.shops.countDocuments()
30

2. Cities with coffee shops?

db.shops.distinct("city",{},{'city':1})
[ 'Boston', 'Chicago', 'Portland', 'San Francisco', 'Seattle' ]

3. Cities that have at least some coffee shops with excellent wifi?

db.shops.distinct("city",{wifi_quality:'Excellent'},{'city':1})
[ 'Boston', 'Chicago', 'San Francisco' ]

4. What are the names of all the shops in either Boston or Seattle that have a rating higher than 4.5? (List each shop name only once.)

db.shops.distinct("name",{$or:[{'city':'Boston'},{'city':'Seattle'}], 'rating':{'$gt':4.5}}, {'name':1,'_id':0})
[ 'Java Jive', 'Mocha Motion' ]

5. Average rating of all shops?

db.shops.aggregate([{'$group':{'_id':null,'avgrating':{'$avg':'$rating'}}}])
[ { _id: null, avgrating: 4.346666666666667 } ]

6. Average rating for shops that serve Cold Brew?

db.shops.aggregate([{$unwind:'$menu'},{$match:{'menu.item':'Cold Brew'}}, {'$group':{'_id':null,'avgRating':{'$avg':'$rating'}}}])
[ { _id: null, avgRating: 4.32 } ]

7. How many coffee shops in each city? (Order these results so that the city with the most shops is at the top of the list, and the city with the fewest shops is at the bottom.)

db.shops.aggregate([{$group:{'_id':'$city', 'count':{'$sum':1}}}, {$project:{'count':1,'city':'$_id','_id':0}}, {'$sort':{'count':-1}}])
[
  { count: 14, city: 'Chicago' },
  { count: 6, city: 'Seattle' },
  { count: 4, city: 'Portland' },
  { count: 4, city: 'Boston' },
  { count: 2, city: 'San Francisco' }
]