Estimated completion time: 5 minutes
You can run operations using the MongoDB Query Language (MQL) which includes most, but not all standard server commands. To learn which MQL operations are supported, see the MQL Support documentation.
Note
The Atlas Data Federation sample datasets are read-only.
Prerequisites
To complete this part of the tutorial, you must have completed:
You must be connected to your federated database instance with the MongoDB Shell before running the following queries.
Queries
The following example queries apply to a federated database instance that you deployed with the Feed Downstream Systems wizard.
If you have not done so already, complete the following to run the following queries successfully:
Load sample data into the Atlas cluster you're using as a data source for your federated database instance.
Configure the downstream system to extract data from you Atlas cluster into your AWS S3 bucket.
Before running the queries, switch to the sample_airbnb database:
use sample_airbnb
Find the number of AirBnB offerings with 3 bedrooms and a high review score:
1 db.listingsAndReviews.aggregate([{$match: {"bedrooms" : 3, "review_scores.review_scores_rating": {$gt: 79}} }, {$count: "numProperties"}])
1 { 2 "numProperties" : 295 3 }
Find properties with 3 bedrooms and include only the name and bedrooms fields in the results. Sort the returned documents by customer review rating. Limit the number of documents returned to 5:
1 db.listingsAndReviews.find({"bedrooms": 3}, {"name": 1, "bedrooms": 1}).sort({review_scores_rating: -1}).limit(5)
1 [ 2 { 3 _id: '20045679', 4 name: 'House Near Espinho/Santa Maria Feira', 5 bedrooms: Long("3") 6 }, 7 { 8 _id: '19760228', 9 name: 'Apartment Salva - 3 bedroom in Poble Sec', 10 bedrooms: Long("3") 11 }, 12 { 13 _id: '19768051', 14 name: 'Ultra Modern Pool House Maroubra', 15 bedrooms: Long("3") 16 }, 17 { 18 _id: '19877706', 19 name: 'Big 3 Bedroom Garden Level Apartment Near Subway', 20 bedrooms: Long("3") 21 }, 22 { 23 _id: '6291063', 24 name: 'Beautiful Tropical Oasis near beach in Kailua', 25 bedrooms: Long("3") 26 } 27 ]
Before running the queries, switch to the sample_analytics database:
use sample_analytics
Find users who have purchased Commodity with a limit of 10000. Limit the number of documents returned to 3:
1 db.accounts.find({"limit": {$eq: 10000}, "products": "Commodity"}).limit(3)
1 { 2 "_id" : ObjectId("5ca4bbc7a2dd94ee5816238d"), 3 "account_id" : 557378, 4 "limit" : 10000, 5 "products" : [ 6 "InvestmentStock", 7 "Commodity", 8 "Brokerage", 9 "CurrencyService" 10 ] 11 } 12 { 13 "_id" : ObjectId("5ca4bbc7a2dd94ee58162390"), 14 "account_id" : 278603, 15 "limit" : 10000, 16 "products" : [ 17 "Commodity", 18 "InvestmentStock" 19 ] 20 } 21 { 22 "_id" : ObjectId("5ca4bbc7a2dd94ee5816239b"), 23 "account_id" : 870466, 24 "limit" : 10000, 25 "products" : [ 26 "Derivatives", 27 "Brokerage", 28 "Commodity", 29 "InvestmentStock" 30 ] 31 }
Find customers whose birthdate is before 1990-01-01 and limit the number of documents returned to 5:
1 db.customers.find({"birthdate": {$lt: ISODate("1990-01-01T22:15:34.000+00:00")}}, {"name": 1, "birthdate": 1, "email": 1}).limit(5)
1 { 2 "_id" : ObjectId("5ca4bbcea2dd94ee58162a6a"), 3 "name" : "Katherine David", 4 "birthdate" : ISODate("1988-06-20T22:15:34Z"), 5 "email" : "timothy78@hotmail.com" 6 } 7 { 8 "_id" : ObjectId("5ca4bbcea2dd94ee58162a6c"), 9 "name" : "Brad Cardenas", 10 "birthdate" : ISODate("1977-05-06T21:57:35Z"), 11 "email" : "dustin37@yahoo.com" 12 } 13 { 14 "_id" : ObjectId("5ca4bbcea2dd94ee58162a74"), 15 "name" : "Dr. Angela Brown", 16 "birthdate" : ISODate("1977-06-19T20:35:52Z"), 17 "email" : "michaelespinoza@gmail.com" 18 } 19 { 20 "_id" : ObjectId("5ca4bbcea2dd94ee58162a76"), 21 "name" : "Lauren Clark", 22 "birthdate" : ISODate("1980-10-28T16:25:59Z"), 23 "email" : "briannafrost@yahoo.com" 24 } 25 { 26 "_id" : ObjectId("5ca4bbcea2dd94ee58162a77"), 27 "name" : "Jacqueline Haynes", 28 "birthdate" : ISODate("1982-09-01T07:12:57Z"), 29 "email" : "virginia36@hotmail.com" 30 }
Find transaction details for user whose account ID is 557378 and use the $sort stage to sort on the transactions.symbol field:
1 db.accounts.aggregate([ { $match: {"account_id": 557378}},{$sort: {"transactions.symbol": -1}} ])
1 { 2 "_id" : ObjectId("5ca4bbc7a2dd94ee5816238d"), 3 "account_id" : 557378, 4 "limit" : 10000, 5 "products" : [ "InvestmentStock", "Commodity", "Brokerage", "CurrencyService" ] 6 }
Before running the queries, switch to the sample_mflix database:
use sample_mflix
Find all movies between the years 2010 and 2015 and include only the _id, title, and year fields in the results. Limit the number of documents returned to 5.
1 db.movies.find({"type": "movie", "year": {$gt: 2010, $lt: 2015} }, {"title": 1, "year": 1 }).limit(5)
1 { 2 "_id" : ObjectId("573a13b8f29313caabd4c8c5"), 3 "year" : 2011, 4 "title" : "Thor" 5 } 6 { 7 "_id" : ObjectId("573a13b0f29313caabd34a3e"), 8 "year" : 2011, 9 "title" : "Cowboys & Aliens" 10 } 11 { 12 "_id" : ObjectId("573a13b8f29313caabd4ca3f"), 13 "title" : "Red Dog", 14 "year" : 2011 15 } 16 { 17 "_id" : ObjectId("573a13b8f29313caabd4d58c"), 18 "title" : "Jack and Jill", 19 "year" : 2011 20 } 21 { 22 "_id" : ObjectId("573a13b8f29313caabd4d5b5"), 23 "year" : 2011, 24 "title" : "Take Me Home Tonight" 25 }
Find the sessions details for a user specified by ID.
1 db.sessions.find({"user_id": "t3qulfeem@kwiv5.6ur"})
1 { 2 "_id" : ObjectId("5a97f9c91c807bb9c6eb5fb4"), 3 "user_id" : "t3qulfeem@kwiv5.6ur", 4 "jwt" : "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NieyJpYXQiOjE1MTk5MDkzMjEsIm5iZiI6MTUxOTkwOTMyMSwianRpIjoiNmJlZDAwMWYtNTFiYi00NzVhLTgAtMDcwNGE5Mjk0MWZlIiwiZXhwIjoxNTE5OTEwMjIxLCJpZGVudGl0eSI6eyJlbWFpbCI6InQzcXVsZmVlbd2l2NS42dXIiLCJuYW1lIjoiM2lveHJtZnF4IiwicGFzc3dvcmQiOm51bGx9LCJmcmVzaCI6ZmFsc2UsInRUiOiJhY2Nlc3MiLCJ1c2VyX2NsYWltcyI6eyJ1c2VyIjp7ImVtYWlsIjoidDNxdWxmZWVtQGt3aXY1LjZ1cIm5hbWUiOiIzaW94cm1mcXgiLCJwYXNzd29yZCI6bnVsbH19ejtr_NyZyBronWMKuE0RFTjWej--T0zGrdc_iymGtVs" 5 }
Find the theater specified by its id and zipcode.
1 db.theaters.findOne({$and: [{"_id": ObjectId("59a47286cfa9a3a73e51e763")}, {"location.address.zipcode": "93933"}]})
1 { 2 "_id" : ObjectId("59a47286cfa9a3a73e51e763"), 3 "theaterId" : 1061, 4 "location" : { 5 "address" : { 6 "street1" : "101 General Stillwell Dr", 7 "city" : "Marina", 8 "state" : "CA", 9 "zipcode" : "93933" 10 }, 11 "geo" : { 12 "type" : "Point", 13 "coordinates" : [ 14 -121.81196, 15 36.66708 16 ] 17 } 18 } 19 }
Find all users whose last name is Lannister and limit the number of documents returned to 5.
1 db.users.find({ name: /Lannister/ }).limit(5)
1 { 2 "_id" : ObjectId("59b99db5cfa9a34dcd7885b8"), 3 "name" : "Jaime Lannister", 4 "email" : "nikolaj_coster-waldau@gameofthron.es", 5 "password" : "$2b$12$6vz7wiwO.EI5Rilvq1zUc./9480gb1uPtXcahDxIadgyC3PS8XCUK" 6 } 7 { 8 "_id" : ObjectId("59b99db6cfa9a34dcd7885ba"), 9 "name" : "Cersei Lannister", 10 "email" : "lena_headey@gameofthron.es", 11 "password" : "$2b$12$FExjgr7CLhNCa.oUsB9seub8mqcHzkJCFZ8heMc8CeIKOZfeTKP8m" 12 } 13 { 14 "_id" : ObjectId("59b99dbdcfa9a34dcd7885c7"), 15 "name" : "Tyrion Lannister", 16 "email" : "peter_dinklage@gameofthron.es", 17 "password" : "$2b$12$xtHwQNXYlQzP2REobUDlzuQimjzBlXrTx1GnwP.xkfULeuuUpRxa2" 18 } 19 { 20 "_id" : ObjectId("59b99dc2cfa9a34dcd7885d2"), 21 "name" : "Tywin Lannister", 22 "email" : "charles_dance@gameofthron.es", 23 "password" : "$2b$12$/i04T5yEJvmsBhF0Jd.kJOk3ZhRzezbTU7ASEM5o43Xxsa4o6IgEy" 24 } 25 { 26 "_id" : ObjectId("59b99dcecfa9a34dcd7885ea"), 27 "name" : "Lancel Lannister", 28 "email" : "eugene_simon@gameofthron.es", 29 "password" : "$2b$12$mNWiHoOqOWQser3s6ezqZeTU5vhskTq.K7xkeTA2P.CIfoWsHvonO" 30 }
Before running the queries, switch to the sample_training database:
use sample_training
Find all states with a population greater than 10 million.
1 db.zips.aggregate([{$group: { _id: "$state", totalPop: { $sum: "$pop" }}}, {$match: { totalPop: { $gte: 10*1000*1000 }}}])
1 { "_id" : "NY", "totalPop" : 17990455 } 2 { "_id" : "FL", "totalPop" : 12937926 } 3 { "_id" : "PA", "totalPop" : 11881643 } 4 { "_id" : "CA", "totalPop" : 29760021 } 5 { "_id" : "TX", "totalPop" : 16986510 } 6 { "_id" : "IL", "totalPop" : 11430602 } 7 { "_id" : "OH", "totalPop" : 10847115 }
Find all trips taken by customers that were less than 70 minutes.
1 db.trips.find({"tripduration": { "$lte" : 70 }, "usertype": { "$eq": "Customer" }})
1 { 2 "_id" : ObjectId("572bb8232b288919b68af7cd"), 3 "tripduration" : 66, 4 "start station id" : 460, 5 "start station name" : "S 4 St & Wythe Ave", 6 "end station id" : 460, 7 "end station name" : "S 4 St & Wythe Ave", 8 "bikeid" : 23779, 9 "usertype" : "Customer", 10 "birth year" : "", 11 "start station location" : { 12 "type" : "Point", 13 "coordinates" : [ 14 -73.96590294, 15 40.71285887 16 ] 17 }, 18 "end station location" : { 19 "type" : "Point", 20 "coordinates" : [ 21 -73.96590294, 22 40.71285887 23 ] 24 }, 25 "start time" : ISODate("2016-01-02T11:49:11Z"), 26 "stop time" : ISODate("2016-01-02T11:50:18Z") 27 }
Summary
Congratulations! You just set up a federated database instance, created a database and collections from data stored in an S3 bucket, and queried the data using MQL commands.
For more information on federated database instances, see Atlas Data Federation.
Note
When you dynamically generate collections from filenames, the number of collections is not accurately reported in the Data Federation view.