MongoDB聚合：$unionWith

$unionWith聚合阶段执行两个集合的合并，将两个集合的管道结果合并到一个结果集传送到下一个阶段。合并后的结果文档的顺序是不确定的。

语法

{ $unionWith: { coll: "<collection>", pipeline: [ <stage1>, ... ] } }

要包含集合的所有文档不进行任何处理，可以使用简化形式：

{ $unionWith: "<collection>" }  // 包含指定集合的所有文档

使用

参数字段：

字段	描述
coll	希望在结果集中包含的集合或视图管道的结果
pipeline	可选，应用于coll的聚合管道[<stage1>, <stage2>, ....]，聚合管道不能包含$out和$merge阶段。从v6.0开始，管道可以在第一个阶段包含$search阶段

$unionWith操作等价于下面的SQL语句：

SELECT *
FROM Collection1
WHERE ...
UNION ALL
SELECT *
FROM Collection2
WHERE ...

重复的结果

前一阶段的合并结果和$unionWith阶段的合并结果可能包含重复结果。例如，创建一个suppliers 集合和一个warehouses 集合：

db.suppliers.insertMany([{ _id: 1, supplier: "Aardvark and Sons", state: "Texas" },{ _id: 2, supplier: "Bears Run Amok.", state: "Colorado"},{ _id: 3, supplier: "Squid Mark Inc. ", state: "Rhode Island" },
])

db.warehouses.insertMany([{ _id: 1, warehouse: "A", region: "West", state: "California" },{ _id: 2, warehouse: "B", region: "Central", state: "Colorado"},{ _id: 3, warehouse: "C", region: "East", state: "Florida" },
])

下面的聚合合并了聚合suppliers和warehouse的state’字段投影结果。

db.suppliers.aggregate([{ $project: { state: 1, _id: 0 } },{ $unionWith: { coll: "warehouses", pipeline: [ { $project: { state: 1, _id: 0 } } ]} }
])

结果包含重复的文档

{ "state" : "Texas" }
{ "state" : "Colorado" }
{ "state" : "Rhode Island" }
{ "state" : "California" }
{ "state" : "Colorado" }
{ "state" : "Florida" }

要要去除重复，可以加个$group阶段，按照state字段分组：

db.suppliers.aggregate([{ $project: { state: 1, _id: 0 } },{ $unionWith: { coll: "warehouses", pipeline: [ { $project: { state: 1, _id: 0 } } ]} },{ $group: { _id: "$state" } }
])

这样结果就没有重复的文档了：

 { "_id" : "California" }{ "_id" : "Texas" }{ "_id" : "Florida" }{ "_id" : "Colorado" }{ "_id" : "Rhode Island" }

$unionWith 分片集合

如果$unionWith阶段是$lookup管道的一部分，则$unionWith的coll被分片。例如，在下面的聚合操作中，不能对inventory_q1集合进行分片：

db.suppliers.aggregate([{$lookup: {from: "warehouses",let: { order_item: "$item", order_qty: "$ordered" },pipeline: [...{ $unionWith: { coll: "inventory_q1", pipeline: [ ... ] } },...],as: "stockdata"}}
])

限制

• 聚合管道不能在事务中使用$unionWith
• 如果$unionWith阶段是$lookup管道的一部分，则$unionWith的集合不能被分片
• unionWith管道不能包含out阶段
• unioWith管道不能包含merge阶段

举例

用多年的数据集合创建销售报告

下面示例使用$unionWith阶段来合并数据并返回多个集合的结果，在这些示例中，每个集合都包含一年的销售数据。

填充样本数据：

分别创建sales_2017、sales_2018、sales_2019、sales_2019四个集合并填充数据：

db.sales_2017.insertMany( [{ store: "General Store", item: "Chocolates", quantity: 150 },{ store: "ShopMart", item: "Chocolates", quantity: 50 },{ store: "General Store", item: "Cookies", quantity: 100 },{ store: "ShopMart", item: "Cookies", quantity: 120 },{ store: "General Store", item: "Pie", quantity: 10 },{ store: "ShopMart", item: "Pie", quantity: 5 }
] )

db.sales_2018.insertMany( [{ store: "General Store", item: "Cheese", quantity: 30 },{ store: "ShopMart", item: "Cheese", quantity: 50 },{ store: "General Store", item: "Chocolates", quantity: 125 },{ store: "ShopMart", item: "Chocolates", quantity: 150 },{ store: "General Store", item: "Cookies", quantity: 200 },{ store: "ShopMart", item: "Cookies", quantity: 100 },{ store: "ShopMart", item: "Nuts", quantity: 100 },{ store: "General Store", item: "Pie", quantity: 30 },{ store: "ShopMart", item: "Pie", quantity: 25 }
] )

db.sales_2019.insertMany( [{ store: "General Store", item: "Cheese", quantity: 50 },{ store: "ShopMart", item: "Cheese", quantity: 20 },{ store: "General Store", item: "Chocolates", quantity: 125 },{ store: "ShopMart", item: "Chocolates", quantity: 150 },{ store: "General Store", item: "Cookies", quantity: 200 },{ store: "ShopMart", item: "Cookies", quantity: 100 },{ store: "General Store", item: "Nuts", quantity: 80 },{ store: "ShopMart", item: "Nuts", quantity: 30 },{ store: "General Store", item: "Pie", quantity: 50 },{ store: "ShopMart", item: "Pie", quantity: 75 }
] )

db.sales_2020.insertMany( [{ store: "General Store", item: "Cheese", quantity: 100, },{ store: "ShopMart", item: "Cheese", quantity: 100},{ store: "General Store", item: "Chocolates", quantity: 200 },{ store: "ShopMart", item: "Chocolates", quantity: 300 },{ store: "General Store", item: "Cookies", quantity: 500 },{ store: "ShopMart", item: "Cookies", quantity: 400 },{ store: "General Store", item: "Nuts", quantity: 100 },{ store: "ShopMart", item: "Nuts", quantity: 200 },{ store: "General Store", item: "Pie", quantity: 100 },{ store: "ShopMart", item: "Pie", quantity: 100 }
] )

按照year、store和item的销售额

db.sales_2017.aggregate( [{ $set: { _id: "2017" } },{ $unionWith: { coll: "sales_2018", pipeline: [ { $set: { _id: "2018" } } ] } },{ $unionWith: { coll: "sales_2019", pipeline: [ { $set: { _id: "2019" } } ] } },{ $unionWith: { coll: "sales_2020", pipeline: [ { $set: { _id: "2020" } } ] } },{ $sort: { _id: 1, store: 1, item: 1 } }
] )

• $set阶段用于更新_id字段，使其包含年份
• $unionWith阶段，将四个集合中的所有文档合并在一起，每个文档也使用$set阶段
• $sort阶段，按照_id（年份）、store和item进行排序

管道输出：

{ "_id" : "2017", "store" : "General Store", "item" : "Chocolates", "quantity" : 150 }
{ "_id" : "2017", "store" : "General Store", "item" : "Cookies", "quantity" : 100 }
{ "_id" : "2017", "store" : "General Store", "item" : "Pie", "quantity" : 10 }
{ "_id" : "2017", "store" : "ShopMart", "item" : "Chocolates", "quantity" : 50 }
{ "_id" : "2017", "store" : "ShopMart", "item" : "Cookies", "quantity" : 120 }
{ "_id" : "2017", "store" : "ShopMart", "item" : "Pie", "quantity" : 5 }
{ "_id" : "2018", "store" : "General Store", "item" : "Cheese", "quantity" : 30 }
{ "_id" : "2018", "store" : "General Store", "item" : "Chocolates", "quantity" : 125 }
{ "_id" : "2018", "store" : "General Store", "item" : "Cookies", "quantity" : 200 }
{ "_id" : "2018", "store" : "General Store", "item" : "Pie", "quantity" : 30 }
{ "_id" : "2018", "store" : "ShopMart", "item" : "Cheese", "quantity" : 50 }
{ "_id" : "2018", "store" : "ShopMart", "item" : "Chocolates", "quantity" : 150 }
{ "_id" : "2018", "store" : "ShopMart", "item" : "Cookies", "quantity" : 100 }
{ "_id" : "2018", "store" : "ShopMart", "item" : "Nuts", "quantity" : 100 }
{ "_id" : "2018", "store" : "ShopMart", "item" : "Pie", "quantity" : 25 }
{ "_id" : "2019", "store" : "General Store", "item" : "Cheese", "quantity" : 50 }
{ "_id" : "2019", "store" : "General Store", "item" : "Chocolates", "quantity" : 125 }
{ "_id" : "2019", "store" : "General Store", "item" : "Cookies", "quantity" : 200 }
{ "_id" : "2019", "store" : "General Store", "item" : "Nuts", "quantity" : 80 }
{ "_id" : "2019", "store" : "General Store", "item" : "Pie", "quantity" : 50 }
{ "_id" : "2019", "store" : "ShopMart", "item" : "Cheese", "quantity" : 20 }
{ "_id" : "2019", "store" : "ShopMart", "item" : "Chocolates", "quantity" : 150 }
{ "_id" : "2019", "store" : "ShopMart", "item" : "Cookies", "quantity" : 100 }
{ "_id" : "2019", "store" : "ShopMart", "item" : "Nuts", "quantity" : 30 }
{ "_id" : "2019", "store" : "ShopMart", "item" : "Pie", "quantity" : 75 }
{ "_id" : "2020", "store" : "General Store", "item" : "Cheese", "quantity" : 100 }
{ "_id" : "2020", "store" : "General Store", "item" : "Chocolates", "quantity" : 200 }
{ "_id" : "2020", "store" : "General Store", "item" : "Cookies", "quantity" : 500 }
{ "_id" : "2020", "store" : "General Store", "item" : "Nuts", "quantity" : 100 }
{ "_id" : "2020", "store" : "General Store", "item" : "Pie", "quantity" : 100 }
{ "_id" : "2020", "store" : "ShopMart", "item" : "Cheese", "quantity" : 100 }
{ "_id" : "2020", "store" : "ShopMart", "item" : "Chocolates", "quantity" : 300 }
{ "_id" : "2020", "store" : "ShopMart", "item" : "Cookies", "quantity" : 400 }
{ "_id" : "2020", "store" : "ShopMart", "item" : "Nuts", "quantity" : 200 }
{ "_id" : "2020", "store" : "ShopMart", "item" : "Pie", "quantity" : 100 }

根据item汇总销售额

db.sales_2017.aggregate( [{ $unionWith: "sales_2018" },{ $unionWith: "sales_2019" },{ $unionWith: "sales_2020" },{ $group: { _id: "$item", total: { $sum: "$quantity" } } },{ $sort: { total: -1 } }
] )

• $unionWith阶段将文档从指定的集合检索到管道中
• $group阶段按item字段分组，并使用$sum计算每个item的总销售量
• $sort阶段按合计降序排列文档

结果：

{ "_id" : "Cookies", "total" : 1720 }
{ "_id" : "Chocolates", "total" : 1250 }
{ "_id" : "Nuts", "total" : 510 }
{ "_id" : "Pie", "total" : 395 }
{ "_id" : "Cheese", "total" : 350 }