Finding listings with reviews in the listingsAndReviews collection

Last update on May 28 2023 12:07:37 (UTC/GMT +8 hours)

Find all listings with listing_url, name, address, reviews in the listingsAndReviews collection that have at least one review.

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(
  {
    reviews: { $exists: true, $not: { $size: 0 } }
  },
  {
    listing_url: 1,
    name: 1,
    address: 1,
    reviews: 1
  }
);

Output:

 {
    _id: '10141950',
    listing_url: 'https://www.airbnb.com/rooms/10141950',
    name: 'Big, Bright & Convenient Sheung Wan',
    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.15007, 22.28422 ],
        is_location_exact: true
      }
    },
    reviews: [
      {
        _id: '59088971',
        date: ISODate("2016-01-06T05:00:00.000Z"),
        listing_id: '10141950',
        reviewer_id: '1205536',
        reviewer_name: 'Winnie',
        comments: "Regg's place is clean and bright and at the best location in Hong Kong. Regg is communicative and easy going. We enjoyed our stay very much."
      }
    ]
  },
  {
    _id: '10166986',
    listing_url: 'https://www.airbnb.com/rooms/10166986',
    name: 'Resort-like living in Williamsburg',
    address: {
      street: 'Brooklyn, NY, United States',
      suburb: 'Williamsburg',
      government_area: 'Williamsburg',
      market: 'New York',
      country: 'United States',
      country_code: 'US',
      location: {
        type: 'Point',
        coordinates: [ -73.93869, 40.71552 ],
        is_location_exact: true
      }
    },
    reviews: [
      {
        _id: '58238428',
        date: ISODate("2016-01-01T05:00:00.000Z"),
        listing_id: '10166986',
        reviewer_id: '23529124',
        reviewer_name: 'David',
        comments: 'Mohammed was very welcoming and helpful and ensured we had everything we needed for our stay.'
      }
    ]
  },

.....

Explanation:

The said query in MongoDB that retrieves all the documents with the listing URL, name, address, and reviews fields from the listingsAndReviews collection that have at least one review.

In the first parameter of the find() method the $exists operator checks whether the reviews field exists, and the $not operator with $size ensures that it has a non-zero size. It filters the documents that have at least one review.

In the second parameter the projection object includes the listing_url, name, address, and reviews by setting their values to 1, indicating inclusion.

Improve this sample solution and post your code through Disqus.

Previous: Find listings with 24-hour check-in and located in Brazil.
Next: Finding the number of documents with a blank medium picture url in the listingsAndReviews collection.