用C#计算嵌套对象的数量 [英] Calculate the count of nested objects with C#
问题描述
我正在使用ASP.Net Core 2.0 Web API开发软件.我需要计算集合中某些字段的数量. 我在MongoDB的集合中有如下数据.我需要找到我的收藏夹中有多少个标签和多少个传感器.一个特定的端点具有多个标签，每个标签具有多个传感器.
I am developing a software using ASP.Net core 2.0 web api. I need to calculate count of some fields in my collection. I have data in my collection in MongoDB as below. I need to find how many Tags and how many sensors do I have in my collection. A specific endpoint has multi tags and each tag has multi sensors.
{
"_id" : ObjectId("5aef51dfaf42ea1b70d0c4db"),
"EndpointId" : "89799bcc-e86f-4c8a-b340-8b5ed53caf83",
"DateTime" : ISODate("2018-05-06T19:05:02.666Z"),
"Url" : "test",
"Tags" : [
{
"Uid" : "C1:3D:CA:D4:45:11",
"Type" : 1,
"DateTime" : ISODate("2018-05-06T19:05:02.666Z"),
"Sensors" : [
{
"Type" : 1,
"Value" : NumberDecimal("-95")
},
{
"Type" : 2,
"Value" : NumberDecimal("-59")
},
{
"Type" : 3,
"Value" : NumberDecimal("11.029802536740132")
},
{
"Type" : 4,
"Value" : NumberDecimal("27.25")
},
{
"Type" : 6,
"Value" : NumberDecimal("2924")
}
]
},
{
"Uid" : "C1:3D:CA:D4:45:11",
"Type" : 1,
"DateTime" : ISODate("2018-05-06T19:05:02.666Z"),
"Sensors" : [
{
"Type" : 1,
"Value" : NumberDecimal("-95")
},
{
"Type" : 2,
"Value" : NumberDecimal("-59")
},
{
"Type" : 3,
"Value" : NumberDecimal("11.413037961112279")
},
{
"Type" : 4,
"Value" : NumberDecimal("27.25")
},
{
"Type" : 6,
"Value" : NumberDecimal("2924")
}
]
},
{
"Uid" : "E5:FA:2A:35:AF:DD",
"Type" : 1,
"DateTime" : ISODate("2018-05-06T19:05:02.666Z"),
"Sensors" : [
{
"Type" : 1,
"Value" : NumberDecimal("-97")
},
{
"Type" : 2,
"Value" : NumberDecimal("-58")
},
{
"Type" : 3,
"Value" : NumberDecimal("10.171658037099185")
}
]
}
]
}
/* 2 */
{
"_id" : ObjectId("5aef51e0af42ea1b70d0c4dc"),
"EndpointId" : "89799bcc-e86f-4c8a-b340-8b5ed53caf83",
"Url" : "test",
"Tags" : [
{
"Uid" : "E2:02:00:18:DA:40",
"Type" : 1,
"DateTime" : ISODate("2018-05-06T19:05:04.574Z"),
"Sensors" : [
{
"Type" : 1,
"Value" : NumberDecimal("-98")
},
{
"Type" : 2,
"Value" : NumberDecimal("-65")
},
{
"Type" : 3,
"Value" : NumberDecimal("7.845424441900629")
},
{
"Type" : 4,
"Value" : NumberDecimal("0.0")
},
{
"Type" : 6,
"Value" : NumberDecimal("3012")
}
]
},
{
"Uid" : "12:3B:6A:1A:B7:F9",
"Type" : 1,
"DateTime" : ISODate("2018-05-06T19:05:04.574Z"),
"Sensors" : [
{
"Type" : 1,
"Value" : NumberDecimal("-95")
},
{
"Type" : 2,
"Value" : NumberDecimal("-59")
},
{
"Type" : 3,
"Value" : NumberDecimal("12.939770381907275")
}
]
}
]
}
我想计算与特定EndpointId相关的标签和传感器的数量.如何在mongoDB中编写该查询?
I want to calculate the count of Tags and Sensors related to specific EndpointId. How can I write that query in mongoDB?
推荐答案
该查询用于对"Tags"中的"Uid"和"EndpointId"中的唯一"出现次数进行计数>将会是:
The query to count the "unique" occurances within an "EndpointId" of each of the "Uid" in "Tags" and the "Type" in "Sensors" would be:
db.collection.aggregate([
{ "$unwind": "$Tags" },
{ "$unwind": "$Tags.Sensors" },
{ "$group": {
"_id": {
"EndpointId": "$EndpointId",
"Uid": "$Tags.Uid",
"Type": "$Tags.Sensors.Type"
},
}},
{ "$group": {
"_id": {
"EndpointId": "$_id.EndpointId",
"Uid": "$_id.Uid",
},
"count": { "$sum": 1 }
}},
{ "$group": {
"_id": "$_id.EndpointId",
"tagCount": { "$sum": 1 },
"sensorCount": { "$sum": "$count" }
}}
])
或者对于C#
var results = collection.AsQueryable()
.SelectMany(p => p.Tags, (p, tag) => new
{
EndpointId = p.EndpointId,
Uid = tag.Uid,
Sensors = tag.Sensors
}
)
.SelectMany(p => p.Sensors, (p, sensor) => new
{
EndpointId = p.EndpointId,
Uid = p.Uid,
Type = sensor.Type
}
)
.GroupBy(p => new { EndpointId = p.EndpointId, Uid = p.Uid, Type = p.Type })
.GroupBy(p => new { EndpointId = p.Key.EndpointId, Uid = p.Key.Uid },
(k, s) => new { Key = k, count = s.Count() }
)
.GroupBy(p => p.Key.EndpointId,
(k, s) => new
{
EndpointId = k,
tagCount = s.Count(),
sensorCount = s.Sum(x => x.count)
}
);
哪个输出:
{
"EndpointId" : "89799bcc-e86f-4c8a-b340-8b5ed53caf83",
"tagCount" : 4,
"sensorCount" : 16
}
实际上，考虑到所呈现的文档具有"Uid"的唯一值，这样做实际上是最有效"的方式，而对于$reduce文档本身中的金额而言:
Though actually the "most efficient" way to do this considering that the documents presented have unique values for "Uid" anyway would be to $reduce the amounts within the documents itself:
db.collection.aggregate([
{ "$group": {
"_id": "$EndpointId",
"tags": {
"$sum": {
"$size": { "$setUnion": ["$Tags.Uid",[]] }
}
},
"sensors": {
"$sum": {
"$sum": {
"$map": {
"input": { "$setUnion": ["$Tags.Uid",[]] },
"as": "tag",
"in": {
"$size": {
"$reduce": {
"input": {
"$filter": {
"input": {
"$map": {
"input": "$Tags",
"in": {
"Uid": "$$this.Uid",
"Type": "$$this.Sensors.Type"
}
}
},
"cond": { "$eq": [ "$$this.Uid", "$$tag" ] }
}
},
"initialValue": [],
"in": { "$setUnion": [ "$$value", "$$this.Type" ] }
}
}
}
}
}
}
}
}}
])
该语句实际上并不能很好地映射到LINQ，因此您将需要使用BsonDocument接口来为该语句构建BSON.当然，如果集合中的多个文档中实际上出现了相同的"Uid"值"did"，则必须使用$unwind语句才能将它们从数组条目中跨文档分组"在一起.
The statement does not really map well to LINQ however, so you would be required to use the BsonDocument interface to build the BSON for the statement. And of course where the same "Uid" values "did" in fact occur within multiple documents in the collection, then the $unwind statements are necessary in order to "group" those together across documents from within the array entries.
您可以通过获取以下内容的 $size 来解决此问题数组.对于外部数组，这仅适用于文档中数组的字段路径，对于内部数组项，您需要使用元素，然后获取"Sensors"和
You solve this by obtaining the $size of the arrays. For the outer array this is simply applying to field path of the array in the document, and for the inner array items you need to process with $map in order to process each "Tags" element and then obtain the $size of "Sensors" and $sum the resulting array to reduce to the overall count.
每个文档将是:
db.collection.aggregate([
{ "$project": {
"tags": { "$size": "$Tags" },
"sensors": {
"$sum": {
"$map": {
"input": "$Tags",
"in": { "$size": "$$this.Sensors" }
}
}
}
}}
])
您在C#代码中分配给类的位置将是这样:
Which where you have assigned to classes in your C# code would be like:
collection.AsQueryable()
.Select(p => new
{
tags = p.Tags.Count(),
sensors = p.Tags.Select(x => x.Sensors.Count()).Sum()
}
);
那些返回的地方:
{ "tags" : 3, "sensors" : 13 }
{ "tags" : 2, "sensors" : 8 }
您要 $group 的结果，例如整个收藏集，那么您将:
Where you want to $group the results, as for example over the whole collection, then you would do:
db.collection.aggregate([
/* The shell would use $match for "query" conditions */
//{ "$match": { "EndpointId": "89799bcc-e86f-4c8a-b340-8b5ed53caf83" } },
{ "$group": {
"_id": null,
"tags": { "$sum": { "$size": "$Tags" } },
"sensors": {
"$sum": {
"$sum": {
"$map": {
"input": "$Tags",
"in": { "$size": "$$this.Sensors" }
}
}
}
}
}}
])
您的C#代码与以前一样是
Which for your C# code like before would be:
collection.AsQueryable()
.GroupBy(p => "", (k,s) => new
{
tags = s.Sum(p => p.Tags.Count()),
sensors = s.Sum(p => p.Tags.Select(x => x.Sensors.Count()).Sum())
}
);
那些返回的地方:
{ "tags" : 5, "sensors" : 21 }
对于"EndpointId，那么您只需使用该字段作为分组关键字，而不是null或0，因为C#驱动程序映射会应用该字段:
And for "EndpointId, then you simply use that field as the grouping key, rather than the null or 0 as it gets applied by the C# driver mapping:
collection.AsQueryable()
/* Use the Where if you want a query to match only those documents */
//.Where(p => p.EndpointId == "89799bcc-e86f-4c8a-b340-8b5ed53caf83")
.GroupBy(p => p.EndpointId, (k,s) => new
{
tags = s.Sum(p => p.Tags.Count()),
sensors = s.Sum(p => p.Tags.Select(x => x.Sensors.Count()).Sum())
}
);
您给我们的两个文档样本中的总和当然是相同的:
Which is of course the same sum of the two document sample you gave us:
{ "tags" : 5, "sensors" : 21 }
这些都是非常简单的结果，一旦您习惯了语法，便可以执行简单的管道.
So these are very simple results, with simple pipeline execution once you get used to the syntax.
我建议您熟悉核心文档中的聚合运算符 ，当然还有"LINQ备忘单" 和C#驱动程序代码存储库中的表达式及其用法映射.
I suggest familiarizing yourself with the Aggregation Operators from the core documentation, and of course the "LINQ Cheat Sheet" of expressions and their usage mapping from withing the C# Driver code repository.
另请参见常规 LINQ参考.
这篇关于用C#计算嵌套对象的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
