时间戳

首页 > 解决方案 > 在 MongoDB 中合并 2 个集合

mongodb - 在 MongoDB 中合并 2 个集合

问题描述

我试图以几乎相同的结构加入 2 个不同的集合，唯一的区别是每个集合的概念。我真的不知道在 SQL 中像 UNION 命令一样组合 2 个表。基本上我的目标是按原样返回来自 2 个不同集合的所有数据。

下面是示例。先感谢您。

COLLECTION1
{ "_id" : ObjectId("60215fd42de966ed4e994fb7"), "customertype" : "Single", "FirstName" : "Peter", "Age" : 21 }
{ "_id" : ObjectId("60e269862161b41a5d1d050a"), "customertype" : "Single", "FirstName" : "Sam", "Age" : 24 }
{ "_id" : ObjectId("5f7d36e713f22c52226fe679"), "customertype" : "Single", "FirstName" : "Mike", "Age" : 27 }
{ "_id" : ObjectId("60215fd42de966ed4e994fb7"), "customertype" : "Single", "FirstName" : "Kobe", "Age" : 22 }
{ "_id" : ObjectId("6041ae1c3a9c2f06c8fcb18c"), "customertype" : "Single", "FirstName" : "Robin", "Age" : 18  }
{ "_id" : ObjectId("6041ae033a9c2f06c8fcb186"), "customertype" : "Single", "FirstName" : "Usain", "Age" : 20  }
{ "_id" : ObjectId("5fdc498251b3d77f747cd726"), "customertype" : "Single", "FirstName" : "Muhamad", "Age" : 25 }

COLLECTION2
{ "_id" : ObjectId("569ce2b8aff90071f0952af7"), "customertype" : "Married", "FirstName" : "Kyrie", "Age" : 35 }
{ "_id" : ObjectId("5f7beab991e9464d27fafa2c"), "customertype" : "Married", "FirstName" : "Kevin", "Age" : 34 }
{ "_id" : ObjectId("56b8ad16f17d17e35cd3bb15"), "customertype" : "Married", "FirstName" : "Anthony", "Age" : 31}
{ "_id" : ObjectId("60f8e77dc0aaff21f4102d37"), "customertype" : "Married", "FirstName" : "Nikki", "Age" : 37 }
{ "_id" : ObjectId("5f6ad6f56714265e8bbbba5b"), "customertype" : "Married", "FirstName" : "Christian", "Age" : 30}
{ "_id" : ObjectId("5f6ad3716714265e8bb9b73b"), "customertype" : "Married", "FirstName" : "John", "Age" : 38  }
{ "_id" : ObjectId("5fe3065990346227745578ff"), "customertype" : "Married", "FirstName" : "Mikee", "Age" : 40 }



Output:
{"FirstName" : "Peter", "Age" : 21 }
{"FirstName" : "Sam", "Age" : 24 }
{"FirstName" : "Mike", "Age" : 27 }
{"FirstName" : "Kobe", "Age" : 22 }
{"FirstName" : "Robin", "Age" : 18  }
{"FirstName" : "Usain", "Age" : 20  }
{"FirstName" : "Muhamad", "Age" : 25 }
{"FirstName" : "Kyrie", "Age" : 35 }
{"FirstName" : "Kevin", "Age" : 34 }
{"FirstName" : "Anthony", "Age" : 31}
{"FirstName" : "Nikki", "Age" : 37 }
{"FirstName" : "Christian", "Age" : 30}
{"FirstName" : "John", "Age" : 38  }
{"FirstName" : "Mikee", "Age" : 40 }



"SAMPLE CODE BUT NOT RUNNING"

{
    runCommand: {
        aggregate: "COLLECTION1",
        pipeline: [


   { $lookup: { from: 'COLLECTION2', localField:'?', foreignField: '_id', as: 'get2ndset' } },
         { $unwind: { path: '$get2ndset', preserveNullAndEmptyArrays: true } },

                    {
        $project:{
"FirstName":"$FirstName",
"Age":"$Age"

                }
                    }
],
"cursor": {batchSize:100000}
}
}

标签： mongodbmongodb-query

解决方案

您可以$unionWith在这样的聚合管道中使用：

  • $unionWith合并集合。
  • $project不显示某些字段。
db.collection1.aggregate([
  {
    "$unionWith": {
      "coll": "collection2",
      "pipeline": [
        {
          $set: {
            _id: "$_id"
          }
        }
      ]
    }
  },
  {
    "$project": {
      "_id": 0,
      "customertype": 0
    }
  }
])

这里的例子

如文档中所述，$unionWith对应于此 SQL 语句：

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

而且，正如你所说的“只需在 SQL 中组合 2 个表，如 UNION 命令”......也就是说，你想要的 SQL UNION。

推荐阅读