Find Listings with superhost and at least 2 listings in the listingsAndReviews collection
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.
Structure 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.'
}
]
},
.......
Query:
db.listingsAndReviews.find(
{
"host.host_is_superhost": true,
"host.host_total_listings_count": { $gte: 2 }
},
{
listing_url: 1,
name: 1,
address: 1,
host: 1
}
)
Output:
{
_id: '1058555',
listing_url: 'https://www.airbnb.com/rooms/1058555',
name: 'Nice Cosy Room In Taksim',
host: {
host_id: '5823933',
host_url: 'https://www.airbnb.com/users/show/5823933',
host_name: 'Nihat',
host_location: 'Istanbul',
host_about: 'I live alone in Taksim area and i work at bar.\r\n' +
'I like meet new friends from all of the world.\r\n' +
'I like to Travel a lot ofcourse if i have free time :) East Asia , Sun , Sea , Sand , Movie :) ',
host_response_time: 'within an hour',
host_thumbnail_url: 'https://a0.muscache.com/im/pictures/user/6bf03261-e7ac-4e0e-8121-3828612bbb6a.jpg?aki_policy=profile_small',
host_picture_url: 'https://a0.muscache.com/im/pictures/user/6bf03261-e7ac-4e0e-8121-3828612bbb6a.jpg?aki_policy=profile_x_medium',
host_neighbourhood: 'Cihangir',
host_response_rate: 100,
host_is_superhost: true,
host_has_profile_pic: true,
host_identity_verified: false,
host_listings_count: 2,
host_total_listings_count: 2,
host_verifications: [ 'email', 'phone', 'facebook', 'reviews' ]
},
address: {
street: 'Taksim, Cihangir, Istanbul , Beyoğlu, Turkey',
suburb: 'Beyoglu',
government_area: 'Beyoglu',
market: 'Istanbul',
country: 'Turkey',
country_code: 'TR',
location: {
type: 'Point',
coordinates: [ 28.98648, 41.03376 ],
is_location_exact: true
}
}
},
{
_id: '10635276',
listing_url: 'https://www.airbnb.com/rooms/10635276',
name: 'GREAT Apartment at Barra da Tijuca',
host: {
host_id: '54904357',
host_url: 'https://www.airbnb.com/users/show/54904357',
host_name: 'Luiz Henrique',
host_location: 'Rio de Janeiro, State of Rio de Janeiro, Brazil',
host_about: 'Sou de Belo Horizonte moro no Rio desde 1998',
host_thumbnail_url: 'https://a0.muscache.com/im/pictures/897884b4-1c64-4817-9d81-6bf51c6a6024.jpg?aki_policy=profile_small',
host_picture_url: 'https://a0.muscache.com/im/pictures/897884b4-1c64-4817-9d81-6bf51c6a6024.jpg?aki_policy=profile_x_medium',
host_neighbourhood: 'Barra da Tijuca',
host_is_superhost: true,
host_has_profile_pic: true,
host_identity_verified: false,
host_listings_count: 2,
host_total_listings_count: 2,
host_verifications: [ 'email', 'phone', 'reviews', 'jumio', 'government_id' ]
},
address: {
street: 'Rio de Janeiro, Rio de Janeiro, Brazil',
suburb: 'Barra da Tijuca',
government_area: 'Barra da Tijuca',
market: 'Rio De Janeiro',
country: 'Brazil',
country_code: 'BR',
location: {
type: 'Point',
coordinates: [ -43.32984175609327, -23.011124595436097 ],
is_location_exact: true
}
}
},
Explanation:
The said query in MongoDB that retrieves all the documents with the listing_url, name, address, and host fields from the listingsAndReviews collection where the host is a superhost and has at least 2 listings.
The find() method filters the documents with a host who is a superhost and has at least 2 listings.
The $gte operator checks whether the value of the total_listings_count field within the host subdocument is greater than or equal to 2.
The projection includes the listing_url, name, address, and host fields in the output.
Improve this sample solution and post your code through Disqus.
Previous: Find listings in Lagoa suburb in the listingsAndReviews collection.
Next: Find listings with hosts having profile picture and identity verification in the listingsAndReviews collection.
What is the difficulty level of this exercise?
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/mongodb-exercises/mongodb-listingsandreviews-exercise-46.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics