聚合管道

聚合管道是一个基于数据处理管道概念建模的数据聚合框架。文档进入多阶段管道,该管道将文档转换成汇总结果,其语法 db.collction.aggregate(<pipeline>, <options>)

• pipeline: 类型array,定义文档使用的聚合管道和操作符
• options: 传递给聚合命令的其他选项。

管道表达式

管道表达式只可以操作当前管道中的文档,不能访问其他的文档.表达式操作可以在内存中完成对文档的转换

常用表达式

聚合阶段

方法

示例

$project

# 修改管道中的文档.输出 隐藏_id, 显示name,生成新字段city值为location第2个值

> db.qvbilam.aggregate([{ $project: { _id: false, name: true, city: {$arrayElemAt: ["$location",0]} } } ])

# 输出,当location有字段为字符串时会报错
{ "name" : "angelQvbilam", "city" : null }
{ "name" : "PiaoYao", "city" : "HeBei" }
{ "name" : "ZhaoX", "city" : "ZheJiang" }

$match

# 匹配管道中location第一个值为"HeBei"的值

> db.qvbilam.aggregate({ $match: { "location.0": "HeBei"  }  })

# 返回

{ "_id" : ObjectId("602f7e8dc4f596e5b431db1d"), "name" : "PiaoYao", "num" : 21, "location" : [ "HeBei", "ShiJiaZhuang", "ShenZe" ] }

$unwind

语法

{
  $unwind:
    {
      path: <field path>,
      includeArrayIndex: <string>,
      preserveNullAndEmptyArrays: <boolean>
    }
}

说明

db.qvbilam.aggregate({ $unwind: { path: "$field", preserverNullAndEmptyArrays: true } })

示例

# 获取location不为空的文档

> db.qvbilam.find({ location: {$ne: null} }, { _id:false, num: false })

# 返回

{ "name" : "PiaoYao", "location" : [ "HeBei", "ShiJiaZhuang", "ShenZe" ] }
{ "name" : "ZhaoX", "location" : [ "ZheJiang", "HangZhou", "XiaoShan" ] }
{ "name" : "ZhaoC", "location" : "ChongQing" }


# 展开数组

> db.qvbilam.aggregate({ $match: { location: {$ne: null} } }, {$unwind: { path: "$location" }}, {$project: { _id: false, num: false }})

# 返回
{ "name" : "PiaoYao", "location" : "HeBei" }
{ "name" : "PiaoYao", "location" : "ShiJiaZhuang" }
{ "name" : "PiaoYao", "location" : "ShenZe" }
{ "name" : "ZhaoX", "location" : "ZheJiang" }
{ "name" : "ZhaoX", "location" : "HangZhou" }
{ "name" : "ZhaoX", "location" : "XiaoShan" }
{ "name" : "ZhaoC", "location" : "ChongQing" }

$lookup

语法

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

说明

示例

# 为新的集合插入文档
db.location.insertMany([
    {
    name: "HeBei",
    longitude: 114.468664,
    Latitude: 38.037057
    },
    {
    name: "ShiJiaZhuang",
    longitude: 114.514859,
    Latitude: 38.042306
    },
    {
    name: "ShenZe",
    longitude: 115.20094,
    Latitude: 38.18417
    },
    {
    name: "ZheJiang",
    longitude: 120.152791,
    Latitude: 30.267446
    },
    {
    name: "HangZhou",
    longitude: 120.155070,
    Latitude: 30.274084
    },
    {
    name: "XiaoShan",
    longitude: 120.49328,
    Latitude: 30.283330
    },
    {
    name: "ChongQing",
    longitude: 106.551556,
    Latitude: 29.563009
    }
])

# 查询

db.qvbilam.aggregate([{ $match: { location: {$ne: null} } },{
  $lookup: {
    from: "location",
    localField: "location",
    foreignField: "name",
    as: "longitudeAndLatitude"
  }
},{ $project: { _id: false, num: false} }])

# 返回

{ "_id" : ObjectId("602f7e8dc4f596e5b431db1d"), "name" : "PiaoYao", "num" : 21, "location" : [ "HeBei", "ShiJiaZhuang", "ShenZe" ], "longitudeAndLatitude" : [ { "_id" : ObjectId("6034a13b0b4db02284f61ce9"), "name" : "HeBei", "longitude" : 114.468664, "Latitude" : 38.037057 }, { "_id" : ObjectId("6034a13b0b4db02284f61cea"), "name" : "ShiJiaZhuang", "longitude" : 114.514859, "Latitude" : 38.042306 }, { "_id" : ObjectId("6034a13b0b4db02284f61ceb"), "name" : "ShenZe", "longitude" : 115.20094, "Latitude" : 38.18417 } ] }
{ "_id" : ObjectId("602f7eccc4f596e5b431db1e"), "name" : "ZhaoX", "num" : 50, "location" : [ "ZheJiang", "HangZhou", "XiaoShan" ], "longitudeAndLatitude" : [ { "_id" : ObjectId("6034a13b0b4db02284f61cec"), "name" : "ZheJiang", "longitude" : 120.152791, "Latitude" : 30.267446 }, { "_id" : ObjectId("6034a13b0b4db02284f61ced"), "name" : "HangZhou", "longitude" : 120.15507, "Latitude" : 30.274084 }, { "_id" : ObjectId("6034a13b0b4db02284f61cee"), "name" : "XiaoShan", "longitude" : 120.49328, "Latitude" : 30.28333 } ] }
{ "_id" : ObjectId("6034969a0b4db02284f61ce8"), "name" : "ZhaoC", "num" : 51, "location" : "ChongQing", "longitudeAndLatitude" : [ { "_id" : ObjectId("6034a13b0b4db02284f61cef"), "name" : "ChongQing", "longitude" : 106.551556, "Latitude" : 29.563009 } ] }

# 查询 隐藏管道内部分字段与链表部分字段

db.qvbilam.aggregate([
  { $match: { location: {$ne: null} } },
  { $unwind: { path: "$location" } },{
  $lookup: {
    from: "location",
    localField: "location",
    foreignField: "name",
    as: "longitudeAndLatitude"
  }
},{ $project: { _id: false, num: false, "longitudeAndLatitude._id": false, "longitudeAndLatitude.name": false} }])

# 返回

{ "name" : "PiaoYao", "location" : "HeBei", "longitudeAndLatitude" : [ { "longitude" : 114.468664, "Latitude" : 38.037057 } ] }
{ "name" : "PiaoYao", "location" : "ShiJiaZhuang", "longitudeAndLatitude" : [ { "longitude" : 114.514859, "Latitude" : 38.042306 } ] }
{ "name" : "PiaoYao", "location" : "ShenZe", "longitudeAndLatitude" : [ { "longitude" : 115.20094, "Latitude" : 38.18417 } ] }
{ "name" : "ZhaoX", "location" : "ZheJiang", "longitudeAndLatitude" : [ { "longitude" : 120.152791, "Latitude" : 30.267446 } ] }
{ "name" : "ZhaoX", "location" : "HangZhou", "longitudeAndLatitude" : [ { "longitude" : 120.15507, "Latitude" : 30.274084 } ] }
{ "name" : "ZhaoX", "location" : "XiaoShan", "longitudeAndLatitude" : [ { "longitude" : 120.49328, "Latitude" : 30.28333 } ] }
{ "name" : "ZhaoC", "location" : "ChongQing", "longitudeAndLatitude" : [ { "longitude" : 106.551556, "Latitude" : 29.563009 } ] }

$group

语法

{ 
  $group: { 
    _id: <expression>, 
    <field1>: { <accumulator1> : <expression1> }, 
    ... 
  }
}

• _id字段是必需的.可以将_id值指定为null，以计算整个输入文档.
• 其余的计算字段是可选的,使用<accumulator>操作符进行计算.
• _id和<accumulator>表达式可以接受任何有效的表达式.

操作符

示例

插入订单集合.包含用户名称,单价,购买数量以及商品id

db.order_info.insertMany([
{
     "name": "qvbilam",
  "price": 500,
  "num": 10,
  "goods_id": 1
},
{
     "name": "qvbilam",
  "price": 200,
  "num": 2,
  "goods_id": 2
},
{
     "name": "GuoOne",
  "price": 40,
  "num": 3,
  "goods_id": 2
},
{
     "name": "GuoOne",
  "price": 45,
  "num": 8,
  "goods_id": 1
},
])

通过用户姓名进行分组

# 通过$name字段作为主键进行分组

db.order_info.aggregate({ $group: {_id: "$name"} })

# 返回

{ "_id" : "GuoOne" }
{ "_id" : "qvbilam" }

对分组后的文档进行聚合运算

• _id: 按照$name字段进行分组
• totalNum: 计算$num数量
• totalMoney: 计算总价钱(数量 * 单价的总和)
• avgNum: 计算数量的平均值
• countOrder: 统计订单数量
• maxMoney: 计算每笔订单最大的总价

# 分组后聚合运算

db.order_info.aggregate({ 
  $group: {
    _id: "$name",
    totalNum: {$sum: "$num"},
    totalMoney: {$sum : { $multiply: ["$num","$price"] } },
    avgNum: { $avg: "$num" },
    countOrder: {$sum: 1},
    maxMoney: { $max: { $multiply: ["$num","$price"] } },
  }
})

# 返回

{ "_id" : "GuoOne", "totalNum" : 11, "totalMoney" : 480, "avgNum" : 5.5, "countOrder" : 2, "maxMoney" : 360 }
{ "_id" : "qvbilam", "totalNum" : 12, "totalMoney" : 5400, "avgNum" : 6, "countOrder" : 2, "maxMoney" : 5000 }

分组后生成新的数组字段

# 分组后设置新的字段

db.order_info.aggregate({ $group: { _id: "$name", numArr: { $push: { num: "$num", price: "$price" } } } })

# 返回
{ "_id" : "GuoOne", "numArr" : [ { "num" : 3, "price" : 40 }, { "num" : 8, "price" : 45 } ] }
{ "_id" : "qvbilam", "numArr" : [ { "num" : 10, "price" : 500 }, { "num" : 2, "price" : 200 } ] } 

不进行分组进行计算

# 对所有文档的num值进行求和

db.order_info.aggregate({ $group: {_id: null, num: {$sum : "$num"}} })

# 返回

{ "_id" : null, "num" : 23 } 

聚合选项

说明

{
  aggregate: "<collection>",
  pipeline: [ <stage>, <...> ],
  explain: <boolean>,
  allowDiskUse: <boolean>,
  cursor: <document>,
  bypassDocumentValidation: <boolean>,
  readConcern: <document>,
  collation: <document>
}

管道优化