用 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?
推荐答案
计算每个 "Uid" 的 "EndpointId" 内唯一"出现次数的查询"Tags" 中的 code> 和 "Sensors" 中的 "Type" 将是:
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 个数组.对于外部数组，这只是应用于文档中数组的字段路径，而对于内部数组项，您需要使用 $map 以便处理每个 "Tags" 元素，然后获取 $size of "Sensors" 和 $sum生成的数组以减少到总计数.
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# 驱动程序参考中，了解如何映射到一般 MongoDB 的聚合框架的其他示例.
Also see the general LINQ Reference in the C# Driver reference for other examples of how this maps onto the Aggregation Framework of MongoDB in general.
这篇关于用 C# 计算嵌套对象的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
