/ /

运行 Feed Downstream Systems 向导的查询

预计完成时间：5 分钟

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

注意

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

先决条件

要完成本教程的这一部分，您必须完成：

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

查询

以下示例查询适用于使用 Feed Downstream Systems 导向部署的联合数据库实例。

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

加载示例数据到用作联合数据库实例数据源的 Atlas 集群。
配置下游系统，将数据从 Atlas 集群提取到 AWS S3 存储桶。

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

use sample_airbnb

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

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

1 {
2    "numProperties" : 295
3 }

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

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

use sample_analytics

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

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

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

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 }

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

use sample_mflix

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

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

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

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

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

use sample_training

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

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 }

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

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 }

总结

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

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

注意

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

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	]

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	}

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	}

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	}

1	{
2	"_id" : ObjectId("5a97f9c91c807bb9c6eb5fb4"),
3	"user_id" : "t3qulfeem@kwiv5.6ur",
4	"jwt" : "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NieyJpYXQiOjE1MTk5MDkzMjEsIm5iZiI6MTUxOTkwOTMyMSwianRpIjoiNmJlZDAwMWYtNTFiYi00NzVhLTgAtMDcwNGE5Mjk0MWZlIiwiZXhwIjoxNTE5OTEwMjIxLCJpZGVudGl0eSI6eyJlbWFpbCI6InQzcXVsZmVlbd2l2NS42dXIiLCJuYW1lIjoiM2lveHJtZnF4IiwicGFzc3dvcmQiOm51bGx9LCJmcmVzaCI6ZmFsc2UsInRUiOiJhY2Nlc3MiLCJ1c2VyX2NsYWltcyI6eyJ1c2VyIjp7ImVtYWlsIjoidDNxdWxmZWVtQGt3aXY1LjZ1cIm5hbWUiOiIzaW94cm1mcXgiLCJwYXNzd29yZCI6bnVsbH19ejtr_NyZyBronWMKuE0RFTjWej--T0zGrdc_iymGtVs"
5	}

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	}

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	}

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 }

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	}