在MongoDB中使用MapReduce将两个集合合并

Question

我知道 MongoDB 不支持 join 操作，但我必须使用 mapReduce 范式来模拟聚合框架中的 $lookup。我的两个集合如下：

// Employees sample 
{
  "_id" : "1234",
  "first_name" : "John",
  "last_name" : "Bush",
  "departments" : 
  [ 
    { "dep_id" : "d001", "hire_date" : "date001" },
    { "dep_id" : "d004", "hire_date" : "date004" }
  ]
}
{ 
  "_id" : "5678", 
  "first_name" : "Johny", 
  "last_name" : "Cash", 
  "departments" : [ { "dep_id" : "d001", "hire_date" : "date03" } ] 
}
{ 
  "_id" : "9012", 
  "first_name" : "Susan", 
  "last_name" : "Bowdy", 
  "departments" : [ { "dep_id" : "d004", "hire_date" : "date04" } ] 
}

// Departments sample 
{
  "_id" : "d001",
  "dep_name" : "Sales",
  "employees" : [ "1234", "5678" ]
},
{
  "_id" : "d004",
  "name" : "Quality M",
  "employees" : [ "1234", "9012" ]
}

实际上，我想要的结果是这样的：

{
  "_id" : "1234",
  "value" : 
  {
    "first_name" : "John",
    "departments" :
    [
      { "dep_id" : "d001", "dep_name" : "Sales" },
      { "dep_id" : "d004", "dep_name" : "Quality M" }
    ]
  }
}
{ 
  "_id" : "5678", 
  "value" : 
  { 
    "first_name" : "Johnny", 
    "departments" : [ { "dep_id" : "d001", "dep_name" : "Sales" } ]
  } 
}
{ 
  "_id" : "9012", 
  "value" : 
  { 
    "first_name" : "Susan", 
    "departments" : [ { "dep_id" : "d004", "dep_name" : "Quality M" } ] 
  } 
}

常见字段是dep_id（来自Employees）和_id（来自Departments）。

我的代码如下，但它不按我的需求工作。

var mapD = function() {
  for (var i=0; i<this.employees.length; i++) {
    emit(this.employees[i], { dep_id: 0, dep_name: this.dep_name });
  }
}

var mapE = function() {
  for (var i=0; i<this.departments.length; i++) {
    emit(this._id, { dep_id: this.departments[i].dep_id, dep_name: 0 });
  }
}

var reduceLookUp = function(key, values) {
  var result = {dep_id: 0, dep_name: 0};
  values.forEach(function(value) {
    if (value.dep_name !== null && value.dep_name !== undefined) {
      result.dep_name = values.dep_name;
    }
    if (value.dep_id !== null && value.dep_id !== undefined) {
      result.dep_id = value.dep_id;
    }
  });
  return result;
};

db.Departments.mapReduce(mapD, reduceLookUp, { out: { reduce: "joined" } });
db.Employees.mapReduce(mapE, reduceLookUp, { out: { reduce: "joined" } });

我非常感谢您的帮助！提前道谢。

- Vzqivan

Answer 1

在你的问题中，first_name只能从Employees集合中获取，而dep_name只能从Departments集合中获取。

你可以使用MapReduce和聚合框架来实现它。 1. MapReduce 解决方案 如果你按以下方式修改你的map和reduce函数：

var mapD = function() {
  for (var i=0; i<this.employees.length; i++)
    emit(this.employees[i], { dep_id: this._id, dep_name: this.dep_name });  
}

var mapE = function() { emit(this._id, { first_name: this.first_name }); }

var reduceLookUp = function(key, values) {
  var results = {};
  var departments = [];
  values.forEach(function(value) {
    var department = {};
    if (value.dep_id !== undefined) department["dep_id"] = value.dep_id;
    if (value.dep_name !== undefined) department["dep_name"] = value.dep_name;
    if (Object.keys(department).length > 0) departments.push(department);
    if (value.first_name !== undefined) results["first_name"] = value.first_name;
    if (value.departments !== undefined) results["departments"] = value.departments;
  });
  if (Object.keys(departments).length > 0) results["departments"] = departments;
  return results;
}

第一个 MapReduce 调用

db.Departments.mapReduce(mapD, reduceLookUp, { out: { reduce: "joined" } });

将插入到joined集合中

{ 
  "_id" : "1234", 
  "value" : 
  {
    "departments" : 
    [ 
      { "dep_id" : "d001", "dep_name" : "Sales" }, 
      { "dep_id" : "d004", "dep_name" : "Quality M" } 
    ] 
  }
}

第二次调用

db.Employees.mapReduce(mapE, reduceLookUp, { out: { reduce: "joined" } });

应该插入

{ "_id" : "1234", "value" : { "first_name" : "John" } }

但根据文档，reduce输出选项将：

如果输出集合已经存在，则将新结果与现有结果合并。如果现有文档与新结果具有相同的键，则对新文档和现有文档应用reduce函数，并用结果覆盖现有文档。

因此，在您的情况下，将再次使用参数调用reduce函数。

key = "1234",
values =
[
  {
    "departments" : 
    [ 
      { "dep_id" : "d001", "dep_name" : "Sales" }, 
      { "dep_id" : "d004", "dep_name" : "Quality M" } 
    ] 
  },
  { "first_name" : "John" }
]

最终结果是

{ 
  "_id" : "1234", 
  "value" : 
  { 
    "first_name" : "John", 
    "departments" : 
    [ 
      { "dep_id" : "d001", "dep_name" : "Sales" }, 
      { "dep_id" : "d004", "dep_name" : "Quality M" }
    ] 
  } 
}

2. 聚合框架解决方案

对于您的问题，一个更好的解决方案是使用聚合框架而不是Map-Reduce。在这里，您将使用$lookup阶段从Employees中获取一些数据。

db.Departments.aggregate([
  { $unwind: "$employees" },
  { 
    $lookup: 
      { 
        from: "Employees", 
        localField: "employees", 
        foreignField: "_id", 
        as: "employee"
      }
  },
  { $unwind: "$employee" },
  { 
    $group: 
      { 
        "_id": "$employees",
        "first_name": { $first: "$employee.first_name" }, 
        "departments": { $push: { dep_id: "$_id", dep_name: "$dep_name" } } 
      } 
  } 
]);

这将导致

{ 
  "_id" : "1234",
  "first_name" : "John",
  "departments" : 
    [ 
      { "dep_id" : "d001", "dep_name" : "Sales" }, 
      { "dep_id" : "d004", "dep_name" : "Quality M" } 
    ] 
}

- tarashypka

---

如果我想在结果中包含Employee的first_name字段，应该怎么做？ - Vzqivan

抱歉，但我需要使用map和reduce函数。:( - Vzqivan