mongo回顾（八：聚合查询二）

上一篇简单的介绍了mongo的聚合查询，并举例了match与lookup的使用，今天的话继续介绍聚合查询与MongoDB Compass怎么查询聚合操作。

$replaceRoot
用指定的文档替换执行的文档，替换的字段包括_id

{ $replaceRoot: { newRoot: <replacementDocument> } }

如果replacementDocument在文档中的数据不存在，则会解析出错

db.collection.insertMany([
   { "_id": 1, "name" : { "first" : "John", "last" : "Backus" } },
   { "_id": 2, "name" : { "first" : "John", "last" : "McCarthy" } },
   { "_id": 3, "name": { "first" : "Grace", "last" : "Hopper" } },
   { "_id": 4, "firstname": "Ole-Johan", "lastname" : "Dahl" },
])

以下$replaceRoot操作将失败，因为其中一个文档没有该name字段

db.collection.aggregate([
   { $replaceRoot: { newRoot: "$name" } }
])

为了避免这种情况，可以指定一个自定义文档数据

db.collection.aggregate([
   { $replaceRoot: { newRoot: { $mergeObjects: [ { _id: "$_id", first: "", last: "" }, "$name" ] } } }
])

//result
   { "_id": 1,  "first" : "John", "last" : "Backus"  }
   { "_id": 2,  "first" : "John", "last" : "McCarthy" }
   { "_id": 3,  "first" : "Grace", "last" : "Hopper"}
   { "_id": 4, "first" : "", "last" : ""};

或者replaceRoot前用match过滤掉不匹配的数据

db.collection.aggregate([
   { $match: { name : { $exists: true, $not: { $type: "array" }, $type: "object" } } },
   { $replaceRoot: { newRoot: "$name" } }
])
//result
   { "first" : "John", "last" : "Backus"  }
   { "first" : "John", "last" : "McCarthy" }
   { "first" : "Grace", "last" : "Hopper"}

或者使用$ifNull指定name

$ifNull如果表达式的计算结果为空值，包括未定义值或缺少字段的实例，则返回替换表达式的值

db.collection.aggregate([
   { $replaceRoot: { newRoot: { $ifNull: [ "$name", { _id: "$_id", first: "", last: ""} ] } } }
])

//result
   { "first" : "John", "last" : "Backus"  }
   { "first" : "John", "last" : "McCarthy" }
   { "first" : "Grace", "last" : "Hopper"}
   {  "_id": 4, "first" : "", "last" : ""};

replaceRoot可以在replacementDocument节点创建一个新的文档用来替换原文档

db.contacts.insertMany([
  { "_id" : 1, "first_name" : "Gary", "last_name" : "Sheffield", "city" : "New York" },
  { "_id" : 2, "first_name" : "Nancy", "last_name" : "Walker", "city" : "Anaheim" },
  { "_id" : 3, "first_name" : "Peter", "last_name" : "Sumner", "city" : "Toledo" },
])

db.contacts.aggregate( [
   {
      $replaceRoot: {
         newRoot: {
            full_name: {
               $concat : [ "$first_name", " ", "$last_name" ]
            }
         }
      }
   }
] )
//result
{ "full_name" : "Gary Sheffield" }
{ "full_name" : "Nancy Walker" }
{ "full_name" : "Peter Sumner" }

replaceRoot配合mergeObjects可以补充文档数据

$mergeObjects:将多个文档合并为一个文档,不能单独出现，配合$group,$replaceRoot等出现

mergeObjects.png

db.orders.insert([
  { "_id" : 1, "item" : "abc", "price" : 12, "ordered" : 2 },
  { "_id" : 2, "item" : "jkl", "price" : 20, "ordered" : 1 }
])

db.items.insert([
  { "_id" : 1, "item" : "abc", description: "product 1", "instock" : 120 },
  { "_id" : 2, "item" : "def", description: "product 2", "instock" : 80 },
  { "_id" : 3, "item" : "jkl", description: "product 3", "instock" : 60 },
  { "_id" : 4, "item" : "abc", description: "product 4", "instock" : 20},
])

db.orders.aggregate([
   {
      $lookup: {
         from: "items",
         localField: "item",    // field in the orders collection
         foreignField: "item",  // field in the items collection
         as: "fromItems"
      }
   },
   {
      $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$fromItems", 0 ] }, "$$ROOT" ] } }
   },
   { $project: { fromItems: 0 } }
])
//redsult
{ "_id" : 1, "item" : "abc", "description" : "product 1", "instock" : 120, "price" : 12, "ordered" : 2 }
{ "_id" : 2, "item" : "jkl", "description" : "product 3", "instock" : 60, "price" : 20, "ordered" : 1 }

$arrayElemAt返回指定数组索引处的元素。正数索引从头开始，负数从尾开始，超过数组界限不返回值

接下来在MongoDB Compass中执行这个操作，Compass可以展示每一步的结果，使用也比较方便

result.png

第一步左连接后,fromItems是个数组，_id为1的文档fromItems包含两个对象；
第二步将数组的第一个对象与根数据合并，可以看到根文档多了description，instock字段；
第三步将fromItems在返回结果去除

总结：这一篇介绍了replaceRoot，ifNull，mergeObjects，arrayElemAt聚合操作的用法并简单演示了MongoDB Compass，下一篇继续其他的聚合查询操作