MongoDB Find, Projection and Filter
FIND
In order to run these examples I prepered a repository with a docker compose file to run the MongoDB database and the backup of the databases. Take a look at the readme file: MongoDB Samples
Database to run the examples
For this article I will use the sample_airbnb database of mongodb-samples repository.
Basic example
The most basic way to retrive data from MongoDB is with the find command:
db.getCollection('listingsAndReviews').find({})The argument is empty for now, but with this argument we can filter the data.
It will retrieve all the documents with all of their fields from the collection lisntingAndRevies.
But retrieving all the fields isn’t a good practice, because usually we just need to retrieve the fields that we will use.
We can achive this by projecting the desired fields
Select which fields should be retrieved
We can add a second parameter to set each desired field
db.getCollection('listingsAndReviews').find({}, { bedrooms: 1, accommodates: 1, bed_type: 1, beds: 1 })+--------+------------+--------+--------+----+
|_id |accommodates|bed_type|bedrooms|beds|
+--------+------------+--------+--------+----+
|10006546|8 |Real Bed|3 |5 |
|10009999|4 |Real Bed|1 |2 |
|1001265 |2 |Real Bed|1 |1 |
|10021707|1 |Real Bed|1 |1 |
|10030955|2 |Real Bed|1 |1 |
+--------+------------+--------+--------+----+As you can see the field _id is retrieved automatically, if you want to avoid this behavior you must set the _id to 0 like this:
db.getCollection('listingsAndReviews').find({}, { _id: 0, bedrooms: 1, accommodates: 1, bed_type: 1, beds: 1 })+------------+--------+--------+----+
|accommodates|bed_type|bedrooms|beds|
+------------+--------+--------+----+
|8 |Real Bed|3 |5 |
|4 |Real Bed|1 |2 |
|2 |Real Bed|1 |1 |
+------------+--------+--------+----+If you want to filter the data you can create a filter this way:
db.getCollection('listingsAndReviews').find({ bed_type: 'Real Bed' }, { bedrooms: 1, accommodates: 1, bed_type: 1, beds: 1 })+--------+------------+--------+--------+----+
|_id |accommodates|bed_type|bedrooms|beds|
+--------+------------+--------+--------+----+
|10006546|8 |Real Bed|3 |5 |
|10009999|4 |Real Bed|1 |2 |
|1001265 |2 |Real Bed|1 |1 |
|10021707|1 |Real Bed|1 |1 |
|10030955|2 |Real Bed|1 |1 |
+--------+------------+--------+--------+----+With this command the MongoDB database will just retrieve the documents where the field bed_type is equal to Real Bed.
Comparison Operators
For more elaborate operations there are some comparison operators that we can use with the find command, like:
$eq (equal)
$ne (not equal)
$gt (greater than)
$gte (greater than or equal)
$lt (lower than)
$lte (lower than or equal)
$in (in)
$nin (not in)Adding a condition
We can add a condition to the find command to just retrieve documents where the field beds is greater than 1.
db.getCollection('listingsAndReviews').find({ beds: { $gt: 1 } }, { beds: 1 })+--------+----+
|_id |beds|
+--------+----+
|10006546|5 |
|10009999|2 |
|10038496|3 |
|10047964|6 |
|10051164|8 |
|10057447|2 |
+--------+----+Now just documents where the field beds is greater than or equal to 1.
db.getCollection('listingsAndReviews').find({ beds: { $gte: 1 } }, { beds: 1 })+--------+----+
|_id |beds|
+--------+----+
|10006546|5 |
|10009999|2 |
|1001265 |1 |
|10021707|1 |
|10030955|1 |
|1003530 |1 |
+--------+----+Logical Query Operators
In order to create more complex filters you can use logical query operators
$and (all criteria must be true)
$or (at least one criteria must be true)
$nor (none of the criteria can be true)
$not (inverts the condition)Defining a range for the number of beds:
db.getCollection('listingsAndReviews').find({ $and: [
{ beds: { $gte: 1 } },
{ beds: { $lte: 3 } }
] }, { beds: 1 })+--------+----+
|_id |beds|
+--------+----+
|10009999|2 |
|1001265 |1 |
|10021707|1 |
|10030955|1 |
|1003530 |1 |
|10038496|3 |
|10057447|2 |
+--------+----+Sorting the result
Usually you will need to sort the result.
To perform a sort you just need to use the find function and then the sort function.
Use 1 for an ascending sort.
db.getCollection('listingsAndReviews').find({ $and: [
{ beds: { $gte: 1 } },
{ beds: { $lte: 3 } }
] }, { beds: 1 }).sort({ beds: 1 })+--------+----+
|_id |beds|
+--------+----+
|26488387|1 |
|26361065|1 |
|26365414|1 |
|26366454|1 |
|26380958|1 |
|26391176|1 |
+--------+----+Use -1 for a descending sort
db.getCollection('listingsAndReviews').find({ $and: [
{ beds: { $gte: 1 } },
{ beds: { $lte: 3 } }
] }, { beds: 1 }).sort({ beds: -1 })+--------+----+
|_id |beds|
+--------+----+
|4593396 |3 |
|1912695 |3 |
|4458669 |3 |
|19080306|3 |
|4515942 |3 |
+--------+----+Paginating the result
Like in relational databases, you can use the skip, limit and sort functions to paginate the result.
db.getCollection('listingsAndReviews').find({ $and: [
{ beds: { $gte: 1 } },
{ beds: { $lte: 3 } }
] }, { beds: 1 }).sort({ beds: -1 }).skip(10).limit(3)+--------+----+
|_id |beds|
+--------+----+
|10628126|3 |
|102995 |3 |
|10038496|3 |
+--------+----+Filtering inside an array
If you want to find the results that have a specific value within the array, do the following:
db.getCollection('listingsAndReviews').find({ amenities: "Wifi" }, { amenities: 1 })+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|_id |amenities |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|10006546|["TV", "Cable TV", "Wifi", "Kitchen", "Paid parking off premises", "Smoking allowed", "Pets allowed", "Buzzer/wireless intercom", "Heating", "Family/kid friendly", "Washer", "First aid kit", "Fire extinguisher", "Essentials", "Hangers", "Hair dryer", "Iron", "Pack ’n Play/travel crib", "Room-darkening shades", "Hot water", "Bed linens", "Extra pillows and blankets", "Microwave", "Coffee maker", "Refrigerator", "Dishwasher", "Dishes and silverware", "Cooking basics", "Oven", "Stove", "Cleaning before checkout", "Waterfront"] |
|10009999|["Wifi", "Wheelchair accessible", "Kitchen", "Free parking on premises", "Smoking allowed", "Hot tub", "Buzzer/wireless intercom", "Family/kid friendly", "Washer", "First aid kit", "Essentials", "Hangers", "Hair dryer", "Iron", "Laptop friendly workspace"] |
|1001265 |["TV", "Cable TV", "Wifi", "Air conditioning", "Pool", "Kitchen", "Free parking on premises", "Elevator", "Hot tub", "Washer", "Dryer", "Essentials", "Shampoo", "Hangers", "Hair dryer", "Iron", "Laptop friendly workspace", "Self check-in", "Lockbox", "Hot water", "Bed linens", "Extra pillows and blankets", "Ethernet connection", "Microwave", "Coffee maker", "Refrigerator", "Dishes and silverware", "Cooking basics", "Stove", "BBQ grill", "Garden or backyard", "Well-lit path to entrance", "Disabled parking spot", "Step-free access", "Wide clearance to bed", "Step-free access"]|
|10021707|["Internet", "Wifi", "Air conditioning", "Kitchen", "Buzzer/wireless intercom", "Heating", "Smoke detector", "Carbon monoxide detector", "Essentials", "Lock on bedroom door"] |
|10030955|["TV", "Cable TV", "Internet", "Wifi", "Air conditioning", "Pool", "Kitchen", "Free parking on premises", "Doorman", "Gym", "Elevator", "Buzzer/wireless intercom", "Family/kid friendly", "Washer", "Essentials", "24-hour check-in"] |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+If you need documents that have multiple values within an array you can do it this way:
db.getCollection('listingsAndReviews').find({ amenities: { $all: [ "Wifi", "Wheelchair accessible", "Elevator" ] } }, { amenities: 1 })+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|_id |amenities |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|10069642|["TV", "Cable TV", "Internet", "Wifi", "Air conditioning", "Wheelchair accessible", "Kitchen", "Free parking on premises", "Doorman", "Gym", "Elevator", "Buzzer/wireless intercom", "Family/kid friendly", "Washer", "Essentials", "Hair dryer", "Iron"] |
|10082307|["TV", "Internet", "Wifi", "Air conditioning", "Wheelchair accessible", "Doorman", "Elevator", "Family/kid friendly", "Shampoo", "Hangers", "Hair dryer", "Iron"] |
|10115921|["TV", "Cable TV", "Internet", "Wifi", "Air conditioning", "Wheelchair accessible", "Kitchen", "Paid parking off premises", "Smoking allowed", "Doorman", "Elevator", "Buzzer/wireless intercom", "Heating", "Family/kid friendly", "Suitable for events", "Dryer", "Smoke detector", "Carbon monoxide detector", "First aid kit", "Safety card", "Fire extinguisher", "Essentials", "Shampoo", "24-hour check-in", "Hangers", "Hair dryer", "Iron", "Laptop friendly workspace", "Self check-in", "Building staff", "Crib", "Hot water", "Luggage dropoff allowed", "Long term stays allowed"]|
|10116256|["TV", "Internet", "Wifi", "Air conditioning", "Wheelchair accessible", "Kitchen", "Paid parking off premises", "Smoking allowed", "Doorman", "Elevator", "Buzzer/wireless intercom", "Heating", "Family/kid friendly", "Suitable for events", "Washer", "Dryer", "Smoke detector", "Carbon monoxide detector", "Fire extinguisher", "Essentials", "Shampoo", "24-hour check-in", "Hangers", "Hair dryer", "Iron", "Laptop friendly workspace", "Self check-in", "Building staff", "Hot water", "Luggage dropoff allowed", "Long term stays allowed"] |
|10116578|["Cable TV", "Wifi", "Air conditioning", "Wheelchair accessible", "Kitchen", "Free parking on premises", "Doorman", "Elevator", "Buzzer/wireless intercom", "Family/kid friendly", "Washer", "24-hour check-in", "Iron"] |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+Nest fields
If you need to filter nested fields, you can do this way:
db.getCollection('listingsAndReviews').find({ "address.street": "Rio de Janeiro, Rio de Janeiro, Brazil" }, { amenities: 1 })+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|_id |amenities |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|10009999|["Wifi", "Wheelchair accessible", "Kitchen", "Free parking on premises", "Smoking allowed", "Hot tub", "Buzzer/wireless intercom", "Family/kid friendly", "Washer", "First aid kit", "Essentials", "Hangers", "Hair dryer", "Iron", "Laptop friendly workspace"] |
|10030955|["TV", "Cable TV", "Internet", "Wifi", "Air conditioning", "Pool", "Kitchen", "Free parking on premises", "Doorman", "Gym", "Elevator", "Buzzer/wireless intercom", "Family/kid friendly", "Washer", "Essentials", "24-hour check-in"] |
|10038496|["TV", "Cable TV", "Internet", "Wifi", "Air conditioning", "Kitchen", "Paid parking off premises", "Smoking allowed", "Doorman", "Elevator", "Buzzer/wireless intercom", "Family/kid friendly", "Washer", "Fire extinguisher", "Essentials", "Hangers", "Hair dryer", "Iron", "Laptop friendly workspace", "Hot water", "Bed linens", "Extra pillows and blankets", "Microwave", "Coffee maker", "Refrigerator", "Dishes and silverware", "Oven", "Stove", "Long term stays allowed", "Wide hallway clearance", "Host greets you"]|
|10051164|["TV", "Cable TV", "Internet", "Wifi", "Air conditioning", "Kitchen", "Smoking allowed", "Family/kid friendly", "First aid kit", "Fire extinguisher", "Essentials"] |
|10069642|["TV", "Cable TV", "Internet", "Wifi", "Air conditioning", "Wheelchair accessible", "Kitchen", "Free parking on premises", "Doorman", "Gym", "Elevator", "Buzzer/wireless intercom", "Family/kid friendly", "Washer", "Essentials", "Hair dryer", "Iron"] |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+Verify if a field exist
As in MongoDB we do not have a fixed model of fields, some documents can have a list of fields and other documents can have this list plus other fields. So if you need to filter documents verifying if a specific field is present, you can do this way:
db.getCollection('listingsAndReviews').find({ transit: { $exists: true } }, { transit: 1 })+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|_id |transit |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|10006546|Transport: • Metro station and S. Bento railway 5min; • Bus stop a 50 meters; • Lift Guindais (Funicular) 50 meters; • Tuc Tuc-to get around the city; • Buses tourist; • Cycling through the marginal drive; • Cable car in Gaia, overlooking the Port (just cross the bridge).|
|10009999|Easy access to transport (bus, taxi, car) and easy free parking around. Very close to Gávea, Leblon, Ipanema, Copacabana and Botafogo. |
|1001265 |Honolulu does have a very good air conditioned bus system. |
|10021707| |
|10030955| |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+