(MongoDB)将两个查询管道的输出相交

Question

我正在尝试创建一个MongoDB管道，该管道与以下SQL查询相同：

SELECT DISTINCT A.ID FROM
(Query_A) A 
INNER JOIN 
(Query_B) B
ON A.ID = B.ID

我已经想出了以下代码来运行这两个查询管道：

[
{
"$facet": {
"query_a": [...],
"query_b": [...]
}
},
...
]

这些管道返回IDs，我想得到这些IDs的交集("$query_a.ID"等于"$query_b.ID"(

编辑：

$facet步骤的结果是：

[
{
queryA: [
{"ID": "c80ea2cb-3272-77ae-8f46-d95de600c5bf",
"date": "1"},
{"ID": "cdbcc129-548a-9d51-895a-1538200664e6",
"date": "2"},
{"ID": "a4ece1ba-42ae-e735-17b0-f619daa506f9",
"date": "3"}
],
queryB: [
{"ID": "c80ea2cb-3272-77ae-8f46-d95de600c5bg",
"date": "4"},
{"ID": "cdbcc129-548a-9d51-895a-1538200664e6",
"date": "5"},
{"ID": "a4ece1ba-42ae-e735-17b0-f619daa506f9",
"date": "6"}
]
}
]

但我要求的结果是：

[
{
"dateA": "2",
"dateB": "5",
"intersection": "cdbcc129-548a-9d51-895a-1538200664e6"
},
{
"dateA": "3",
"dateB": "6",
"intersection": "a4ece1ba-42ae-e735-17b0-f619daa506f9"
}
]

Answer 1

一个选项是使用$setIntersection，然后找到匹配的dates:

db.collection.aggregate([
{$project: {
intersection: {$setIntersection: ["$queryA.ID", "$queryB.ID"]},
queryA: 1, queryB: 1}
},
{$project: {
_id: 0,
data: {
$map: {
input: "$intersection",
in: {intersection: "$$this",
queryA: {
$first: {$filter: {
input: "$queryA",
as: "item",
cond: {$eq: ["$$item.ID", "$$this"]}
}}
},
queryB: {
$first: {$filter: {
input: "$queryB",
as: "item",
cond: {$eq: ["$$item.ID", "$$this"]}
}}
}
}
}
}
}
},
{$unwind: "$data"},
{$replaceRoot: {newRoot: "$data"}},
{$project: {intersection: 1, dateA: "$queryA.date", dateB: "$queryB.date"}}
])

看看它是如何在操场上工作的例子

另一种选择是，由于您希望每个相交的ID都有文档，因此使用$unwind，然后使用$group来查找常见的ID:

db.collection.aggregate([
{$project: {
all: {$concatArrays: [
{$map: {
input: "$queryA",
in: {ID: "$$this.ID", dateA: "$$this.date"}
}},
{$map: {
input: "$queryB",
in: {ID: "$$this.ID", dateB: "$$this.date"}
}}
]}
}},
{$unwind: "$all"},
{$group: {
_id: "$all.ID",
dateA: {$push: "$all.dateA"},
dateB: {$push: "$all.dateB"}
}},
{$project: {
dateA: {$first: "$dateA"},
dateB: {$first: "$dateB"},
intersection: "$_id",
_id: 0
}},
{$match: {dateA: {$exists: true}, dateB: {$exists: true}}}
])

看看它是如何在操场上工作的例子