Find listings with hosts having profile picture and identity verification in the listingsAndReviews collection
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.
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_has_profile_pic": true,
"host.host_identity_verified": true
},
{
listing_url: 1,
name: 1,
address: 1,
host: 1
}
)
Output:
{
_id: '10108388',
listing_url: 'https://www.airbnb.com/rooms/10108388',
name: 'Sydney Hyde Park City Apartment (checkin from 6am)',
host: {
host_id: '16187044',
host_url: 'https://www.airbnb.com/users/show/16187044',
host_name: 'Desireé',
host_location: 'Australia',
host_about: 'At the centre of my life is my beautiful family...home is wherever my family is.\r\n' +
'\r\n' +
"I enjoy filling my life with positive experiences and love to travel and experience new cultures, to meet new people, to read and just enjoy the beauty and wonders of the 'littlest' things in the world around me and my family. \r\n",
host_response_time: 'within an hour',
host_thumbnail_url: 'https://a0.muscache.com/im/users/16187044/profile_pic/1402737505/original.jpg?aki_policy=profile_small',
host_picture_url: 'https://a0.muscache.com/im/users/16187044/profile_pic/1402737505/original.jpg?aki_policy=profile_x_medium',
host_neighbourhood: 'Darlinghurst',
host_response_rate: 100,
host_is_superhost: true,
host_has_profile_pic: true,
host_identity_verified: true,
host_listings_count: 1,
host_total_listings_count: 1,
host_verifications: [ 'email', 'phone', 'reviews', 'jumio', 'government_id' ]
},
address: {
street: 'Darlinghurst, NSW, Australia',
suburb: 'Darlinghurst',
government_area: 'Sydney',
market: 'Sydney',
country: 'Australia',
country_code: 'AU',
location: {
type: 'Point',
coordinates: [ 151.21346, -33.87603 ],
is_location_exact: false
}
}
},
{
_id: '10166883',
listing_url: 'https://www.airbnb.com/rooms/10166883',
name: 'Large railroad style 3 bedroom apt in Manhattan!',
host: {
host_id: '35215309',
host_url: 'https://www.airbnb.com/users/show/35215309',
host_name: 'Vick',
host_location: 'New York, New York, United States',
host_about: '',
host_thumbnail_url: 'https://a0.muscache.com/im/pictures/bd6a13af-36d3-4658-920b-87c221704fe2.jpg?aki_policy=profile_small',
host_picture_url: 'https://a0.muscache.com/im/pictures/bd6a13af-36d3-4658-920b-87c221704fe2.jpg?aki_policy=profile_x_medium',
host_neighbourhood: 'East Harlem',
host_is_superhost: false,
host_has_profile_pic: true,
host_identity_verified: true,
host_listings_count: 1,
host_total_listings_count: 1,
host_verifications: [ 'email', 'phone', 'reviews', 'jumio', 'government_id' ]
},
address: {
street: 'New York, NY, United States',
suburb: 'Manhattan',
government_area: 'East Harlem',
market: 'New York',
country: 'United States',
country_code: 'US',
location: {
type: 'Point',
coordinates: [ -73.93943, 40.79805 ],
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 has a profile picture and has been identity verified.
Within the find() method to filter documents the query checks whether the value of the host_has_profile_pic field within the host subdocument is true and also checks whether the value of the host_identity_verified field within the host subdocument is true.
The projection stage 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 with superhost and at least 2 listings in the listingsAndReviews collection.
Next: Retrieve listing information with host verifications.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics