多个对象数组文档的C#Mongodb笛卡尔积 [英] C# Mongodb cartesian product of multiple object array documents
问题描述
试图用C#/Linq甚至原始的Mongodb查询本身来了解如何将多个数组作为笛卡尔积进行连接.
Trying to understand with either C#/Linq or even raw Mongodb query itself how to join multiple arrays as a cartesian product.
例如，我有一个集合，我将其过滤为以下两个文档:
Say for example I had a collection that I filtered down to the following two documents:
[
{"movie":"starwars","showday":"monday"},
{"movie":"batman","showday":"thursday"},
{"movie":"sleepless","showday":"tuesday"}
]
[
{"actor":"angelina","location":"new york"},
{"actor":"jamie","location":"california"},
{"actor":"mcavoy","location":"arizona"}
]
如何连接每个数组中的每个项目以产生以下类型的结果?
How can I join each item in each array to produce the following type of result?
[{"movie":"starwars","showday":"monday","actor":"angelina","location":"new york"},
{"movie":"batman","showday":"thursday","actor":"angelina","location":"new york"},
{"movie":"sleepless","showday":"tuesday","actor":"angelina","location":"new york"},
{"movie":"starwars","showday":"monday","actor":"jamie","location":"california"},
{"movie":"batman","showday":"thursday","actor":"jamie","location":"california"},
{"movie":"sleepless","showday":"tuesday","actor":"jamie","location":"california"},
{"movie":"starwars","showday":"monday","actor":"mcavoy","location":"arizona"},
{"movie":"batman","showday":"thursday","actor":"mcavoy","location":"arizona"},
{"movie":"sleepless","showday":"tuesday","actor":"mcavoy","location":"arizona"}]
我正在寻找一种可以处理任意数量文档的解决方案.因此，例如，如果在此示例中，有一个第3个文档，它也具有3个对象数组，这些对象数组将在数组中生成27个项目的结果集-或原来的27行.
I am looking for a solution that could work with any number of documents. So for example if in this example there was a 3rd document that also had 3 object arrays that would produce a result set of 27 items in the array - or 27 rows as it were.
希望找到一种使用C#(Linq?)Mongodb Driver来查询和返回数据的解决方案，但是对于Mongodb特定的查询也是开放的，因为我希望可以从那里反转逻辑.谢谢
Hoping for a solution of how to use C# (Linq?) Mongodb Driver to query and return data like this but would be open to even a mongodb specific query as I can hopefully reverse the logic from there. thank you
推荐答案
您可以在下面的聚合管道中尝试.
You can try below aggregation pipeline.
注意3.5.6 +开发版本中提供了 mergeObjects 聚合运算符.将被发布到即将发布的3.6版本中.
Note mergeObjects aggregation operator is available in the 3.5.6 + development release which will be rolled into upcoming 3.6 release.
db.collection.find();
{
"data" : [
[
{
"movie" : "starwars",
"showday" : "monday"
},
{
"movie" : "batman",
"showday" : "thursday"
},
{
"movie" : "sleepless",
"showday" : "tuesday"
}
],
[
{
"actor" : "angelina",
"location" : "new york"
},
{
"actor" : "jamie",
"location" : "california"
},
{
"actor" : "mcavoy",
"location" : "arizona"
}
]
]
}
使用条件表达式进行聚合.
Aggregation using conditional expression.
aggregate({
$project: {
cp: {
$reduce: {
input: "$data",
initialValue: {
$arrayElemAt: ["$data", 0] // Set the initial value to the first element of the arrays.
},
in: {
$let: {
vars: {
currentr: "$$this", // Current processing element
currenta: "$$value" // Current accumulated value
},
in: {
$cond: [{ // Conditional expression to return the accumulated value as initial value for first element
$eq: ["$$currentr", "$$currenta"]
},
"$$currenta",
{ // From second element onwards prepare the cartesian product
$reduce: {
input: {
$map: {
input: "$$currenta",
as: a"a",
in: {
$map: {
input: "$$currentr",
as: r"r",
in: {
$mergeObjects: ["$$a", "$$r"] // Merge accumulated value with the current processing element
}
}
}
}
},
initialValue: [],
in: {
$concatArrays: ["$$value", "$$this"] // Reduce the merged values which will be used as accumulator for next element
}
}
}]
}
}
}
}
}
}
});
聚合(使用$setUnion).
添加此解决方案仅是为了抑制条件表达式以提供更具可读性的管道.
This solution was only added to suppress the conditional expression to give more readable pipeline.
aggregate({
$project: {
cp: {
$reduce: {
input: "$data",
initialValue: {
$arrayElemAt: ["$data", 0] // Set the initial value to the first element of the arrays.
},
in: {
$let: {
vars: {
currentr: "$$this", // Current processing element
currenta: "$$value" // Current accumulated value
},
in:{
$reduce: {
input: {
$map: {
input: "$$currenta",
as: "a",
in: {
$map: {
input: "$$currentr",
as: "r",
in: {
$mergeObjects: ["$$a", "$$r"] // Merge accumulated value with the current processing element
}
}
}
}
},
initialValue: [],
in: {
$setUnion: ["$$value", "$$this"] // Reduce the merged values which will be used as accumulator for next element
}
}
}
}
}
}
}
}
});
更新
上述两种解决方案都不能与数组中的重复值一起使用，正如下面Asya Kamsky的注释所指出的，因为第一种解决方案中的$cond和第二种解决方案中的$setUnion不正确.
Both the above solutions will not work with repeating values across arrays as noted by Asya Kamsky's comments below because of the incorrect $cond in the first solution and $setUnion in second solution.
正确的解决方法是
以[ { } ]的initialValue
或
更改input以排除第一个元素，例如input: {$slice:["$data", 1, {$subtract:[{$size:"$data"},1]}]},
change input to exclude the first element like input: {$slice:["$data", 1, {$subtract:[{$size:"$data"},1]}]},
完整的聚合管道
aggregate({
$project: {
cp: {
$reduce: {
input: {$slice:["$data", 1, {$subtract:[{$size:"$data"},1]}]},
initialValue: {$arrayElemAt:["$data",0]},
in: {
$let: {
vars: {
currentr: "$$this",
currenta: "$$value"
},
in:{
$reduce: {
input: {
$map: {
input: "$$currenta",
as: "a",
in: {
$map: {
input: "$$currentr",
as: "r",
in: {
$mergeObjects: ["$$a", "$$r"]
}
}
}
}
},
initialValue: [],
in: {
$concatArrays: ["$$value", "$$this"]
}
}
}
}
}
}
}
}
});
这篇关于多个对象数组文档的C#Mongodb笛卡尔积的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
