MongoDB Exercises: listingsAndReviews collection

Last update on May 29 2023 20:36:23 (UTC/GMT +8 hours)

MongoDB Query Exercises and Solution : listingsAndReviews collection - 50 Exercises

Sample document of 'listingsAndReviews' collection:

  {
    _id: '10059872',
    listing_url: 'https://www.airbnb.com/rooms/10059872',
    name: 'Soho Cozy, Spacious and Convenient',
    summary: 'Clean, fully furnish, Spacious 1 bedroom flat just off the escalator in Mid Levels. 2 minutes From Soho Bar and Restaurants. Located in a quiet alley 1 minute from Sun Yat Sen',
    space: '',
    description: 'Clean, fully furnish, Spacious 1 bedroom flat just off the escalator in Mid Levels. 2 minutes From Soho Bar and Restaurants. Located in a quiet alley 1 minute from Sun Yat Sen',
    neighborhood_overview: '',
    notes: '',
    transit: '',
    access: '',
    interaction: '',
    house_rules: '',
    property_type: 'Apartment',
    room_type: 'Entire home/apt',
    bed_type: 'Real Bed',
    minimum_nights: '4',
    maximum_nights: '20',
    cancellation_policy: 'flexible',
    last_scraped: ISODate("2019-03-11T04:00:00.000Z"),
    calendar_last_scraped: ISODate("2019-03-11T04:00:00.000Z"),
    first_review: ISODate("2015-12-19T05:00:00.000Z"),
    last_review: ISODate("2018-03-27T04:00:00.000Z"),
    accommodates: 3,
    bedrooms: 1,
    beds: 2,
    number_of_reviews: 3,
    bathrooms: Decimal128("1.0"),
    amenities: [
      'Air conditioning',
      'Kitchen',
      'Smoking allowed',
      'Doorman',
      'Elevator',
      'Heating',
      'Family/kid friendly',
      'Essentials',
      '24-hour check-in',
      'translation missing: en.hosting_amenity_50'
    ],
    price: Decimal128("699.00"),
    weekly_price: Decimal128("5000.00"),
    extra_people: Decimal128("0.00"),
    guests_included: Decimal128("1"),
    images: {
      thumbnail_url: '',
      medium_url: '',
      picture_url: 'https://a0.muscache.com/im/pictures/4533a1dc-6fd8-4167-938d-391c6eebbc19.jpg?aki_policy=large',
      xl_picture_url: ''
    },
    host: {
      host_id: '51624384',
      host_url: 'https://www.airbnb.com/users/show/51624384',
      host_name: 'Giovanni',
      host_location: 'Hong Kong, Hong Kong',
      host_about: '',
      host_thumbnail_url: 'https://a0.muscache.com/im/pictures/264b82a7-756f-4da8-b607-dc9759e2a10f.jpg?aki_policy=profile_small',
      host_picture_url: 'https://a0.muscache.com/im/pictures/264b82a7-756f-4da8-b607-dc9759e2a10f.jpg?aki_policy=profile_x_medium',
      host_neighbourhood: 'Soho',
      host_is_superhost: false,
      host_has_profile_pic: true,
      host_identity_verified: false,
      host_listings_count: 1,
      host_total_listings_count: 1,
      host_verifications: [ 'email', 'phone', 'reviews', 'jumio', 'government_id' ]
    },
    address: {
      street: 'Hong Kong, Hong Kong Island, Hong Kong',
      suburb: 'Central & Western District',
      government_area: 'Central & Western',
      market: 'Hong Kong',
      country: 'Hong Kong',
      country_code: 'HK',
      location: {
        type: 'Point',
        coordinates: [ 114.15027, 22.28158 ],
        is_location_exact: true
      }
    },
    availability: {
      availability_30: 0,
      availability_60: 0,
      availability_90: 0,
      availability_365: 0
    },
    review_scores: {
      review_scores_accuracy: 10,
      review_scores_cleanliness: 10,
      review_scores_checkin: 10,
      review_scores_communication: 10,
      review_scores_location: 10,
      review_scores_value: 8,
      review_scores_rating: 100
    },
    reviews: [
      {
        _id: '56904633',
        date: ISODate("2015-12-19T05:00:00.000Z"),
        listing_id: '10059872',
        reviewer_id: '5302612',
        reviewer_name: 'Octavio',
        comments: 'The host canceled this reservation 11 days before arrival. This is an automated posting.'
      },
      {
        _id: '223175530',
        date: ISODate("2018-01-01T05:00:00.000Z"),
        listing_id: '10059872',
        reviewer_id: '48436743',
        reviewer_name: 'Ross',
        comments: 'Giovanni was very helpful and responsive to my questions. This is a great apartment that is very convenient for exploring Hong Kong.'
      },
      {
        _id: '247251577',
        date: ISODate("2018-03-27T04:00:00.000Z"),
        listing_id: '10059872',
        reviewer_id: '111288273',
        reviewer_name: 'Christian',
        comments: 'The host canceled this reservation 8 days before arrival. This is an automated posting.'
      }
    ]
  },

You may download the compressed file and uncompress it to find the collection used in our exercises. The collection comprises of 2000 documents.

1. Find the price per night of the first record in the listingsAndReviews collection.
Click me to see the solution

2. Retrieve the cleaning fee of the first record in the listingsAndReviews collection.
Click me to see the solution

3. Find the host_name, host_location, host_about of the first record in the listingsAndReviews collection.
Click me to see the solution

4. Retrieve the number of bedrooms in the first record in the listingsAndReviews collection.
Click me to see the solution

5. Retrieve the number of guests are included in the first record in the listingsAndReviews collection.
Click me to see the solution

6. Write a MongoDB query to check whether the host have a profile picture in the first record in the listingsAndReviews collection.
Click me to see the solution

7. Write a MongoDB query to check whether the host's identity have been verified in the first record in the listingsAndReviews collection.
Click me to see the solution

8. Write a MongoDB query to find how many listings does the host have in the first records in the listingsAndReviews collection.
Click me to see the solution

9. Write a MongoDB query to find the street address of the first record in the listingsAndReviews collection.
Click me to see the solution

10. Find all the listings in the listingsAndReviews collection where the property_type field is set to "House".
Click me to see the solution

11. Find all the listings in the listingsAndReviews collection with listing_url, name, host_name, host_location, reviewer_name and price that have a nightly price greater than $500.
Click me to see the solution

12. Find all the listings in the listingsAndReviews collection that are located in Brazil and have a review score rating of at least 9. Return name, address, and review_scores_rating.
Click me to see the solution

13. Find all the listings with name, address, reviewer_name, and review_scores_rating in the listingsAndReviews collection that have a "hot tub" amenity and are located in the United States.
Click me to see the solution

14. Find all the listings with name, amenities and price in the listingsAndReviews collection that have a "pool" amenity and a nightly price between $200 and $400.
Click me to see the solution

15. Find all the listings with name, amenities and address in the listingsAndReviews collection that have a "Washer" amenity and are located in either Canada or Mexico.
Click me to see the solution

16. Find the top 10 most reviewed listings with listing_url, name, country, review_scoresin the listingsAndReviews collection.
Click me to see the solution

17. Find all the listings with listing_url, name, address and review_scores in the listingsAndReviews collection that have a "fireplace" amenity and a review score rating of at least 8.
Click me to see the solution

18. Find all the listings with listing_url, name, address and amenities, review scores in the listingsAndReviews collection that have a "washer" amenity and are located in either Italy or Spain.
Click me to see the solution

19. Find the listings with listing_url, name, address and amenities, price, review scores in the listingsAndReviews collection that have the highest nightly prices.
Click me to see the solution

20. Find the listings with listing_url, name, address and amenities,price,review scores in the listingsAndReviews collection that have the lowest nightly prices.
Click me to see the solution

21. Retrieve all documents with name, address, reviewer_name, review_scores_ratingin the listingsAndReviewscollection that have a number_of_reviews field is equal to 0.
Click me to see the solution

22. Retrieve all documents with name, address, host, reviewer_name, review_scores_ratingin the listingsAndReviews collection where the host_is_superhost field is equal to true.
Click me to see the solution

23. Retrieve all documents with name, address, host, reviewer_name, review_scores_ratingin the listingsAndReviews collection where the coordinates field is not null.
Click me to see the solution

24. Retrieve all documents with name, address, host, bed_type, bed, review_scores_ratingfrom the listingsAndReviewscollection where the beds field is greater than or equal to 2.
Click me to see the solution

25. Find all listings with name, address, host in the listingsAndReviews collection that have a host with a host_name containing the word "Livia".
Click me to see the solution

26. Find all listings with name, address, host in the listingsAndReviews collection that have a host with a host_location of "Brazil".
Click me to see the solution

27. Retrieve all documents with name, address, host, availability in the listingsAndReviews collection where the availability_365 field is greater than 300.
Click me to see the solution

28. Retrieve all documents with listing_url, name, bedrooms, pricein the listingsAndReviews collectionwhere the bedrooms field is equal to 1.
Click me to see the solution

29. Retrieve all documents with listing_url, name, bedrooms, cleaning_fee, and price in the listingsAndReviews collectionwhere the cleaning_fee field is not null.
Click me to see the solution

30. Retrieve all documents with listing_url, name, bedrooms, pricein the listingsAndReviews collection where the price field is between 600 and 900.
Click me to see the solution

31. Retrieve all documents with listing_url, name, host, price in the listingsAndReviews collection where the host_verifications array contains "email".
Click me to see the solution

32. Retrieve all documents with listing_url, name, amenity, host in the listingsAndReviews collection where the amenities array contains both "TV" and "Wifi".
Click me to see the solution

33. Find all listings with listing_url, name, amenities, host in the listingsAndReviewscollection that have a host with a Jumio verification and a about section.
Click me to see the solution

34. Retrieve all documents with listing_url, name, host, price in the listingsAndReviews collection where the host_total_listings_count field is greater than 1.
Click me to see the solution

35. Retrieve all documents with listing_url, name, property_type, bed, price in the listingsAndReviewscollectionwhere the property_type field is equal to "Apartment" and the beds field is greater than or equal to 2.
Click me to see the solution

36. Find all listings with listing_url, name, property_type, bed, bathrooms, price in the listingsAndReviews collection that have a minimum of 2 bathrooms.
Click me to see the solution

37. Find all listings with listing_url, name, property_type, bed, price, guests_included in the listingsAndReviews collection that have a maximum of 5 guests included in the price.
Click me to see the solution

38. Find all listings with listing_url, name, property_type, bed, price, security_deposit in the listingsAndReviews collection that have a price greater than $500 and a security deposit of $1000 or more.
Click me to see the solution

39. Find all listings with listing_url, name, property_type, bed, price, cancellation_policy in the listingsAndReviews collection that have a cancellation policy of "flexible".
Click me to see the solution

40. Find all listings with listing_url, name, property_type, bed_type, amenities, price in the listingsAndReviews collection that have a real bed as the bed type and a kitchen amenity.
Click me to see the solution

41. Find all listings with listing_url, name, address, amenities in the listingsAndReviews collection that have a 24-hour check-in amenity and are located in Brazil.
Click me to see the solution

42. Find all listings with listing_url, name, address, reviews in the listingsAndReviews collection that have at least one review.
Click me to see the solution

43. Find the number of documents that have a blank medium picture url in the istingsAndReviews collection.
Click me to see the solution

44. Find all listings with listing_url, name, address, availability_30 in the istingsAndReviews collection that have an availability of at least 30 days.
Click me to see the solution

45. Find all listings with listing_url, name, address in the listingsAndReviews collection that have a suburb of "Lagoa".
Click me to see the solution

46. Find all listings with listing_url, name, address, host in the listingsAndReviews collection that have a host who is a superhost and has at least 2 listings.
Click me to see the solution

47. Find all listings with listing_url, name, address, host in the listingsAndReviews collection that have a host who has a profile pic and has been identity verified.
Click me to see the solution

48. Write a mongodb query to find the listing_url, name, address, host_verifications, and size of host_verification under the host subdocument in the listingsAndReviews collection.
Click me to see the solution

49. Find all listings with listing_url, name, address, host_verificationand size of host_verification array in the listingsAndReviews collection that have a host with at least 3 verifications.
Click me to see the solution

50. Find all listings with listing_url, name, address, host_picture_url in the listingsAndReviews collection that have a host with a picture url.
Click me to see the solution

Click here to get Sample listingsAndReviews Dataset from MongoDB.

More to Come !