Docs 菜单
Docs 主页
/
MongoDB Atlas
/
查询联合数据
/
开始体验

对联合数据库实例运行查询

在此页面上

  • 先决条件
  • 查询
  • 总结

预计完成时间：5 分钟

您可以使用MongoDB查询语言 (MQL )运行操作，其中包括大多数但不是全部标准服务器命令。 要学习；了解支持哪些MQL操作，请参阅 MQL支持文档。

注意

Atlas Data Federation 示例数据集为只读。

先决条件

要完成本教程的这一部分，您需要先完成：

在运行以下查询之前，必须使用 MongoDB Shell连接到联合数据库实例。

查询

这些示例查询会有所不同，具体取决于您是使用“馈送下游系统”、 “浏览样本数据”还是“跨集群查询数据”向导来部署联合数据库实例。 单击相应的标签页查看相应示例。

如果尚未执行此操作，请完成以下操作以成功运行以下查询：

  • 加载示例数据到用作联合数据库实例数据源的 Atlas 集群。

  • 配置下游系统，将Atlas集群中的数据提取到 Amazon Web Services S3存储桶中。

在运行查询之前，请切换到 sample_airbnb数据库：

use sample_airbnb

查找有3间卧室且评分较高的 Airbnb 房源数量：

1db.listingsAndReviews.aggregate([{$match: {"bedrooms" : 3, "review_scores.review_scores_rating": {$gt: 79}} }, {$count: "numProperties"}])
1{
2   "numProperties" : 295
3}

查找具有3间卧室的属性，并在结果中仅包含name和bedrooms字段。按客户评级对返回的文档进行排序。 将返回的文档数量限制为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]

在运行查询之前，请切换到sample_analytics数据库：

use sample_analytics

查找已购买Commodity （限制为10000的用户。 将返回的文档数量限制为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}

查找出生日期在1990-01-01之前的客户，并将返回的文档数量限制为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}

查找帐户ID为557378的用户的ACID 事务详细信息，并使用 $sort阶段对transactions.symbol字段进行排序：

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}

在运行查询之前，请切换到sample_mflix数据库：

use sample_mflix

查找2010和2015年之间的所有电影，并在结果中仅包含_id 、 title和year字段。 将返回的文档数量限制为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}

查找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}

查找由id和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}

查找姓氏为Lannister的所有用户，并将返回的文档数量限制为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}

在运行查询之前，请切换到sample_training数据库：

use sample_training

查找所有人口超过10百万的州。

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 }

查找客户所有少于70分钟的行程。

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}

单击 并在相应的标签页中为联合数据库实例中包含的示例数据集运行查询。

在运行查询之前，请切换到airbnb数据库：

use airbnb

查找有3间卧室且评分较高的 Airbnb 房源数量：

1db.listingsAndReviews.aggregate([{$match: {"bedrooms" : 3, "review_scores.review_scores_rating": {$gt: 79}} }, {$count: "numProperties"}])
1{
2   "numProperties" : 295
3}

查找具有3间卧室的属性，并在结果中仅包含name和bedrooms字段。按客户评级对返回的文档进行排序。 将返回的文档数量限制为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]

在运行查询之前，请切换到analytics数据库：

use analytics

查找已购买Commodity （限制为10000的用户。 将返回的文档数量限制为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}

查找出生日期在1990-01-01之前的客户，并将返回的文档数量限制为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}

查找帐户ID为557378的用户的ACID 事务详细信息，并使用$sort阶段对transactions.symbol字段进行排序：

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}

在运行查询之前，请切换到mflix数据库：

use mflix

查找2010和2015年之间的所有电影，并在结果中仅包含_id 、 title和year字段。 将返回的文档数量限制为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}

查找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}

查找由id和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}

查找姓氏为Lannister的所有用户，并将返回的文档数量限制为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}

在运行查询之前，请切换到training数据库：

use training

查找距离超过5 、费用50或以下的所有行程。

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

查找有四名或四名以上乘客的行程。

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

如果您尚未执行此操作，请在运行这些查询之前将示例数据加载到用作联合数据库实例数据源的Atlas集群。

在运行查询之前，请切换到VirtualDatabase数据库：

use VirtualDatabase

查找有3间卧室且评分较高的 Airbnb 房源数量：

1db.VirtualCollection.aggregate([{$match: {"bedrooms" : 3, "review_scores.review_scores_rating": {$gt: 79}}}, {$count: "numProperties"}])
1{
2   "numProperties" : 295
3}

查找具有3间卧室的属性，并在结果中仅包含name和bedrooms字段。按客户评级对返回的文档进行排序。 将返回的文档数量限制为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]

在运行查询之前，请切换到VirtualDatabase数据库：

use VirtualDatabase

查找已购买Commodity （限制为10000的用户。 将返回的文档数量限制为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}

查找出生日期在1990-01-01之前的客户，并将返回的文档数量限制为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}

查找帐户ID为557378的用户的ACID 事务详细信息，并使用$sort阶段对transactions.symbol字段进行排序：

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

在运行查询之前，请切换到VirtualDatabase数据库：

use VirtualDatabase

查找2010和2015年之间的所有电影，并在结果中仅包含_id 、 title和year字段。 将返回的文档数量限制为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}

查找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}

查找由id和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}

查找姓氏为Lannister的所有用户，并将返回的文档数量限制为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}

在运行查询之前，请切换到VirtualDatabase数据库：

use VirtualDatabase

查找总人口超过10百万的所有州。

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 }

查找客户所有少于70分钟的行程。

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}

总结

恭喜！您刚刚建立了一个联合数据库实例， 利用 S3 存储桶的数据创建了数据库和集合， 并使用 MQL 命令查询了数据。

有关联合数据库实例的更多信息，请参阅 Atlas Data Federation。

注意

当您从文件名动态生成集合时， Data Federation视图不会准确报告集合的数量。