Exercises

Exercise 1

• Use restaurants.json

• Using aggregation framework, print documents below.

  1. Find Korean cuisine

  2. Group by borough and count the cuisines.

  [{'_id': 'Brooklyn', 'count': 16},
  {'_id': 'Queens', 'count': 167},
  {'_id': 'Staten Island', 'count': 1},
  {'_id': 'Manhattan', 'count': 78}]

Exercise 2

• Using aggregation framework, print documents below.

  1. Find Korean cuisine

  2. Unwind grades

  3. Group by borough and grade in grades, and count the document

  4. Sort by count and print five documents

  [{'_id': {'borough': 'Queens', 'grade': 'A'}, 'count': 443},
  {'_id': {'borough': 'Manhattan', 'grade': 'A'}, 'count': 232},
  {'_id': {'borough': 'Queens', 'grade': 'B'}, 'count': 140},
  {'_id': {'borough': 'Manhattan', 'grade': 'B'}, 'count': 49},
  {'_id': {'borough': 'Brooklyn', 'grade': 'A'}, 'count': 38}]

Exercise 3

• Find international airports in California using aggregate framework

1. states collection

   • Get the California location (Polygon)

2. airports collection

   • Find “intl” in name field (text)

   • Find airports in the California

   • Project name, type and code (w/o _id)

   • Sort by name (ascending order) and code (descending order)

Exercise 4

• Find Korean restaurants that are located at less 2km from international airports (use the type field).

  • Warn: should create 2dsphere index to address for restaurants collection.

  [{'_id': ObjectId('5c8626729f44c9f6767fe0af'),
  'address': {'building': '0',
               'coord': 497.9151037865528,
               'street': 'Jfk International Airpor',
               'zipcode': '11430'},
   'borough': 'Queens',
   'cuisine': 'Korean',
   'grades': [{'date': datetime.datetime(2014, 6, 18, 0, 0),
               'grade': 'A',
               'score': 2},
              {'date': datetime.datetime(2014, 1, 14, 0, 0),
               'grade': 'A',
               'score': 12},
              {'date': datetime.datetime(2013, 1, 4, 0, 0),
               'grade': 'A',
               'score': 11},
              {'date': datetime.datetime(2012, 4, 25, 0, 0),
               'grade': 'A',
               'score': 13}],
   'name': 'Korean Lounge',
   'restaurant_id': '40625198'}]

Exercise 5

• Print the average, minimum, and maximum of passengers(ride_pasgr_numfield) of each subway line.

• Hint: group by the ‘line_num’ field and use $max, $min, and $avg.

• Result

  [{'_id': '경원선', 'avg_station': 7413.872106666667, 'max_station': 27076, 'min_station': 1},
  {'_id': '일산선', 'avg_station': 11100.640561622466, 'max_station': 25857, 'min_station': 1},
  {'_id': '1호선', 'avg_station': 26936.31375, 'max_station': 70147, 'min_station': 4815},
  ...
  {'_id': '수인선', 'avg_station': 3436.322596153846, 'max_station': 9023, 'min_station': 25},
  {'_id': '경강선', 'avg_station': 2294.190909090909, 'max_station': 9201, 'min_station': 192}]

Exercise 6

• Print the total number of passengers (ride_pasgr_numfield) during 2017-12-21 ~ 2017-12-28.

• ‘use_dt’ is a stringtype.

• Hint: use $match,$gt$lt, $sum, $sort

• Result

  [{'_id': {'use_dt': '20171221'}, 'total_rider_pasgr': 8505895},
  {'_id': {'use_dt': '20171222'}, 'total_rider_pasgr': 8917017},
  {'_id': {'use_dt': '20171223'}, 'total_rider_pasgr': 6781294},
  {'_id': {'use_dt': '20171224'}, 'total_rider_pasgr': 4866330},
  {'_id': {'use_dt': '20171225'}, 'total_rider_pasgr': 5096990},
  {'_id': {'use_dt': '20171226'}, 'total_rider_pasgr': 7935515},
  {'_id': {'use_dt': '20171227'}, 'total_rider_pasgr': 7906780},
  {'_id': {'use_dt': '20171228'}, 'total_rider_pasgr': 8056509}]