| 盈彩体育注册(中国)有限公司 |  您所在的位置：网站首页 ›
盈彩体育注册(中国)有限公司 › $unionWith (aggregation) |
$unionWith (aggregation)
|
Docs Home → Develop Applications → MongoDB Manual $unionWith (aggregation)On this page DefinitionSyntaxConsiderationsExamplesThis version of the documentation is archived and no longer supported. View the current documentation to learn how to upgrade your version of MongoDB server.Definition$unionWithNew in version 4.4. Performs a union of two collections. $unionWith combinespipeline results from two collections into a single result set. The stageoutputs the combined result set (including duplicates) to the next stage. The order in which the combined result set documents are output isunspecified. SyntaxThe $unionWith stage has the following syntax: { $unionWith: { coll: "", pipeline: [ >, ... ] } }To include all documents from the specified collection without anyprocessing, you can use the simplified form: { $unionWith: "" } // Include all documents from the specified collectionThe $unionWith stage takes a document with the followingfields: FieldDescriptioncollThe collection or view whose pipelineresults you wish to include in the result set. pipelineOptional. An aggregation pipeline to apply to the specifiedcoll. [ , , ...] The pipeline cannot include the $out and$merge stages. The $unionWith operation would correspond to the followingSQL statement: SELECT *FROM Collection1WHERE ...UNION ALLSELECT *FROM Collection2WHERE ...ConsiderationsDuplicates ResultsThe combined results from the previous stage and the$unionWith stage can include duplicates. 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 statefield projection from the suppliers collection with the resultsfrom the state field projection from the warehouse collection: db.suppliers.aggregate([ { $project: { state: 1, _id:} }, { $unionWith: { coll: "warehouses", pipeline: [ { $project: { state: 1, _id:} } ]} }])As can be seen from the returned documents, the result set containsduplicates: { "state" : "Texas" }{ "state" : "Colorado" }{ "state" : "Rhode Island" }{ "state" : "California" }{ "state" : "Colorado" }{ "state" : "Florida" }To remove the duplicates, you can include a $group stage togroup by the state field: db.suppliers.aggregate([ { $project: { state: 1, _id:} }, { $unionWith: { coll: "warehouses", pipeline: [ { $project: { state: 1, _id:} } ]} }, { $group: { _id: "$state" } }])The result set no longer contains duplicates: { "_id" : "California" }{ "_id" : "Texas" }{ "_id" : "Florida" }{ "_id" : "Colorado" }{ "_id" : "Rhode Island" }$unionWith a Sharded CollectionIf the $unionWith stage is part of the $lookuppipeline, the $unionWith coll cannot be sharded. For example, in the followingaggregation operation, the inventory_q1 collection cannot besharded: db.suppliers.aggregate([ { $lookup: { from: "warehouses", let: { order_item: "$item", order_qty: "$ordered" }, pipeline: [ ... { $unionWith: { coll: "inventory_q1", pipeline: [ ... ] } }, ... ], as: "stockdata" } }])CollationIf the db.collection.aggregate() includes a collation, that collation is used forthe operation, ignoring any other collations. If the db.collection.aggregate() does not include acollation, thedb.collection.aggregate() method uses the collation for thetop-level collection/view on which thedb.collection.aggregate() is run: If the $unionWith coll is a collection, itscollation is ignored. If the $unionWith coll is a view, then its collation must match that of the top-levelcollection/view. Otherwise, the operation errors. RestrictionsRestrictionsDescriptionTransactionsAn aggregation pipeline cannot use $unionWith insidetransactions.Sharded CollectionIf the $unionWith stage is part of the $lookuppipeline, the $unionWith coll cannot be sharded.$outThe $unionWith pipeline cannotinclude the $out stage.$mergeThe $unionWith pipeline cannotinclude the $merge stage.ExamplesCreate a Yearly Report from the Union of Quarterly Data CollectionsCreate a sample sales2019q1 collection with the following documents: 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: 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: 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: 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 ItemsThe following aggregation uses $unionWith to combinedocuments from all four collections to create a yearly sales reportthat lists all sales by quarter and stores: 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 thequarter. 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 documentsfrom the four collections; each also using the $set stageon its documents. That is, the documents are from all fourcollections 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. { "_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 ItemsThe following aggregation uses $unionWith to combinedocuments from all four collections to create a yearly sales reportthat lists the yearly sales quantity per item: db.sales2019q1.aggregate( [ { $unionWith: "sales2019q2" }, { $unionWith: "sales2019q3" }, { $unionWith: "sales2019q4" }, { $group: { _id: "$item", total: { $sum: "$quantity" } } }, { $sort: { total: -1 }}] )The sequence of $unionWith stages retrieve documentsfrom the specified collections into the pipeline: { "_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: { "_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 descendingtotal. { "_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 withineach $unionWith stage: 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: { "_id" : "Pie", "total" : 30 }{ "_id" : "Cookies", "total" : 440 }{ "_id" : "Chocolates", "total" : 400 }The sequence of $unionWith stages groups the sales total bythe items from the specified collections into the pipeline: { "_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 brevityThe last $group stage groups these quarterly groupings: { "_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 descendingtotal. { "_id" : "Cookies", "total" : 1940 }{ "_id" : "Chocolates", "total" : 1450 }{ "_id" : "Nuts", "total" : 510 }{ "_id" : "Pie", "total" : 410 }{ "_id" : "Cheese", "total" : 350 }← $sortByCount (aggregation)$unset (aggregation) → |
【本文地址】 转载请注明
