Mongo聚合函数

时间:2022-10-31 13:34:25

{ "_id" : ObjectId("57301c7e5fd5d6e2afa221d1"), "a" : "张三", "b" : 11, "list": [ { "name" : "aaaa", "age" : 21 } ] }
{ "_id" : ObjectId("57301c7e5fd5d6e2afa221d2"), "a" : "张三1", "b" : 22, "list": [ { "name" : "bbbb", "age" : 123 } ] }
{ "_id" : ObjectId("57301c7e5fd5d6e2afa221d3"), "a" : "张三2", "b" : 33, "list": [ { "name" : "cccc", "age" : 21 } ] }
{ "_id" : ObjectId("57301c7e5fd5d6e2afa221d4"), "a" : "张三3", "b" : 44, "list": [ { "name" : "dddd", "age" : 21 } ] }
{ "_id" : ObjectId("57301c7e5fd5d6e2afa221d5"), "a" : "张三4", "b" : 55, "list": [ { "name" : "eeee", "age" : 21 } ] }
{ "_id" : ObjectId("57301c7e5fd5d6e2afa221d6"), "a" : "张三1", "b" : 66, "list": [ { "name" : "ffff", "age" : 19 } ] }
{ "_id" : ObjectId("57301c7e5fd5d6e2afa221d7"), "a" : "张三2", "b" : 77, "list": [ { "name" : "gggg", "age" : 28 } ] }
{ "_id" : ObjectId("57301c7e5fd5d6e2afa221d8"), "a" : "张三3", "b" : 88, "list": [ { "name" : "hhhh", "age" : 28 } ] }
{ "_id" : ObjectId("57301c7e5fd5d6e2afa221d9"), "a" : "张三4", "b" : 99, "list": [ { "name" : "mmmm", "age" : 21 } ] }
{ "_id" : ObjectId("57301c7e5fd5d6e2afa221da"), "a" : "张三", "b" : 11, "list": [ { "name" : "gggg", "age" : 28 } ] }
{ "_id" : ObjectId("57301c7e5fd5d6e2afa221db"), "a" : "张三", "b" : 22, "list": [ { "name" : "hhhh", "age" : 28 } ] }
{ "_id" : ObjectId("57301c7e5fd5d6e2afa221dc"), "a" : "张三", "b" : 33, "list": [ { "name" : "mmmm", "age" : 21 } ] }

可以计算出一共有多少条数据

> db.aa.count()
12

count和find一样只有满足条件的才会参与计算

> db.aa.count({a:"张三"})
4

distinct
db.runCommand({"distinct":[集合名称], "key":[需要区分的字段])

> db.runCommand({"distinct":"aa","key":"a"})
{
  "values" : [
      "张三",
      "张三1",
      "张三2",
      "张三3",
      "张三4"
  ],
  "stats" : {
      "n" : 12,
      "nscanned" : 12,
      "nscannedObjects" : 12,
      "timems" : 76,
      "cursor" : "BasicCursor"
  },
  "ok" : 1
}

/* 0 */
{
"_id" : ObjectId("572ae180734e0336c344e8c4"),
"name" : "张三",
"books" : [{
"name" : "mongo权威指南",
"money" : 44.0,
"page" : 643.0
}, {
"name" : "语文",
"money" : 17.0,
"page" : 45.0
}]
} /* 1 */
{
"_id" : ObjectId("572ae180734e0336c344e8c5"),
"name" : "李四",
"books" : [{
"name" : "语文",
"money" : 17.0,
"page" : 45.0
}]
} /* 2 */
{
"_id" : ObjectId("572ae180734e0336c344e8c6"),
"name" : "王二",
"books" : [{
"name" : "数学",
"money" : 28.0,
"page" : 208.0
}, {
"name" : "语文",
"money" : 17.0,
"page" : 45.0
}]
} /* 3 */
{
"_id" : ObjectId("572ae180734e0336c344e8c7"),
"name" : "麻子",
"books" : [{
"name" : "英语",
"money" : 36.0,
"page" : 102.0
}, {
"name" : "化学",
"money" : 8.0,
"page" : 77.0
}]
} /* 4 */
{
"_id" : ObjectId("572ae180734e0336c344e8c8"),
"name" : "王五",
"books" : [{
"name" : "地理",
"money" : 59.0,
"page" : 54.0
}, {
"name" : "化学",
"money" : 8.0,
"page" : 77.0
}, {
"name" : "语文",
"money" : 17.0,
"page" : 45.0
}]
} /* 5 */
{
"_id" : ObjectId("572ae180734e0336c344e8c9"),
"name" : "赵六",
"books" : [{
"name" : "生物",
"money" : 22.0,
"page" : 66.0
}, {
"name" : "化学",
"money" : 8.0,
"page" : 77.0
}, {
"name" : "语文",
"money" : 17.0,
"page" : 45.0
}]
} /* 6 */
{
"_id" : ObjectId("572ae180734e0336c344e8ca"),
"name" : "孙七",
"books" : [{
"name" : "化学",
"money" : 8.0,
"page" : 77.0
}, {
"name" : "语文",
"money" : 200.0,
"page" : 45.0
}]
} /* 7 */
{
"_id" : ObjectId("572ae180734e0336c344e8cb"),
"name" : "张三",
"books" : [{
"name" : "数学",
"money" : 44.0,
"page" : 643.0
}, {
"name" : "语文",
"money" : 17.0,
"page" : 45.0
}, {
"name" : "英语",
"money" : 44.0,
"page" : 643.0
}]
}

db.aa.aggregate({"$project":{"books":1,"_id":0}})
$project 可以books从每个文档中投影出来。
这个语法与查询中德字段选择器比较像:可以通过指定 "fieldname":1选择需要投射的字段,或者通过指定"fieldname":0排除不需要的字段。执行完这个操作之后,结果集中的每个文档都会以{"_id":id,"books":"books"}这样的形式表示。

db.aa.aggregate({"$group":{"_id":"$name","count":{"$sum":1},"aaaa":{"$sum":1}}})
这里首先指定了需要进行分组的字段 "name"。这是由 "_id":"$name"指定的。
第二个字的的艺术是为分组内每个文档的 "count" 字段加1.
count这个字段是我们自己定义的一个字段。

db.aa.aggregate({"$group":{"_id":"$name","count":{"$sum":1},"aaaa":{"$sum":1}}},{"$sort":{"count":1}})
{"$sort":{"count":1}}
这个操作会对结果集中的文档根据count字段进行降序操作。 -1为升序操作

db.aa.aggregate({"$group":{"_id":"$name","count":{"$sum":1},"aaaa":{"$sum":1}}},{"$limit":2})
{"$limit":2}
这个操作会将最终的返回结果集限制为当前结果中的前2个

db.aa.aggregate({"$group":{"_id":"$name","count":{"$sum":1},"aaaa":{"$sum":1}}},{"$skip":2})
{"$skip":2}
接受一个数字n,然后抛弃掉结果集中的前n个文档,将剩余的文档作为结果集返回。

db.aa.aggregate({"$match":{"name":"张三"}})
db.aa.aggregate({"$match":{"name":"张三"}},{"$group":{"_id":"$name","count":{"$sum":1},"aaaa":{"$sum":1}}})
{"$match":{"name":"张三"}}
$match用于对文档集合进行筛选,之后就可以在筛选得到的文档子集上做聚合。

db.aa.aggregate({ "$unwind" : "$books" },{"$group":{"_id":{"book":"$books.name","money":"$money"},"count":{"$sum":1}}})
{ "$unwind" : "$books" }
拆分可以将数组中的每一个值拆分为独立的文档,例如books中是一个集合,使用unwind就可以把这些集合给拉平了,然后再进行聚合