$unionWith(聚合)
在此页面上
定义
语法
$unionWith
阶段使用以下语法:
{ $unionWith: { coll: "<collection>", pipeline: [ <stage1>, ... ] } }
要包含指定集合中的所有文档而不进行任何处理,您可以使用简化的形式:
{ $unionWith: "<collection>" } // Include all documents from the specified collection
$unionWith
阶段采用包含以下字段的文档:
字段 | 必要性 | 说明 |
---|---|---|
如果省略 pipeline ,则为必需。否则为可选。 | 您希望将其管道结果包含在结果集中的集合或视图。 如果省略 | |
如果省略 coll ,则为必需。否则为可选。 | 应用于输入文档的聚合管道。
管道不能包括 |
$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" } } ])
排序规则
如果 db.collection.aggregate()
包含排序规则,则会使用该排序规则进行操作,忽略任何其他排序规则。
如果 db.collection.aggregate()
不包含排序规则,db.collection.aggregate()
方法将使用运行 db.collection.aggregate()
的顶级集合/视图的排序规则:
如果 $unionWith coll 是一个集合,它的排序规则将被忽略。
如果 $unionWith coll 是一个视图,则它必须与顶层集合/视图具有一致的排序规则。否则,操作将出错。
Atlas Search 支持
从 MongoDB 6.0 开始,您可以在 $unionWith
管道中指定 Atlas Search $search
或 $searchMeta
阶段来搜索 Atlas 集群上的集合。$search
或 $searchMeta
阶段必须是 $unionWith
管道内的第一个阶段。
[{ "$unionWith": { "coll": <collection-name>, "pipeline": [{ "$search": { "<operator>": { <operator-specification> } }, ... }] } }]
[{ "$unionWith": { "coll": <collection-name>, "pipeline": [{ "$searchMeta": { "<collector>": { <collector-specification> } }, ... }] } }]
要查看 $unionWith
与 $search
的示例,请参阅 Atlas Search 教程:使用 $unionWith 运行 Atlas Search $search 查询。
限制
限制 | 说明 |
---|---|
聚合管道不能在事务中使用 $unionWith 。 | |
分片集合 | |
$unionWith 管道不能包含 $out 阶段。 | |
$unionWith 管道不能包含 $merge 阶段。 |
举例
从年度数据集合联合创建销售报告
以下示例使用 $unionWith
阶段合并数据,并从多个集合中返回结果。在这些示例中,每个集合都包含一年的销售数据。
填充样本数据
使用以下文档创建
sales_2017
集合: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 } ] ) 使用以下文档创建
sales_2018
集合: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 } ] ) 使用以下文档创建
sales_2019
集合: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 } ] ) 使用以下文档创建
sales_2020
集合: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 } ] )
报告 1:按年份、商店和商品列出的所有销售额
以下聚合创建了一份年度销售报告,其中按季度和门店列出了所有销售额。该管道使用 $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 } } ] )
具体来说,聚合管道使用:
$set
阶段,用于更新_id
字段以包含年份。一系列
$unionWith
阶段,用于将来自四个集合的所有文档合并,每个集合还会使用$set
阶段对文档进行处理。按
_id
(年份)、store
和item
排序的$sort
阶段。
管道输出:
{ "_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 }
报告 2:按商品分类的合计销售额
以下聚合创建了一份销售报告,其中列出了每个商品的销售数量。该管道使用 $unionWith
来合并所有四年的文档:
db.sales_2017.aggregate( [ { $unionWith: "sales_2018" }, { $unionWith: "sales_2019" }, { $unionWith: "sales_2020" }, { $group: { _id: "$item", total: { $sum: "$quantity" } } }, { $sort: { total: -1 } } ] )
$unionWith
阶段的序列将指定集合中的文档检索到管道中:$sort
阶段按降序total
排列文档。
管道输出:
{ "_id" : "Cookies", "total" : 1720 } { "_id" : "Chocolates", "total" : 1250 } { "_id" : "Nuts", "total" : 510 } { "_id" : "Pie", "total" : 395 } { "_id" : "Cheese", "total" : 350 }
创建具有指定文档的联合
您可以使用$unionWith
与pipeline
字段中指定的文档执行并集。当您在pipeline
字段中指定$documents
阶段时,您可以与未存储在单独集合中的文档执行并集。
创建集合 cakeFlavors
:
db.cakeFlavors.insertMany( [ { _id: 1, flavor: "chocolate" }, { _id: 2, flavor: "strawberry" }, { _id: 3, flavor: "cherry" } ] )
以下$unionWith
操作与pipeline
$documents
字段中指定的文档执行联合:
db.cakeFlavors.aggregate( [ { $unionWith: { pipeline: [ { $documents: [ { _id: 4, flavor: "orange" }, { _id: 5, flavor: "vanilla", price: 20 } ] } ] } } ] )
输出:
[ { _id: 1, flavor: 'chocolate' }, { _id: 2, flavor: 'strawberry' }, { _id: 3, flavor: 'cherry' }, { _id: 4, flavor: 'orange' }, { _id: 5, flavor: 'vanilla', price: 20 } ]