$unionWith (aggregation)

Duplicate Results重复的结果

The combined results from the previous stage and the $unionWith stage can include duplicates.上一阶段和$unionWith阶段的组合结果可能包括重复项。

For example, create a suppliers collection and a warehouses collection:例如，创建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" },
])

The following aggregation combines the state field projection results from the suppliers and warehouse collections.以下聚合组合了来自suppliers和warehouse集合的state字段投影结果。

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

The result set contains duplicates:结果集包含重复项：

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

To remove the duplicates, you can include a $group stage to group by the state field:要删除重复项，可以包含$group阶段以按state字段分组：

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

The result set no longer contains duplicates:结果集不再包含重复项：

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

$unionWith a Sharded Collection分片集合

If the $unionWith stage is part of the $lookup pipeline, the $unionWith coll cannot be sharded. 如果$unionWith阶段是$lookup pipeline的一部分，则不能对$unionWith coll进行分片。For example, in the following aggregation operation, the inventory_q1 collection cannot be sharded:例如，在以下聚合操作中，inventory_q1集合无法进行分片：

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

Collation排序规则

If the db.collection.aggregate() includes a collation, that collation is used for the operation, ignoring any other collations.如果db.collection.aggregate()包含collation，则该排序规则将用于操作，而忽略任何其他排序规则。

If the db.collection.aggregate() does not include a collation, the db.collection.aggregate() method uses the collation for the top-level collection/view on which the db.collection.aggregate() is run:如果db.collection.aggregate()不包括collation，则db.collection.aggregate()方法将使用运行db.collection.aggregate()的顶级集合/视图的排序规则：

• If the $unionWith coll is a collection, its collation is ignored.如果$unionWith coll是一个集合，则会忽略其排序规则。
• If the $unionWith coll is a view, then its collation must match that of the top-level collection/view. Otherwise, the operation errors.如果$unionWith coll是一个视图，则其排序规则必须与顶级集合/视图的排序规则匹配。否则，操作将出错。

Atlas Search SupportAtlas搜索支持

Starting in MongoDB 6.0, you can specify the Atlas Search $search or $searchMeta stage in the $unionWith pipeline to search collections on the Atlas cluster. 从MongoDB 6.0开始，您可以在$unionWith管道中指定Atlas Search $search或$searchMeta阶段来搜索Atlas集群上的集合。The $search or the $searchMeta stage must be the first stage inside the $unionWith pipeline.$search或$searchMeta阶段必须是$unionWith管道内的第一个阶段。

[{
  "$unionWith": {
    "coll": <collection-name>,
    "pipeline": [{
      "$search": {
        "<operator>": {
          <operator-specification>
        }
      },
      ...
    }]
  }
}]

[{
  "$unionWith": {
    "coll": <collection-name>,
    "pipeline": [{
      "$searchMeta": {
        "<collector>": {
          <collector-specification>
        }
      },
      ...
    }]
  }
}]

Restrictions限制

Create Sales Reports from the Union of Yearly Data Collections从年度数据集合联盟创建销售报告

The following examples use the $unionWith stage to combine data and return results from multiple collections. In these examples, each collection contains a year of sales data.以下示例使用$unionWith阶段组合数据并返回多个集合的结果。在这些示例中，每个集合都包含一年的销售数据。

Report 1: All Sales by Year and Stores and Items报告1：按年份、商店和项目列出的所有销售额

The following aggregation creates a yearly sales report that lists all sales by quarter and stores. 以下汇总创建了一个年度销售报告，其中按季度和门店列出了所有销售额。The pipeline uses $unionWith to combine documents from all four collections:管道使用$unionWith组合来自所有四个集合的文档：

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 } }
] )

Specifically, the aggregation pipeline uses:具体而言，聚合管道使用：

• A $set stage to update the _id field to contain the year.一个$set阶段，用于更新_id字段以包含年份。
• A sequence of $unionWith stages to combine all documents from the four collections, each also using the $set stage on its documents.一个$unionWith阶段的序列，用于组合四个集合中的所有文档，每个集合还在其文档上使用$set阶段。
• A $sort stage to sort by the _id (the year), the store, and item.一个$sort阶段，用于根据_id（年份）、store和item进行排序。

Pipeline output:管道输出：

{ "_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 }

Report 2: Aggregated Sales by Items报告2：按项目汇总的销售额

The following aggregation creates a sales report that lists the sales quantity per item. 以下聚合创建了一个销售报告，其中列出了每个项目的销售数量。The pipeline uses $unionWith to combine documents from all four years:管道使用$unionWith组合所有四年的文档：

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

• The sequence of $unionWith stages retrieve documents from the specified collections into the pipeline:$unionWith阶段的序列将指定集合中的文档检索到管道中：
• The $group stage groups by the item field and uses $sum to calculate the total sales quantity per item.$group阶段按item字段分组，并使用$sum计算每个物料的总销售数量。
• The $sort stage orders the documents by descending total.$sort阶段按total降序排列文档。

Pipeline output:管道输出：

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