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?