$unionWith (aggregation)

On this page本页内容

Definition定义

$unionWith

New in version 4.4.在版本4.4中新增。

Performs a union of two collections. 执行两个集合的并集。$unionWith combines pipeline results from two collections into a single result set. 将来自两个集合的管道结果合并为单个结果集。The stage outputs the combined result set (including duplicates) to the next stage.该阶段将组合结果集（包括副本）输出到下一阶段。

The order in which the combined result set documents are output is unspecified.未指定组合结果集文档的输出顺序。

Syntax语法

The $unionWith stage has the following syntax:$unionWith阶段语法如下：

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

To include all documents from the specified collection without any processing, you can use the simplified form:要包含指定集合中的所有文档而不进行任何处理，您可以使用简化形式：

{ $unionWith: "<collection>" }  // Include all documents from the specified collection

The $unionWith stage takes a document with the following fields:$unionWith阶段接受具有以下字段的文档：

Field字段Description描述
coll

The collection or view whose pipeline results you wish to include in the result set.要将其pipeline结果包括在结果集中的集合或视图。

pipeline

Optional. 可选。An aggregation pipeline to apply to the specified coll.应用于指定coll的聚合管道。

[ <stage1>, <stage2>, ...]

The pipeline cannot include the $out and $merge stages.管道不能包括$out和$merge阶段。

The $unionWith operation would correspond to the following SQL statement:$unionWith操作将对应于以下SQL语句：

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

Considerations注意事项

Duplicates Results重复结果

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

For example, create a suppliers collection:例如，创建供应商集合：

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 which combines the results from the state field projection from the suppliers collection with the results from the state field projection from the warehouse collection:以下聚合将suppliers集合的state字段投影结果与warehouse集合的state域投影结果相结合：

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

As can be seen from the returned documents, 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:要删除重复项，可以在state字段中包含$group阶段以进行分组：

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管道的一部分， $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()包含一个排序规则，则该排序规则用于操作，忽略任何其他排序规则。

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()不包含排序规则，则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. 如果$unionWith coll是视图，则其排序规则必须与顶级集合/视图的排序规则匹配。Otherwise, the operation errors.否则，操作错误。

Restrictions限制

Restrictions限制Description描述
Transactions事务An aggregation pipeline cannot use $unionWith inside transactions.聚合管道不能在内部事务中使用$unionWith。
Sharded Collection分片集合If the $unionWith stage is part of the $lookup pipeline, the $unionWith coll cannot be sharded.如果$unionWith阶段是$lookup管道的一部分，$UnionWith Coll不能被分片。
$outThe $unionWith pipeline cannot include the $out stage.$unionWith管道不能包含$out阶段。
$mergeThe $unionWith pipeline cannot include the $merge stage.$unionWith管道不能包含$merge阶段。

Examples示例

Create a Yearly Report from the Union of Quarterly Data Collections根据季度数据集合联盟创建年度报告

Create a sample sales2019q1 collection with the following documents:使用以下文档创建示例sales2019q1集合：

db.sales2019q1.insertMany([
  { store: "A", item: "Chocolates", quantity: 150 },
  { store: "B", item: "Chocolates", quantity: 50 },
  { store: "A", item: "Cookies", quantity: 100 },
  { store: "B", item: "Cookies", quantity: 120 },
  { store: "A", item: "Pie", quantity: 10 },
  { store: "B", item: "Pie", quantity: 5 }
])

Create a sample sales2019q2 collection with the following documents:使用以下文档创建sales2019q2样本集合：

db.sales2019q2.insertMany([
  { store: "A", item: "Cheese", quantity: 30 },
  { store: "B", item: "Cheese", quantity: 50 },
  { store: "A", item: "Chocolates", quantity: 125 },
  { store: "B", item: "Chocolates", quantity: 150 },
  { store: "A", item: "Cookies", quantity: 200 },
  { store: "B", item: "Cookies", quantity: 100 },
  { store: "B", item: "Nuts", quantity: 100 },
  { store: "A", item: "Pie", quantity: 30 },
  { store: "B", item: "Pie", quantity: 25 }
])

Create a sample sales2019q3 collection with the following documents:使用以下文档创建sales2019q3样本集合：

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

Create a sample sales2019q4 collection with the following documents:使用以下文档创建sales2019q4样本集合：

db.sales2019q4.insertMany([
  { store: "A", item: "Cheese", quantity: 100, },
  { store: "B", item: "Cheese", quantity: 100},
  { store: "A", item: "Chocolates", quantity: 200 },
  { store: "B", item: "Chocolates", quantity: 300 },
  { store: "A", item: "Cookies", quantity: 500 },
  { store: "B", item: "Cookies", quantity: 400 },
  { store: "A", item: "Nuts", quantity: 100 },
  { store: "B", item: "Nuts", quantity: 200 },
  { store: "A", item: "Pie", quantity: 100 },
  { store: "B", item: "Pie", quantity: 100 }
])

Report 1: All Sales by Quarter and Stores and Items报告1：按季度、商店和项目划分的所有销售额

The following aggregation uses $unionWith to combine documents from all four collections to create a yearly sales report that lists all sales by quarter and stores:以下聚合使用$unionWith合并所有四个集合中的文档，以创建按季度和商店列出所有销售额的年度销售报告：

db.sales2019q1.aggregate( [
   { $set: { _id: "2019Q1" } },
   { $unionWith: { coll: "sales2019q2", pipeline: [ { $set: { _id: "2019Q2" } } ] } },
   { $unionWith: { coll: "sales2019q3", pipeline: [ { $set: { _id: "2019Q3" } } ] } },
   { $unionWith: { coll: "sales2019q4", pipeline: [ { $set: { _id: "2019Q4" } } ] } },
   { $sort: { _id: 1, store: 1, item: 1 } }
] )

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

  • a $set stage to update the _id field to contain the quarter. $set阶段更新_id字段以包含季度。That is, the documents from this stage has the form:也就是说，该阶段的文件具有以下形式：

    { "_id" : "2019Q1", "store" : "A", "item" : "Chocolates", "quantity" : 150 }
{ "_id" : "2019Q1", "store" : "B", "item" : "Chocolates", "quantity" : 50 }
...

  • a sequence of $unionWith stages to combine all documents from the four collections; each also using the $set stage on its documents. $unionWith阶段序列，用于合并四个集合中的所有文档；每个都在其文档上使用$set阶段。That is, the documents are from all four collections and have the form:也就是说，文件来自所有四个集合，并具有以下形式：

    { "_id" : "2019Q1", "store" : "A", "item" : "Chocolates", "quantity" : 150 }
{ "_id" : "2019Q1", "store" : "B", "item" : "Chocolates", "quantity" : 50 }
...
{ "_id" : "2019Q2", "store" : "A", "item" : "Cheese", "quantity" : 30 }
{ "_id" : "2019Q2", "store" : "B", "item" : "Cheese", "quantity" : 50 }
...
{ "_id" : "2019Q3", "store" : "A", "item" : "Cheese", "quantity" : 50 }
{ "_id" : "2019Q3", "store" : "B", "item" : "Cheese", "quantity" : 20 }
...
{ "_id" : "2019Q4", "store" : "A", "item" : "Cheese", "quantity" : 100 }
{ "_id" : "2019Q4", "store" : "B", "item" : "Cheese", "quantity" : 100 }

  • a $sort stage to sort by the _id (i.e. the quarter), the store, and item.$sort阶段，按_id（即季度）、store和item进行排序。

    { "_id" : "2019Q1", "store" : "A", "item" : "Chocolates", "quantity" : 150 }
{ "_id" : "2019Q1", "store" : "A", "item" : "Cookies", "quantity" : 100 }
{ "_id" : "2019Q1", "store" : "A", "item" : "Pie", "quantity" : 10 }
{ "_id" : "2019Q1", "store" : "B", "item" : "Chocolates", "quantity" : 50 }
{ "_id" : "2019Q1", "store" : "B", "item" : "Cookies", "quantity" : 120 }
{ "_id" : "2019Q1", "store" : "B", "item" : "Pie", "quantity" : 5 }
{ "_id" : "2019Q2", "store" : "A", "item" : "Cheese", "quantity" : 30 }
{ "_id" : "2019Q2", "store" : "A", "item" : "Chocolates", "quantity" : 125 }
{ "_id" : "2019Q2", "store" : "A", "item" : "Cookies", "quantity" : 200 }
{ "_id" : "2019Q2", "store" : "A", "item" : "Pie", "quantity" : 30 }
{ "_id" : "2019Q2", "store" : "B", "item" : "Cheese", "quantity" : 50 }
{ "_id" : "2019Q2", "store" : "B", "item" : "Chocolates", "quantity" : 150 }
{ "_id" : "2019Q2", "store" : "B", "item" : "Cookies", "quantity" : 100 }
{ "_id" : "2019Q2", "store" : "B", "item" : "Nuts", "quantity" : 100 }
{ "_id" : "2019Q2", "store" : "B", "item" : "Pie", "quantity" : 25 }
{ "_id" : "2019Q3", "store" : "A", "item" : "Cheese", "quantity" : 50 }
{ "_id" : "2019Q3", "store" : "A", "item" : "Chocolates", "quantity" : 125 }
{ "_id" : "2019Q3", "store" : "A", "item" : "Cookies", "quantity" : 200 }
{ "_id" : "2019Q3", "store" : "A", "item" : "Nuts", "quantity" : 80 }
{ "_id" : "2019Q3", "store" : "A", "item" : "Pie", "quantity" : 50 }
{ "_id" : "2019Q3", "store" : "B", "item" : "Cheese", "quantity" : 20 }
{ "_id" : "2019Q3", "store" : "B", "item" : "Chocolates", "quantity" : 150 }
{ "_id" : "2019Q3", "store" : "B", "item" : "Cookies", "quantity" : 100 }
{ "_id" : "2019Q3", "store" : "B", "item" : "Nuts", "quantity" : 30 }
{ "_id" : "2019Q3", "store" : "B", "item" : "Pie", "quantity" : 75 }
{ "_id" : "2019Q4", "store" : "A", "item" : "Cheese", "quantity" : 100 }
{ "_id" : "2019Q4", "store" : "A", "item" : "Chocolates", "quantity" : 200 }
{ "_id" : "2019Q4", "store" : "A", "item" : "Cookies", "quantity" : 500 }
{ "_id" : "2019Q4", "store" : "A", "item" : "Nuts", "quantity" : 100 }
{ "_id" : "2019Q4", "store" : "A", "item" : "Pie", "quantity" : 100 }
{ "_id" : "2019Q4", "store" : "B", "item" : "Cheese", "quantity" : 100 }
{ "_id" : "2019Q4", "store" : "B", "item" : "Chocolates", "quantity" : 300 }
{ "_id" : "2019Q4", "store" : "B", "item" : "Cookies", "quantity" : 400 }
{ "_id" : "2019Q4", "store" : "B", "item" : "Nuts", "quantity" : 200 }
{ "_id" : "2019Q4", "store" : "B", "item" : "Pie", "quantity" : 100 }

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

The following aggregation uses $unionWith to combine documents from all four collections to create a yearly sales report that lists the yearly sales quantity per item:以下聚合使用$unionWith合并所有四个集合中的文档，以创建年度销售报告，列出每个项目的年度销售数量：

db.sales2019q1.aggregate( [
   { $unionWith: "sales2019q2" },
   { $unionWith: "sales2019q3" },
   { $unionWith: "sales2019q4" },
   { $group: { _id: "$item", total: { $sum: "$quantity" } } },
   { $sort: { total: -1 }}
] )

  • The sequence of $unionWith stages retrieve documents from the specified collections into the pipeline:$unionWith阶段的顺序将指定集合中的文档检索到管道中：

    { "_id" : ObjectId("5e505848c15f157c0793fec7"), "store" : "A", "item" : "Chocolates", "quantity" : 150 }
{ "_id" : ObjectId("5e505848c15f157c0793fec8"), "store" : "B", "item" : "Chocolates", "quantity" : 50 }
{ "_id" : ObjectId("5e505848c15f157c0793fec9"), "store" : "A", "item" : "Cookies", "quantity" : 100 }
{ "_id" : ObjectId("5e505848c15f157c0793feca"), "store" : "B", "item" : "Cookies", "quantity" : 120 }
{ "_id" : ObjectId("5e505848c15f157c0793fecb"), "store" : "A", "item" : "Pie", "quantity" : 10 }
{ "_id" : ObjectId("5e505848c15f157c0793fecc"), "store" : "B", "item" : "Pie", "quantity" : 5 }
{ "_id" : ObjectId("5e50584bc15f157c0793fecd"), "store" : "A", "item" : "Cheese", "quantity" : 30 }
{ "_id" : ObjectId("5e50584bc15f157c0793fece"), "store" : "B", "item" : "Cheese", "quantity" : 50 }
{ "_id" : ObjectId("5e50584bc15f157c0793fecf"), "store" : "A", "item" : "Chocolates", "quantity" : 125 }
{ "_id" : ObjectId("5e50584bc15f157c0793fed0"), "store" : "B", "item" : "Chocolates", "quantity" : 150 }
{ "_id" : ObjectId("5e50584bc15f157c0793fed1"), "store" : "A", "item" : "Cookies", "quantity" : 200 }
{ "_id" : ObjectId("5e50584bc15f157c0793fed2"), "store" : "B", "item" : "Cookies", "quantity" : 100 }
...

  • The $group stage groups by the item field and uses $sum to calculate the yearly total sales quantity per item:$group阶段按项目字段分组，并使用$sum计算每个项目的年度总销售数量：

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

  • The $sort stage orders the documents by descending total.$sort阶段按total降序排列文档。

    { "_id" : "Cookies", "total" : 1940 }
{ "_id" : "Chocolates", "total" : 1450 }
{ "_id" : "Nuts", "total" : 510 }
{ "_id" : "Pie", "total" : 410 }
{ "_id" : "Cheese", "total" : 350 }

Alternatively, you could specify the $group stage within each $unionWith stage:或者，您可以在每个$unionWith阶段中指定$group阶段：

db.sales2019q1.aggregate( [
   { $group: { _id: "$item", total: { $sum: "$quantity" } } },
   { $unionWith: { coll: "sales2019q2", pipeline: [ { $group: { _id: "$item", total: { $sum: "$quantity" } } } ] } },
   { $unionWith: { coll: "sales2019q3", pipeline: [ { $group: { _id: "$item", total: { $sum: "$quantity" } } } ] } },
   { $unionWith: { coll: "sales2019q4", pipeline: [ { $group: { _id: "$item", total: { $sum: "$quantity" } } } ] } },
   { $group: { _id: "$_id", total: { $sum: "$total" } } },
   { $sort: { total: -1 }}
] )

  • The first $group groups the 2019q1 sales totals by items:第一个$group按项目对2019年第一季度的销售总额进行分组：

    { "_id" : "Pie", "total" : 30 }
{ "_id" : "Cookies", "total" : 440 }
{ "_id" : "Chocolates", "total" : 400 }

  • The sequence of $unionWith stages groups the sales total by the items from the specified collections into the pipeline:$unionWith阶段序列按指定集合中的项目将销售总额分组到管道中：

    { "_id" : "Cookies", "total" : 440 }
{ "_id" : "Chocolates", "total" : 400 }
{ "_id" : "Pie", "total" : 30 }
{ "_id" : "Chocolates", "total" : 275 }  // From sales2019q2
{ "_id" : "Nuts", "total" : 100 }
        // From sales2019q2
{ "_id" : "Cheese", "total" : 80 }
       // From sales2019q2
{ "_id" : "Pie", "total" : 55 }
          // From sales2019q2
{ "_id" : "Cookies", "total" : 300 }
     // From sales2019q2
...
                                      // Results from sales2019 q3 and q4 omitted for brevity

  • The last $group stage groups these quarterly groupings:最后一个$group阶段将这些季度分组：

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

  • The $sort stage orders the documents by descending total.$sort阶段按total降序排列文档。

    { "_id" : "Cookies", "total" : 1940 }
{ "_id" : "Chocolates", "total" : 1450 }
{ "_id" : "Nuts", "total" : 510 }
{ "_id" : "Pie", "total" : 410 }
{ "_id" : "Cheese", "total" : 350 }
←  $sortByCount (aggregation)$unset (aggregation) →