MongoDB聚合：$merge 阶段（2）

最新推荐文章于 2024-07-31 13:36:13 发布

原创

最新推荐文章于 2024-07-31 13:36:13 发布 · 1.5k 阅读

18 ·

CC 4.0 BY-SA版权

文章标签：

#mongodb #数据库

本文围绕MongoDB聚合管道将结果写入指定集合的用途展开，给出多个例子。包括按需物化视图创建集合、更新/替换数据、只新增数据，还介绍了合并多个集合结果、使用管道定制合并以及使用变量自定义合并等操作，涉及不同场景下的操作步骤和注意事项。

$merge的用途是把聚合管道产生的结果写入指定的集合，有时候可以用$merge来做物化视图。下面是$merge的一些例子。

举例

按需物化视图：创建集合

当输出集合不存在时，$merge将自动创建。首先在zoo数据库的salaries集合中填充员工和部门历史数据：

db.getSiblingDB("zoo").salaries.insertMany([
   {
   
    "_id" : 1, employee: "Ant", dept: "A", salary: 100000, fiscal_year: 2017 },
   {
   
    "_id" : 2, employee: "Bee", dept: "A", salary: 120000, fiscal_year: 2017 },
   {
   
    "_id" : 3, employee: "Cat", dept: "Z", salary: 115000, fiscal_year: 2017 },
   {
   
    "_id" : 4, employee: "Ant", dept: "A", salary: 115000, fiscal_year: 2018 },
   {
   
    "_id" : 5, employee: "Bee", dept: "Z", salary: 145000, fiscal_year: 2018 },
   {
   
    "_id" : 6, employee: "Cat", dept: "Z", salary: 135000, fiscal_year: 2018 },
   {
   
    "_id" : 7, employee: "Gecko", dept: "A", salary: 100000, fiscal_year: 2018 },
   {
   
    "_id" : 8, employee: "Ant", dept: "A", salary: 125000, fiscal_year: 2019 },
   {
   
    "_id" : 9, employee: "Bee", dept: "Z", salary: 160000, fiscal_year: 2019 },
   {
   
    "_id" : 10, employee: "Cat", dept: "Z", salary: 150000, fiscal_year: 2019 }
])

然后，使用$group和$merge管道阶段，在reporting数据库中创建一个名为budgets的集合。

注意：

对于复制集或标准部署的情况，如果输出数据库不存在会自动创建数据库
对于分片集群部署的情况，要求输出指定的数据库必须已经存在。

db.getSiblingDB("zoo").salaries.aggregate( [
   {
   
    $group: {
   
    _id: {
   
    fiscal_year: "$fiscal_year", dept: "$dept" }, salaries: {
   
    $sum: "$salary" } } },
   {
   
    $merge : {
   
    into: {
   
    db: "reporting", coll: "budgets" }, on: "_id",  whenMatched: "replace", whenNotMatched: "insert" } }
] )

$group阶段根据fiscal_year和dept对salaries进行分组
$merge阶段将$group阶段处理的结果输出到reporting数据库的budgets集合。

此时，budegets将包含下面的文档：

{
   
    "_id" : {
   
    "fiscal_year" : 2017, "dept" : "A" }, "salaries" : 220000 }
{
   
    "_id" : {
   
    "fiscal_year" : 2017, "dept" : "Z" }, "salaries" : 115000 }
{
   
    "_id" : {
   
    "fiscal_year" : 2018, "dept" : "A" }, "salaries" : 215000 }
{
   
    "_id" : {
   
    "fiscal_year" : 2018, "dept" : "Z" }, "salaries" : 280000 }
{
   
    "_id" : {
   
    "fiscal_year" : 2019, "dept" : "A" }, "salaries" : 125000 }
{
   
    "_id" : {
   
    "fiscal_year" : 2019, "dept" : "Z" }, "salaries" : 310000 }

按需物化视图：更新/替换数据

下面的例子继续使用上面例子的数据，salaries集合包含了员工薪酬和部门的历史数据：

{
   
    "_id" : 1, "employee": "Ant", "dept": "A", "salary": 100000, "fiscal_year": 2017 },
{
   
    "_id" : 2, "employee": "Bee", "dept": "A", "salary": 120000, "fiscal_year": 2017 },
{
   
    "_id" : 3, "employee": "Cat", "dept": "Z", "salary": 115000, "fiscal_year": 2017 },
{
   
    "_id" : 4, "employee": "Ant", "dept": "A", "salary": 115000, "fiscal_year": 2018 },
{
   
    "_id" : 5, "employee": "Bee", "dept": "Z", "salary": 145000, "fiscal_year": 2018 },
{
   
    "_id" : 6, "employee": "Cat", "dept": "Z", "salary": 135000, "fiscal_year": 2018 },
{
   
    "_id" : 7, "employee": "Gecko", "dept": "A", "salary": 100000, "fiscal_year": 2018 },
{
   
    "_id" : 8, "employee": "Ant", "dept": "A", "salary": 125000, "fiscal_year": 2019 },
{
   
    "_id" : 9, "employee": "Bee", "dept": "Z", "salary": 160000, "fiscal_year": 2019 },
{
   
    "_id" : 10, "employee": "Cat", "dept": "Z", "salary": 150000, "fiscal_year": 2019 }

budgets集合包含了年度累计预算:

{
   
    "_id" : {
   
    "fiscal_year" : 2017, "dept" : "A" }, "salaries"</

最低0.47元/天解锁文章