The first thing I need to do is plan out my queries for the frontend.
The iOS App is going to display a series of available listings. It will get GPS coordinates as input and it will output a list of available listings, sorted by distance.
FIrst, let’s add a geo-spatial index.
db.throwaway-1.listings.createIndex({ "Property.GPS": "2dsphere" });This only needs to be run once, from the db shell.
Prompt:
{"Property":{"HotelName":"Istanbul Palace Hotel","GPS":[-73.987601,40.748817]}
This is a sample document in a MongoDB database. Write a query, for the shell, that takes GPS coordinates as input and outputs ten documents based on distance from the input GPS coordinates
Our database name is throwaway-1 and our collection name is listings.ChatGPT has a tendency to include comments, even when you don’t ask for them, so be sure to explicitely ask for no comments.
Output should look something like this:
db.listings.find({
"Property.GPS": {
$near: {
$geometry: {
type: "Point",
coordinates: [41.025570330702735, 28.977524665462145]
}
}
}
}).limit(10);From here we cut and paste it into our shell to see if we get results. Play around with the coordinates to verify the distance sorting is working.
I used different GPS coordinates to test that the sorting was working correctly:
db.listings.find({
"Property.GPS": { $near: { $geometry: { type: "Point", coordinates: [
41.025570330702735,
28.977524665462145
]
}
}
}
},
{
"Property.HotelName": 1,
"Property.GPS": 1
}).limit(3);db.listings.find({
"Property.GPS": { $near: { $geometry: { type: "Point", coordinates: [
41.05314902455228,
29.031734625650678
]
}
}
}
},
{
"Property.HotelName": 1,
"Property.GPS": 1
}).limit(3);But, there are some other queries were going to need.
The user is going to be able to search for properties based on their amenities. We’re going to need a distinct list of all possible amenities.
Prompt:
{"Property":{"HotelName":"Istanbul Palace Hotel","GPS":[41.025570330702735, 28.977524665462145],"Address":"456 Bosphorus Avenue","City":"Istanbul","LocalRegion":"Istanbul Province","TotalHotelRooms":150,"Photos":["url_to_photo1.jpg","url_to_photo2.jpg"],"PropertyAmenities":{"Photos":["url_to_amenity_photo1.jpg","url_to_amenity_photo2.jpg"],"WiFi":true,"SwimmingPool":true,"Parking":true,"Gym":true,"SpaServices":true,"Restaurant":true,"Bar":true,"RoomService":true,"LaundryService":true,"BusinessCenter":true,"ConferenceRooms":true,"BanquetFacilities":true,"ChildcareServices":true,"ConciergeService":true,"CurrencyExchangeService":true,"GiftShop":true,"AccessibilityFeatures":true,"AirportShuttle":true,"CarRentalDesk":true,"Casino":false,"GolfCourse":false,"TennisCourts":false,"PrivateBeachAccess":false,"SkiInSkiOutAccess":false,"BikeRental":true,"ElectricVehicleChargingStation":true,"PetFriendly":true,"NonSmoking":true,"24HourFrontDesk":true,"Sauna":true,"TurkishBath":true,"Solarium":false,"HotTub":true,"Library":true,"GameRoom":true,"ChildrensPlayArea":true,"OutdoorGrill":true,"PicnicArea":true,"FirePit":true,"Garden":true,"RooftopTerrace":true,"LiveEntertainment":true,"CookingClasses":false,"YogaClasses":true,"MeditationAreas":true,"WaterSportsFacilities":false,"DivingCenter":false,"Snorkeling":false,"SurfingLessons":false},"Rules":{"CheckInTime":"15:00","CheckOutTime":"11:00","SmokingAllowed":false,"PetsAllowed":true,"BreakfastIncluded":true}},"RoomType":[{"Photos":["url_to_room_photo1.jpg","url_to_room_photo2.jpg"],"Beds":2,"Bathrooms":1,"Category":"Deluxe","MaxOccupants":4,"Smoking":false,"Amenities":{"WiFi":true,"AirConditioning":true,"RoomService":true,"AccessibilityStatus":"Wheelchair accessible"},"Availability":[{"RoomNumber":"101","AvailablePeriods":[{"StartDate":"2023-12-01","EndDate":"2023-12-15"}]}]}]}
This is an example document for a listings collection. I need a mongo shell query that will output a distinct list of property amenities.Output:
db.listings.distinct("Property.PropertyAmenities", {"Property.PropertyAmenities": {$exists: true}})This first result didn’t quite work — it produced an array of documents. My next prompt took some retries.
Prompt
Produce an array of distinct keys for all "Property.PropertyAmenities" documents in the listings collection.Output
db.listings.aggregate([
{
$project: {
amenities: {
$objectToArray: "$Property.PropertyAmenities"
}
}
},
{
$unwind: "$amenities"
},
{
$group: {
_id: null,
keys: {
$addToSet: "$amenities.k"
}
}
},
{
$project: {
_id: 0
}
}
])This results in
[
{
keys: [
'BusinessCenter',
'YogaClasses',
'WaterSportsFacilities',
'SurfingLessons',
'CarRentalDesk',
'Sauna',
'GameRoom',
'OutdoorGrill',
'SpaServices',
'MeditationAreas',
'Gym',
'NonSmoking',
'Casino',
'ChildrensPlayArea',
'RooftopTerrace',
'BikeRental',
'RoomService',
'BanquetFacilities',
'ChildcareServices',
'CurrencyExchangeService',
'Photos',
'AirportShuttle',
'ConferenceRooms',
'TurkishBath',
'GolfCourse',
'TennisCourts',
'Solarium',
'AccessibilityFeatures',
'SkiInSkiOutAccess',
'ConciergeService',
'HotTub',
'Library',
'PicnicArea',
'ElectricVehicleChargingStation',
'LiveEntertainment',
'Restaurant',
'SwimmingPool',
'Parking',
'Bar',
'WiFi',
'GiftShop',
'PetFriendly',
'FirePit',
'Garden',
'LaundryService',
'24HourFrontDesk',
'CookingClasses',
'DivingCenter',
'Snorkeling',
'PrivateBeachAccess'
]
}
]Which I can work with.
Next, we need to be able to search by amenities.
Prompt
User
This is a working mongo shell query.
db.listings.find({
"Property.GPS": { $near: { $geometry: { type: "Point", coordinates: [
41.05314902455228,
29.031734625650678
]
}
}
}
},
{
"Property.HotelName": 1,
"Property.GPS": 1
}).limit(3);
The collection has a Property Key "PropertyAmenities.KEY" where KEY is a string. KEY can be anything.
We need a query that will return results when the provided KEY is TRUE.Output:
db.listings.find(
{
"PropertyAmenities.WiFi": true,
"Property.GPS": {
$near: {
$geometry: {
type: "Point",
coordinates: [41.05314902455228, 29.031734625650678]
}
}
}
},
{
"Property.HotelName": 1,
"Property.GPS": 1
}
).limit(3);
This works. But it won’t result in a very effecient query.
Prompt:
User
In MongoDB, is it possible to provide an array of strings and see if any key exists as true in a query?I already knew this was possible.
const keysToCheck = ["WiFi", "Pool", "Jacuzzi"];
Yes, in MongoDB, you can provide an array of strings and check if any of those keys exist as true in a query using the $in operator combined with the $exists operator. Here's an example query:
Suppose you have an array of keys like ["key1", "key2", "key3"], and you want to find documents where any of these keys exist and are true in the "PropertyAmenities" sub-document:
db.listings.find(
{
$or: keysToCheck.map((key) => ({
[`Property.PropertyAmenities.${key}`]: true,
})),
"Property.GPS": {
$near: {
$geometry: {
type: "Point",
coordinates: [41.05314902455228, 29.031734625650678]
}
}
}
},
{
"Property.HotelName": 1,
"Property.GPS": 1
}
).limit(3);
In this query:
We use the $or operator to specify multiple conditions. We map over the keysToCheck array and create an array of conditions where each condition checks if a specific key exists and is true in the "PropertyAmenities" sub-document.
The $near part of the query remains the same as in your previous example, ensuring that the "Property.GPS" is near the specified coordinates.
This query will return documents where any of the specified keys in keysToCheck exist and are true in the "PropertyAmenities" sub-document, and the "Property.GPS" is near the specified coordinates.After testing, this was confirmed to work.
We now have queries that will:
- Search by distance from GPS coordinates
- Search by amenity.
We’ll also need queries for searching by date and price.
This is an example document for a mongodb collection called 'listings'
{"Property":{"HotelName":"Bosporus Bliss Retreat","GPS":[41.05314902455228, 29.031734625650678],"Address":"101 Sultan Street","City":"Istanbul","LocalRegion":"Istanbul","TotalHotelRooms":80,"Photos":["url_to_photo1.jpg","url_to_photo2.jpg"],"PropertyAmenities":{"Photos":["url_to_amenity_photo1.jpg","url_to_amenity_photo2.jpg"],"WiFi":true,"SwimmingPool":true,"Parking":true,"Gym":true,"SpaServices":true,"Restaurant":true,"Bar":true,"RoomService":true,"LaundryService":true,"BusinessCenter":true,"ConferenceRooms":true,"BanquetFacilities":true,"ChildcareServices":true,"ConciergeService":true,"CurrencyExchangeService":true,"GiftShop":true,"AccessibilityFeatures":true,"AirportShuttle":true,"CarRentalDesk":true,"Casino":false,"GolfCourse":false,"TennisCourts":false,"PrivateBeachAccess":false,"SkiInSkiOutAccess":false,"BikeRental":true,"ElectricVehicleChargingStation":true,"PetFriendly":true,"NonSmoking":true,"24HourFrontDesk":true,"Sauna":true,"TurkishBath":true,"Solarium":true,"HotTub":true,"Library":true,"GameRoom":true,"ChildrensPlayArea":true,"OutdoorGrill":true,"PicnicArea":true,"FirePit":true,"Garden":true,"RooftopTerrace":true,"LiveEntertainment":false,"CookingClasses":false,"YogaClasses":false,"MeditationAreas":false,"WaterSportsFacilities":false,"DivingCenter":false,"Snorkeling":false,"SurfingLessons":false},"Rules":{"CheckInTime":"15:00","CheckOutTime":"11:00","SmokingAllowed":false,"PetsAllowed":true,"BreakfastIncluded":true}},"RoomType":[{"Photos":["url_to_room_photo1.jpg","url_to_room_photo2.jpg"],"Beds":2,"Bathrooms":1,"Category":"Standard","MaxOccupants":2,"Smoking":false,"Amenities":{"WiFi":true,"AirConditioning":true,"RoomService":true,"AccessibilityStatus":"Wheelchair accessible"},"Availability":[{"RoomNumber":"301","AvailablePeriods":[{"StartDate":"2023-12-01","EndDate":"2023-12-15"}]}]}]}
Write a shell query that will allow us to return results based on an input date.Output
To return results from the "listings" collection based on an input date, you can use the MongoDB shell and the `$elemMatch` operator to filter the "RoomType" array based on the input date range. Here's a sample shell query that filters listings where a room is available for the input date:
db.listings.find({
"RoomType.Availability": {
$elemMatch: {
StartDate: { $lte: "2023-12-10" }, // Replace with your input date
EndDate: { $gte: "2023-12-10" } // Replace with your input date
}
}
})
In this query:
- `RoomType.Availability` is used to access the array of available periods for each room.
- `$elemMatch` is used to specify conditions that the array elements should meet.
- We compare the input date "2023-12-10" with the "StartDate" and "EndDate" of each available period to check if the input date falls within any of the available periods.
Make sure to replace "2023-12-10" with your actual input date in the query. This query will return all listings that have at least one room available for the specified input date.It didn’t quite get it — but looking at the reason why I made a simple adjustment: "RoomType.Availability.AvailablePeriods"
db.listings.find({ "RoomType.Availability.AvailablePeriods": { $elemMatch: { StartDate: { $lte: "2023-12-10" }, /* Replace with your input date*/ EndDate: { $gte: "2023-12-10" } /* Replace with your input date*/ } } }ChatGPT will frequently make small mistakes like this. Sometimes it’s easier to just fix it yourself than try to get it from a prompt.
Finally, I noticed that my sample data did not have a field for NightlyPrice. This is a pretty big oversight.
Prompt
I need a mongo shell query to add a field "RoomPrice" to every RoomType subdocument in my listings collection.Outpit
You can use the `$addFields` aggregation stage to add the "RoomPrice" field to every "RoomType" subdocument in your "listings" collection. Here's a sample MongoDB shell query to accomplish this:
db.listings.aggregate([
{
$addFields: {
"RoomType.RoomPrice": 150
}
}
])In this query:
$addFieldsis used to add or set fields within documents.- We specify the path to the "RoomType" array and add the "RoomPrice" field to each subdocument. You can replace
1500with the desired room price value.
This query will update all documents in the "listings" collection by adding the "RoomPrice" field to every "RoomType" subdocument.
Worked well enough.
Now by price
db.listings.find({ "Property.RoomType.RoomPrice": { "$gte": 50, "$lte": 250 } })Now for our most complex query yet. We want an aggregate command that will return documents based on all these critera.
In mongo shell, define min_price as 20, max_price as 250, GPS Coordinates as [41.025570330702735, 28.977524665462145], property_amenities as ["Wifi", "SwimmingPool"], StartDate: "2023-12-01:, EndDate: ""EndDate": "2023-12-15" and create a mongo aggregate command that returns results within 3 miles of the GPS coordinates
Here is the sample document:
{
"Property": {
"HotelName": "Bosporus Bliss Retreat",
"GPS": [
41.05314902455228,
29.031734625650678
],
"Address": "101 Sultan Street",
"City": "Istanbul",
"LocalRegion": "Istanbul",
"TotalHotelRooms": 80,
"Photos": [
"url_to_photo1.jpg",
"url_to_photo2.jpg"
],
"PropertyAmenities": {
"Photos": [
"url_to_amenity_photo1.jpg",
"url_to_amenity_photo2.jpg"
],
"WiFi": true,
"SwimmingPool": true,
},
"Rules": {
"CheckInTime": "15:00",
"CheckOutTime": "11:00",
"SmokingAllowed": false,
"PetsAllowed": true,
"BreakfastIncluded": true
}
},
"RoomType": [
{
"Photos": [
"url_to_room_photo1.jpg",
"url_to_room_photo2.jpg"
],
"Beds": 2,
"Bathrooms": 1,
"Category": "Standard",
"MaxOccupants": 2,
"Smoking": false,
"Amenities": {
"WiFi": true,
"AirConditioning": true,
"RoomService": true,
"AccessibilityStatus": "Wheelchair accessible"
},
"Availability": [
{
"RoomNumber": "301",
"AvailablePeriods": [
{
"StartDate": "2023-12-01",
"EndDate": "2023-12-15"
}
]
}
]
}
]
}And the output:
Certainly, here is an aggregate command that filters the "listings" collection in the "throwaway-1" database according to your criteria:
```javascript
db.listings.aggregate([
{
$geoNear: {
near: { type: "Point", coordinates: [41.025570330702735, 28.977524665462145] },
distanceField: "distance",
maxDistance: 4828, // Approx 3 miles in meters
spherical: true
}
},
{
$match: {
"Property.PropertyAmenities.WiFi": true,
"Property.PropertyAmenities.SwimmingPool": true,
"RoomType.Availability.AvailablePeriods": {
$elemMatch: {
StartDate: { $lte: "2023-12-01" },
EndDate: { $gte: "2023-12-15" }
}
}
}
},
{
$unwind: "$RoomType"
},
{
$match: {
"RoomType.Availability.AvailablePeriods": {
$elemMatch: {
StartDate: { $lte: "2023-12-01" },
EndDate: { $gte: "2023-12-15" }
}
}
}
},
{
$group: {
_id: "$Property.HotelName",
minPrice: { $min: "$RoomType.Price" },
maxPrice: { $max: "$RoomType.Price" },
details: { $first: "$$ROOT" }
}
},
{
$match: {
minPrice: { $gte: 20 },
maxPrice: { $lte: 250 }
}
}
]);
Make sure your collection has a 2dsphere index on the Property.GPS field to use the $geoNear stage:
db.listings.createIndex({ "Property.GPS": "2dsphere" });
Please adjust the index creation command to fit your exact field if necessary.Which didn't work. Instead of trying to get this right through prompt engineering, I decided to use this as a starting point and just debug it. After some tweaking here's what I came up with:
db.listings.aggregate([
{
$geoNear: {
near: { type: "Point", coordinates: [41.025570330702735, 28.977524665462145] },
distanceField: "distance",
maxDistance: 4828, // Approx 3 miles in meters
spherical: true
}
},
{
$match: {
"Property.PropertyAmenities.WiFi": true,
"Property.PropertyAmenities.SwimmingPool": true,
"RoomType.Availability.AvailablePeriods": {
$elemMatch: {
StartDate: { $gte: "2023-12-01" },
EndDate: { $lte: "2023-12-15" }
}
}
}
},
{
$unwind: "$RoomType"
},
{
$match: {
"RoomType.Availability.AvailablePeriods": {
$elemMatch: {
StartDate: { $lte: "2023-12-01" },
EndDate: { $gte: "2023-12-15" }
}
}
}
},
{
$group: {
_id: "$Property.HotelName",
minPrice: { $min: "$RoomType.RoomPrice" },
maxPrice: { $max: "$RoomType.RoomPrice" },
details: { $first: "$$ROOT" }
}
},
{
$match: {
minPrice: { $gte: 20 },
maxPrice: { $lte: 250 }
}
}
]);In this case, it was just faster for me to debug it myself. The issues were fairly small. But the end result is a query that will:
- Allow us to search by price, distance and amenities.
- Get an array of strings of our amenities
When we get to our actual API implementation, we’ll expand the capabilities to include search by city and other factors available in the data. But this is good enough for our purposes.