如何通过计数MongoDB设置数组对象组中的限制 [英] How to set limitation in array object group by count MongoDB
本文介绍了如何通过计数MongoDB设置数组对象组中的限制的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想获取最多的城市数据，仅显示示例雅加达和勿加泗的城市，因为计算的城市总数最多.并可以使用多少动态限制.
i want to get retrieve the most data of city, example only city of jakarta and bekasi will be show, because the most data total of count city. and can use how many dynamically limitation.
链接示例: MongoPlayground
输入
[
{
"_id": ObjectId("5dcbb949e7599115cf1b9441"),
"date": "2019-11-13T15:05:28.379Z",
"id": NumberInt(166),
"location": {
"city": "Bekasi",
"country": "Indonesia",
"district": "Jatimulya",
"latitude": -6.266871666666666,
"longitude": 107.01972499999998,
"province": "Jawa Barat"
},
"update_at": "2019-11-13T15:05:28.391Z"
},
{
"_id": ObjectId("5dcbba2ee7599115cf1ba50b"),
"date": "2019-11-13T15:09:17.001Z",
"id": NumberInt(166),
"location": {
"city": "Bekasi",
"country": "Indonesia",
"district": "Jatimulya",
"latitude": -6.266871666666666,
"longitude": 107.01972499999998,
"province": "Jawa Barat"
},
"update_at": "2019-11-13T15:09:17.009Z"
},
{
"_id": ObjectId("5dcbbb14e7599115cf1bb9ff"),
"date": "2019-11-13T15:13:06.010Z",
"id": NumberInt(166),
"location": {
"city": "Bekasi",
"country": "Indonesia",
"district": "Jatimulya",
"latitude": -6.266871666666666,
"longitude": 107.01972499999998,
"province": "Jawa Barat"
},
"update_at": "2019-11-13T15:13:06.019Z"
},
{
"_id": ObjectId("5dcf596e8c3dfe4d6e865acf"),
"date": "2019-11-16T09:05:32.160Z",
"id": NumberInt(166),
"location": {
"city": "Bekasi",
"country": "Indonesia",
"district": "Gandasari",
"latitude": -6.27103,
"longitude": 107.09003333333334,
"province": "Jawa Barat"
},
"update_at": "2019-11-16T09:05:32.175Z",
},
{
"_id": ObjectId("5dcf5a538c3dfe4d6e866a78"),
"date": "2019-11-16T09:09:21.265Z",
"id": NumberInt(166),
"location": {
"city": "Bekasi",
"country": "Indonesia",
"district": "Gandasari",
"latitude": -6.27103,
"longitude": 107.09003333333334,
"province": "Jawa Barat"
},
"update_at": "2019-11-16T09:09:21.273Z"
},
{
"_id": ObjectId("5dde3eab4144014acda0563b"),
"date": "2019-11-27T16:15:21.740Z",
"id": NumberInt(166),
"location": {
"city": "Bogor",
"country": "Indonesia",
"district": "Cibinong",
"latitude": -6.498371666666666,
"longitude": 106.85079166666668,
"province": "Jawa Barat"
},
"update_at": "2019-11-27T16:15:21.753Z"
},
{
"_id": ObjectId("5dde3fa14144014acda083ac"),
"date": "2019-11-27T16:19:27.102Z",
"id": NumberInt(166),
"location": {
"city": "Bogor",
"country": "Indonesia",
"district": "Cibinong",
"latitude": -6.498371666666666,
"longitude": 106.85079166666668,
"province": "Jawa Barat"
},
"update_at": "2019-11-27T16:19:27.114Z"
},
{
"_id": ObjectId("5dde40984144014acda09bb3"),
"date": "2019-11-27T16:23:32.742Z",
"id": NumberInt(166),
"location": {
"city": "Bogor",
"country": "Indonesia",
"district": "Cibinong",
"latitude": -6.498371666666666,
"longitude": 106.85079166666668,
"province": "Jawa Barat"
},
"update_at": "2019-11-27T16:23:32.753Z"
},
{
"_id": ObjectId("5dde41a64144014acda0bd03"),
"date": "2019-11-27T16:28:04.557Z",
"id": NumberInt(166),
"location": {
"city": "Bogor",
"country": "Indonesia",
"district": "Cibinong",
"latitude": -6.498371666666666,
"longitude": 106.85079166666668,
"province": "Jawa Barat"
},
"update_at": "2019-11-27T16:28:04.569Z"
},
{
"_id": ObjectId("5dde429b4144014acda0d6a9"),
"date": "2019-11-27T16:32:09.888Z",
"id": NumberInt(166),
"location": {
"city": "Bekasi",
"country": "Indonesia",
"district": "Jatimulya",
"latitude": -6.266871666666666,
"longitude": 107.01972499999998,
"province": "Jawa Barat"
},
"update_at": "2019-11-27T16:32:09.909Z"
},
{
"_id": ObjectId("5ccbb96706d1d47a4b2ced4b"),
"date": "2019-11-13T15:05:28.379Z",
"id": NumberInt(166),
"location": {
"city": "Jakarta",
"country": "Indonesia",
"district": "Jatimulya",
"latitude": -6.266871666666666,
"longitude": 107.01972499999998,
"province": "Jawa Barat"
},
"update_at": "2019-11-13T15:05:28.391Z"
},
{
"_id": ObjectId("5ccbb96706d1d47a4b2ced4c"),
"date": "2019-11-13T15:09:17.001Z",
"id": NumberInt(166),
"location": {
"city": "Jakarta",
"country": "Indonesia",
"district": "Jatimulya",
"latitude": -6.266871666666666,
"longitude": 107.01972499999998,
"province": "Jawa Barat"
},
"update_at": "2019-11-13T15:09:17.009Z"
},
{
"_id": ObjectId("5ccbb96706d1d47a4b2ced4d"),
"date": "2019-11-13T15:13:06.010Z",
"id": NumberInt(166),
"location": {
"city": "Jakarta",
"country": "Indonesia",
"district": "Jatimulya",
"latitude": -6.266871666666666,
"longitude": 107.01972499999998,
"province": "Jawa Barat"
},
"update_at": "2019-11-13T15:13:06.019Z"
},
{
"_id": ObjectId("5ccbb96706d1d47a4b2ced4e"),
"date": "2019-11-16T09:05:32.160Z",
"id": NumberInt(166),
"location": {
"city": "Jakarta",
"country": "Indonesia",
"district": "Gandasari",
"latitude": -6.27103,
"longitude": 107.09003333333334,
"province": "Jawa Barat"
},
"update_at": "2019-11-16T09:05:32.175Z",
},
{
"_id": ObjectId("5ccbb96706d1d47a4b2ced4f"),
"date": "2019-11-16T09:09:21.265Z",
"id": NumberInt(166),
"location": {
"city": "Jakarta",
"country": "Indonesia",
"district": "Gandasari",
"latitude": -6.27103,
"longitude": 107.09003333333334,
"province": "Jawa Barat"
},
"update_at": "2019-11-16T09:09:21.273Z"
},
{
"_id": ObjectId("5ccbb96706d1d47a4b2ced50"),
"date": "2019-11-27T16:15:21.740Z",
"id": NumberInt(166),
"location": {
"city": "Jakarta",
"country": "Indonesia",
"district": "Cibinong",
"latitude": -6.498371666666666,
"longitude": 106.85079166666668,
"province": "Jawa Barat"
},
"update_at": "2019-11-27T16:15:21.753Z"
},
{
"_id": ObjectId("5ccbb96706d1d47a4b2ced51"),
"date": "2019-11-27T16:19:27.102Z",
"id": NumberInt(166),
"location": {
"city": "Jakarta",
"country": "Indonesia",
"district": "Cibinong",
"latitude": -6.498371666666666,
"longitude": 106.85079166666668,
"province": "Jawa Barat"
},
"update_at": "2019-11-27T16:19:27.114Z"
},
{
"_id": ObjectId("5ccbb96706d1d47a4b2ced52"),
"date": "2019-11-27T16:23:32.742Z",
"id": NumberInt(166),
"location": {
"city": "Jakarta",
"country": "Indonesia",
"district": "Cibinong",
"latitude": -6.498371666666666,
"longitude": 106.85079166666668,
"province": "Jawa Barat"
},
"update_at": "2019-11-27T16:23:32.753Z"
},
{
"_id": ObjectId("5ccbb96706d1d47a4b2ced53"),
"date": "2019-11-27T16:28:04.557Z",
"id": NumberInt(166),
"location": {
"city": "Jakarta",
"country": "Indonesia",
"district": "Cibinong",
"latitude": -6.498371666666666,
"longitude": 106.85079166666668,
"province": "Jawa Barat"
},
"update_at": "2019-11-27T16:28:04.569Z"
},
{
"_id": ObjectId("5ccbb96706d1d47a4b2ced54"),
"date": "2019-11-27T16:32:09.888Z",
"id": NumberInt(166),
"location": {
"city": "Jakarta",
"country": "Indonesia",
"district": "Jatimulya",
"latitude": -6.266871666666666,
"longitude": 107.01972499999998,
"province": "Jawa Barat"
},
"update_at": "2019-11-27T16:32:09.909Z"
}
]
实施
db.collection.aggregate([
{
"$match": {
"update_at": {
"$gte": "2019-11-02T00:00:00.0Z",
"$lt": "2019-12-06T00:00:00.0Z"
},
"id": {
"$in": [
166
]
}
}
},
{
"$group": {
"_id": {
"date": {
$substr: [
"$update_at",
0,
10
]
},
"city": {
$cond: [
"$location",
"$location.city",
""
]
}
},
"cityCount": {
"$sum": {
$cond: [
"$location",
1,
0
]
}
}
}
},
{
"$group": {
"_id": "$_id.date",
"cities": {
"$push": {
"k": "$_id.city",
"v": "$cityCount"
},
},
"count": {
"$sum": "$cityCount"
}
}
},
{
"$project": {
_id: 0,
date: "$_id",
value: "$count",
city: {
$arrayToObject: "$cities"
}
}
},
{
$facet: {
data: [
{
$match: {}
}
],
distinct: [
{
$addFields: {
city: {
$objectToArray: "$city"
}
}
},
{
$unwind: "$city"
},
{
$group: {
_id: null,
city: {
$addToSet: {
"k": "$city.k",
v: 0
}
}
}
},
{
$addFields: {
city: {
$arrayToObject: "$city"
}
}
}
]
}
},
{
$unwind: "$data"
},
{
$project: {
date: "$data.date",
city: {
$mergeObjects: [
{
$let: {
vars: {
empty_city: {
$arrayElemAt: [
"$distinct",
0
]
}
},
in: "$$empty_city.city"
}
},
"$data.city"
]
}
}
},
{
"$sort": {
"date": 1
}
}
])
实际输出:
{
"city": {
"Bekasi": 3,
"Bogor": 0,
"Jakarta": 3
},
"date": "2019-11-13"
},
{
"city": {
"Bekasi": 2,
"Bogor": 0,
"Jakarta": 2
},
"date": "2019-11-16"
},
{
"city": {
"Bekasi": 1,
"Bogor": 4,
"Jakarta": 5
},
"date": "2019-11-27"
}
我的期望代码显示为获取最多的城市数据，仅显示雅加达和勿加泗市，因为数据总数最多计数城市.并可以使用多少动态限制.
My expectation code show to get retrieve the most data of city, only city of jakarta and bekasi will be show, because the most total of data count count city. and can use how many dynamically limitation.:
预期产量
{
"city": {
"Bekasi": 3,
"Jakarta": 3
},
"date": "2019-11-13"
},
{
"city": {
"Bekasi": 2,
"Jakarta": 2
},
"date": "2019-11-16"
},
{
"city": {
"Bekasi": 1,
"Jakarta": 5
},
"date": "2019-11-27"
}
推荐答案
实际上，我已经查看了您的代码，添加$$empty_city.city时出错，因此请删除一个$符号($empty_city.city)并运行下面的代码.
Actually I have reviewed your code there is a mistake on adding $$empty_city.city so kindly remove one $ sign($empty_city.city) and run below code.
db.collection.aggregate([
{
"$match": {
"update_at": {
"$gte": "2019-11-02T00:00:00.0Z",
"$lt": "2019-12-06T00:00:00.0Z"
},
"id": {
"$in": [
166
]
}
}
},
{
"$group": {
"_id": {
"date": {
$substr: [
"$update_at",
0,
10
]
},
"city": {
$cond: [
"$location",
"$location.city",
""
]
}
},
"cityCount": {
"$sum": {
$cond: [
"$location",
1,
0
]
}
}
}
},
{
"$group": {
"_id": "$_id.date",
"cities": {
"$push": {
"k": "$_id.city",
"v": "$cityCount"
},
},
"count": {
"$sum": "$cityCount"
}
}
},
{
"$project": {
_id: 0,
date: "$_id",
value: "$count",
city: {
$arrayToObject: "$cities"
}
}
},
{
$facet: {
data: [
{
$match: {}
}
],
distinct: [
{
$addFields: {
city: {
$objectToArray: "$city"
}
}
},
{
$unwind: "$city"
},
{
$group: {
_id: null,
city: {
$addToSet: {
"k": "$city.k",
v: 0
}
}
}
},
{
$addFields: {
city: {
$arrayToObject: "$city"
}
}
}
]
}
},
{
$unwind: "$data"
},
{
$project: {
date: "$data.date",
city: {
$mergeObjects: [
{
$let: {
vars: {
empty_city: {
$arrayElemAt: [
"$distinct",
0
]
}
},
in: "$empty_city.city"
}
},
"$data.city"
]
}
}
},
{
"$sort": {
"date": 1
}
}
])
这样您将获得实际输出如下
[
{
"city": {
"Bekasi": 3,
"Jakarta": 3
},
"date": "2019-11-13"
},
{
"city": {
"Bekasi": 2,
"Jakarta": 2
},
"date": "2019-11-16"
},
{
"city": {
"Bekasi": 1,
"Bogor": 4,
"Jakarta": 5
},
"date": "2019-11-27"
}
]
这篇关于如何通过计数MongoDB设置数组对象组中的限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
