Docs Menu
Docs Home
/
Atlas
/
Query Federated Data
/
Get Started

Run Queries Against Your Federated Database Instance

On this page

  • Prerequisites
  • Queries
  • Summary

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 will need to have completed:

You must be connected to your federated database instance with the MongoDB Shell before running the following queries.

Queries

These example queries differ depending on whether you used the Feed Downstream Systems, Explore with Sample Data, or the Query Data Across Clusters wizard to deploy your federated database instance. Click the appropriate tab for the corresponding examples.

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:

1db.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:

1db.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:

1db.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:

1db.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:

1db.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.

1db.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.

1db.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.

1db.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.

1db.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.

1db.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.

1db.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}

Click and run the queries in the appropriate tabs for the sample datasets included in your federated database instance.

Before running the queries, switch to the airbnb database:

use airbnb

Find the number of AirBnB offerings with 3 bedrooms and a high review score:

1db.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:

1db.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 analytics database:

use analytics

Find users who have purchased Commodity with a limit of 10000. Limit the number of documents returned to 3:

1db.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:

1db.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:

1db.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 mflix database:

use 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.

1db.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.

1db.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.

1db.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.

1db.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 training database:

use training

Find all trips further than 5 that cost 50 or less.

1db.trips.find({"fare_amount": { "$lte" : "50" }, "trip_distance": {"$gte": "5" }}).count()
196215

Find how many trips included four or more passengers.

1db.trips.find({"passenger_count": { "$gte" : 4 }}).count()
192846

If you have not done so already, load sample data into the Atlas clusters you're using as data sources for your federated database instance before running these queries.

Before running the queries, switch to the VirtualDatabase database:

use VirtualDatabase

Find the number of AirBnB offerings with 3 bedrooms and a high review score:

1db.VirtualCollection.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:

1db.VirtualCollection.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 VirtualDatabase database:

use VirtualDatabase

Find users who have purchased Commodity with a limit of 10000. Limit the number of documents returned to 3:

1db.VirtualCollection.find({"limit": {$eq: 10000}, "products": "Commodity"}).limit(3)
1{
2       "_id" : ObjectId("5ca4bbc7a2dd94ee581623a3"),
3       "account_id" : 775273,
4       "limit" : 10000,
5       "products" : [
6               "Brokerage",
7               "Commodity",
8               "InvestmentStock"
9       ]
10}
11{
12       "_id" : ObjectId("5ca4bbc7a2dd94ee581623a9"),
13       "account_id" : 324287,
14       "limit" : 10000,
15       "products" : [
16               "Commodity",
17               "CurrencyService",
18               "Derivatives",
19               "InvestmentStock"
20       ]
21}
22{
23       "_id" : ObjectId("5ca4bbc7a2dd94ee581623b2"),
24       "account_id" : 209363,
25       "limit" : 10000,
26       "products" : [
27               "Brokerage",
28               "InvestmentStock",
29               "Commodity",
30               "Derivatives"
31       ]
32}

Find customers whose birthdate is before 1990-01-01 and limit the number of documents returned to 3:

1db.VirtualCollection.find({"birthdate": {$lt: ISODate ("1990-01-01T22:15:34.000+00:00")}}, {"name": 1, "birthdate": 1, "email": 1}).limit(3)
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("5ca4bbcea2dd94ee58162a75"),
15       "name" : "John Vega",
16       "birthdate" : ISODate("1983-09-21T03:22:18Z"),
17       "email" : "ryanpena@yahoo.com"
18}

Find transaction details for user whose account ID is 557378 and use the $sort stage to sort on the transactions.symbol field:

1db.VirtualCollection.aggregate([{$match: {"account_id": 557378}}, {$sort: {"transactions.symbol": -1}}])
1{
2       "_id" : ObjectId("5ca4bbc1a2dd94ee58161cb3"),
3       "account_id" : 557378,
4       "transaction_count" : 56,
5       "bucket_start_date" : ISODate("1990-06-11T00:00:00Z"),
6       "bucket_end_date" : ISODate("2016-11-06T00:00:00Z"),
7       "transactions" : [
8          {
9                    "date" : ISODate("2006-10-06T00:00:00Z"),
10                    "amount" : 2561,
11                    "transaction_code" : "sell",
12                    "symbol" : "adbe",
13                    "price" : "38.236619210617988073863671161234378814697265625",
14                    "total" : "97923.98179839266745716486184"
15          },
16          {
17                    "date" : ISODate("2000-06-19T00:00:00Z"),
18                    "amount" : 9153,
19                    "transaction_code" : "sell",
20                    "symbol" : "adbe",
21                    "price" : "31.12236744839008650842515635304152965545654296875",
22                    "total" : "284863.0292551144618116154561"
23          },
24          {
25                    "date" : ISODate("2013-11-06T00:00:00Z"),
26                    "amount" : 18,
27                    "transaction_code" : "buy",
28                    "symbol" : "amzn",
29                    "price" : "356.639066345529272439307533204555511474609375",
30                    "total" : "6419.503194219526903907535598"
31     },
32...

Before running the queries, switch to the VirtualDatabase database:

use VirtualDatabase

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.

1db.VirtualCollection.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 user specified by ID.

1db.VirtualCollection.find({"user_id": "t3qulfeem@kwiv5.6ur"})
1{
2   "_id" : ObjectId("5a97f9c91c807bb9c6eb5fb4"),
3   "user_id" : "t3qulfeem@kwiv5.6ur",
4   "jwt" : "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NieyJpYXQiOjE1MTk5MDkzMjEsIm5iZiI6MTUxOTk           wOTMyMSwianRpIjoiNmJlZDAwMWYtNTFiYi00NzVhLTgAtMDcwNGE5Mjk0MWZlIiwiZXhwIjox           NTE5OTEwMjIxLCJpZGVudGl0eSI6eyJlbWFpbCI6InQzcXVsZmVlbd2l2NS42dXIiLCJuYW1lI           joiM2lveHJtZnF4IiwicGFzc3dvcmQiOm51bGx9LCJmcmVzaCI6ZmFsc2UsInRUiOiJhY2Nlc3           MiLCJ1c2VyX2NsYWltcyI6eyJ1c2VyIjp7ImVtYWlsIjoidDNxdWxmZWVtQGt3aXY1LjZ1cIm5           hbWUiOiIzaW94cm1mcXgiLCJwYXNzd29yZCI6bnVsbH19ejtr_NyZyBronWMKuE0RFTjWej--T           0zGrdc_iymGtVs"
5}

Find the theater specified by its id and zipcode.

1db.VirtualCollection.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.

1db.VirtualCollection.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 VirtualDatabase database:

use VirtualDatabase

Find all states with a total population greater than 10 million.

1db.VirtualCollection.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.

1db.VirtualCollection.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.