ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > > mongo.Aggregate.倚天剑.横扫千军

mongo.Aggregate.倚天剑.横扫千军

作者：浪花小雨滴 时间：2020-05-22 10:25:55 0 删除 编辑

练功贵在坚持，凡在武学上面有很高造诣的，不外乎俩种人，一种天赋极高的，看一遍就会，达到武功精要，另外一种的就是勤奋的，假已时日，也能登峰造极，大多数人属于后者，气沉丹田，让我们一起来修行。。。

  • 九阴白骨爪之$addFields

简介：在文档中添加新的字段

语法：{ $addFields: { <newField>: <expression>, ... } }

支持版本： 3.4

示例：
数据源：
{
  _id: 1,
  student: "Maya",
  homework: [ 10, 5, 10 ],
  quiz: [ 10, 8 ],
  extraCredit: 
}
{
  _id: 2,
  student: "Ryan",
  homework: [ 5, 6, 5 ],
  quiz: [ 8, 8 ],
  extraCredit: 8
}
查询语句
db.scores.aggregate( [
   {
     $addFields: {
       totalHomework: { $sum: "$homework" } ,
       totalQuiz: { $sum: "$quiz" }
     }
   },
   {
     $addFields: { totalScore:
       { $add: [ "$totalHomework", "$totalQuiz", "$extraCredit" ] } }
   }
] )
输出结果
{
  "_id" : 1,
  "student" : "Maya",
  "homework" : [ 10, 5, 10 ],
  "quiz" : [ 10, 8 ],
  "extraCredit" : ,
  "totalHomework" : 25,
  "totalQuiz" : 18,
  "totalScore" : 43
}
{
  "_id" : 2,
  "student" : "Ryan",
  "homework" : [ 5, 6, 5 ],
  "quiz" : [ 8, 8 ],
  "extraCredit" : 8,
  "totalHomework" : 16,
  "totalQuiz" : 16,
  "totalScore" : 40
}

参考文章：

$addFields (aggregation)​docs.mongodb.com图标
  • 九阴白骨爪之$bucket

简介：根据特定的表达式和边界，对文档就行分类分组

语法：{
$bucket: {
groupBy: <expression>,
boundaries: [ <lowerbound1>, <lowerbound2>, ... ],
default: <literal>,
output: {
<output1>: { <$accumulator expression> },
...
<outputN>: { <$accumulator expression> }
}
}
}

版本：3.4

示例：
数据源
{ "_id" : 1, "title" : "The Pillars of Society", "artist" : "Grosz", "year" : 1926,
    "price" : NumberDecimal("199.99") }
{ "_id" : 2, "title" : "Melancholy III", "artist" : "Munch", "year" : 1902,
    "price" : NumberDecimal("280.00") }
{ "_id" : 3, "title" : "Dancer", "artist" : "Miro", "year" : 1925,
    "price" : NumberDecimal("76.04") }
{ "_id" : 4, "title" : "The Great Wave off Kanagawa", "artist" : "Hokusai",
    "price" : NumberDecimal("167.30") }
{ "_id" : 5, "title" : "The Persistence of Memory", "artist" : "Dali", "year" : 1931,
    "price" : NumberDecimal("483.00") }
{ "_id" : 6, "title" : "Composition VII", "artist" : "Kandinsky", "year" : 1913,
    "price" : NumberDecimal("385.00") }
{ "_id" : 7, "title" : "The Scream", "artist" : "Munch", "year" : 1893
    /* No price*/ }
{ "_id" : 8, "title" : "Blue Flower", "artist" : "O'Keefe", "year" : 1918,
    "price" : NumberDecimal("118.42") }
查询语句
db.artwork.aggregate( [
  {
    $bucket: {
      groupBy: "$price",
      boundaries: [ 0, 200, 400 ],
      default: "Other",
      output: {
        "count": { $sum: 1 },
        "titles" : { $push: "$title" }
      }
    }
  }
] )
输出结果
{
  "_id" : 0,
  "count" : 4,
  "titles" : [
    "The Pillars of Society",
    "Dancer",
    "The Great Wave off Kanagawa",
    "Blue Flower"
  ]
}
{
  "_id" : 200,
  "count" : 2,
  "titles" : [
    "Melancholy III",
    "Composition VII"
  ]
}
{
  "_id" : "Other",
  "count" : 2,
  "titles" : [
    "The Persistence of Memory",
    "The Scream"
  ]
}

参考文章：

$bucket (aggregation)​docs.mongodb.com图标
  • 九阴白骨爪之$bucketAuto

简介：按照指定数量对数组进行分类输出

语法：

{
$bucketAuto: {
groupBy: <expression>,
buckets: <number>,
output: {
<output1>: { <$accumulator expression> },
...
}
granularity: <string>
}
}

版本：3.4

示例：
数据源
{ "_id" : 1, "title" : "The Pillars of Society", "artist" : "Grosz", "year" : 1926,
    "price" : NumberDecimal("199.99"),
    "dimensions" : { "height" : 39, "width" : 21, "units" : "in" } }
{ "_id" : 2, "title" : "Melancholy III", "artist" : "Munch", "year" : 1902,
    "price" : NumberDecimal("280.00"),
    "dimensions" : { "height" : 49, "width" : 32, "units" : "in" } }
{ "_id" : 3, "title" : "Dancer", "artist" : "Miro", "year" : 1925,
    "price" : NumberDecimal("76.04"),
    "dimensions" : { "height" : 25, "width" : 20, "units" : "in" } }
{ "_id" : 4, "title" : "The Great Wave off Kanagawa", "artist" : "Hokusai",
    "price" : NumberDecimal("167.30"),
    "dimensions" : { "height" : 24, "width" : 36, "units" : "in" } }
{ "_id" : 5, "title" : "The Persistence of Memory", "artist" : "Dali", "year" : 1931,
    "price" : NumberDecimal("483.00"),
    "dimensions" : { "height" : 20, "width" : 24, "units" : "in" } }
{ "_id" : 6, "title" : "Composition VII", "artist" : "Kandinsky", "year" : 1913,
    "price" : NumberDecimal("385.00"),
    "dimensions" : { "height" : 30, "width" : 46, "units" : "in" } }
{ "_id" : 7, "title" : "The Scream", "artist" : "Munch",
    "price" : NumberDecimal("159.00"),
    "dimensions" : { "height" : 24, "width" : 18, "units" : "in" } }
{ "_id" : 8, "title" : "Blue Flower", "artist" : "O'Keefe", "year" : 1918,
    "price" : NumberDecimal("118.42"),
    "dimensions" : { "height" : 24, "width" : 20, "units" : "in" } }
查询语句
db.artwork.aggregate( [
   {
     $bucketAuto: {
         groupBy: "$price",
         buckets: 4
     }
   }
] )
输出结果
{
  "_id" : {
    "min" : NumberDecimal("76.04"),
    "max" : NumberDecimal("159.00")
  },
  "count" : 2
}
{
  "_id" : {
    "min" : NumberDecimal("159.00"),
    "max" : NumberDecimal("199.99")
  },
  "count" : 2
}
{
  "_id" : {
    "min" : NumberDecimal("199.99"),
    "max" : NumberDecimal("385.00")
  },
  "count" : 2
}
{
  "_id" : {
    "min" : NumberDecimal("385.00"),
    "max" : NumberDecimal("483.00")
  },
  "count" : 2
}

参考文章：

$bucketAuto (aggregation)​docs.mongodb.com图标
  • 九阴白骨爪之$collStats

简介：返回有关集合和视图的统计信息

语法：

{
$collStats:
{
latencyStats: { histograms: <boolean> },
storageStats: {},
count: {}
}
}

版本：3.4

示例：
查询语句
db.matrices.aggregate( [ { $collStats: { latencyStats: { histograms: true } } } ] )
输出结果
{ "ns" : "test.matrices",
  "host" : mongo.example.net:27017",
  "localTime" : ISODate("2017-10-06T19:43:56.599Z"),
  "latencyStats" :
    { "reads" :
        { "histogram" : [
            { "micros" : NumberLong(16),
              "count" : NumberLong(3) },
            { "micros" : NumberLong(32),
              "count" : NumberLong(1) },
            { "micros" : NumberLong(128),
              "count" : NumberLong(1) } ],
          "latency" : NumberLong(264),
          "ops" : NumberLong(5) },
      "writes" :
        { "histogram" : [
            { "micros" : NumberLong(32),
              "count" : NumberLong(1) },
            { "micros" : NumberLong(64),
              "count" : NumberLong(3) },
            { "micros" : NumberLong(24576),
              "count" : NumberLong(1) } ],
          "latency" : NumberLong(27659),
          "ops" : NumberLong(5) },
      "commands" :
        { "histogram" : [ ],
          "latency" : NumberLong(0),
          "ops" : NumberLong(0) }
    }
}

参考文章：

$collStats (aggregation)​docs.mongodb.com图标
  • 九阴白骨爪之$currentOp

简介：返回包含活动或者休眠操作的文档流，

语法：

{ $currentOp: { allUsers: <boolean>, idleConnections: <boolean>, idleSessions: <boolean>, localOps: <boolean> } }

版本：3.6

示例：
db.getSiblingDB("admin").aggregate( [
   { $currentOp : { allUsers: true, idleSessions: true } },
   { $match : { active: false, transaction : { $exists: true } } }
] )

{
   "host" : "example.mongodb.com:27017",
   "desc" : "inactive transaction",
   "client" : "198.51.100.1:53809",
   "connectionId" : NumberLong(40),
   "appName" : "",
   "clientMetadata" : {
      "driver" : {
         "name" : "PyMongo",
         "version" : "3.7.2"
      },
      "os" : {
         "type" : "Darwin",
         "name" : "Darwin",
         "architecture" : "x86_64",
         "version" : "10.14.2"
      },
      "platform" : "CPython 3.7.1.final.0"
   },
   "lsid" : {
      "id" : UUID("02039c77-3f05-4dd6-a42d-d2168a73fc95"),
      "uid" : BinData(0,"47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=")
   },
   "transaction" : {
      "parameters" : {
         "txnNumber" : NumberLong(1),
         "autocommit" : false,
         "readConcern" : {
            "level" : "snapshot"
         }
      },
      "readTimestamp" : Timestamp(1548869277, 3),
      "startWallClockTime" : "2019-01-30T12:28:06.979-0500",
      "timeOpenMicros" : NumberLong(34930476),
      "timeActiveMicros" : NumberLong(210),
      "timeInactiveMicros" : NumberLong(34930266),
      "expiryTime" : "2019-01-30T12:29:06.979-0500"
   }
   "waitingForLock" : false,
   "active" : false,
   "locks" : {
      "Global" : "w",
      "Database" : "w",
      "Collection" : "w"
   },
   "lockStats" : {
      "Global" : {
         "acquireCount" : {
            "r" : NumberLong(2),
            "w" : NumberLong(1)
         }
      },
      "Database" : {
         "acquireCount" : {
            "r" : NumberLong(1),
            "w" : NumberLong(1)
         }
      },
      "Collection" : {
         "acquireCount" : {
            "r" : NumberLong(1),
            "w" : NumberLong(1)
         }
      }
   }
}

参考文章：

$currentOp (aggregation)​docs.mongodb.com图标
  • 九阴白骨爪之$indexStats

简介：返回集合中每个索引的统计信息

语法：{ $indexStats: { } }

版本：3.2.

示例：
{ "_id" : 1, "item" : "abc", "price" : 12, "quantity" : 2, "type": "apparel" }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "type": "electronics" }
{ "_id" : 3, "item" : "abc", "price" : 10, "quantity" : 5, "type": "apparel" }

db.orders.createIndex( { item: 1, quantity: 1 } )
db.orders.createIndex( { type: 1, item: 1 } )

db.orders.find( { type: "apparel"} )
db.orders.find( { item: "abc" } ).sort( { quantity: 1 } )

db.orders.aggregate( [ { $indexStats: { } } ] )

{
   "name" : "item_1_quantity_1",
   "key" : {
      "item" : 1,
      "quantity" : 1
   },
   "host" : "examplehost.local:27017",
   "accesses" : {
      "ops" : NumberLong(1),
      "since" : ISODate("2015-10-02T14:31:53.685Z")
   }
}
{
   "name" : "_id_",
   "key" : {
      "_id" : 1
   },
   "host" : "examplehost.local:27017",
   "accesses" : {
      "ops" : NumberLong(0),
      "since" : ISODate("2015-10-02T14:31:32.479Z")
   }
}
{
   "name" : "type_1_item_1",
   "key" : {
      "type" : 1,
      "item" : 1
   },
   "host" : "examplehost.local:27017",
   "accesses" : {
      "ops" : NumberLong(1),
      "since" : ISODate("2015-10-02T14:31:58.321Z")
   }
}

参考文章：

$indexStats (aggregation)​docs.mongodb.com图标
  • 九阴白骨爪之$listLocalSessions

简介：列出内存中缓存的回话

语法：{ $listLocalSessions: <document> }

版本：3.6

示例：
List All Local Sessions
db.aggregate( [  { $listLocalSessions: { allUsers: true } } ] )

List All Local Sessions for the Specified Users
db.aggregate( [ { $listLocalSessions: { users: [ { user: "myAppReader", db: "test" } ] } } ] )

List All Local Sessions for the Current User

db.aggregate( [ { $listLocalSessions: { } } ] )

参考文章：

$listlocalSession​docs.mongodb.com图标
  • 九阴白骨爪之$listSessions

简介：列出所有存储在system.session表中的会话

语法：{ $listSessions: <document> }

版本：3.6

示例：
use config

db.system.sessions.aggregate( [  { $listSessions: { allUsers: true } } ] )

use config

db.system.sessions.aggregate( [ { $listSessions: { users: [ {user: "myAppReader", db: "test" } ] } } ] )


use config

db.system.sessions.aggregate( [ { $listSessions: { } } ] )

参考文章：

$listSessions - MongoDB Manual​docs.mongodb.com图标
  • 九阴白骨爪之$out

简介：out操作符必须是最后阶段，把聚合管道得到的文档输出到一个集合

语法：{ $out: "<output-collection>" }

版本：2.6

示例：
数据源
{ "_id" : 8751, "title" : "The Banquet", "author" : "Dante", "copies" : 2 }
{ "_id" : 8752, "title" : "Divine Comedy", "author" : "Dante", "copies" : 1 }
{ "_id" : 8645, "title" : "Eclogues", "author" : "Dante", "copies" : 2 }
{ "_id" : 7000, "title" : "The Odyssey", "author" : "Homer", "copies" : 10 }
{ "_id" : 7020, "title" : "Iliad", "author" : "Homer", "copies" : 10 }

查询语句
db.books.aggregate( [
                      { $group : { _id : "$author", books: { $push: "$title" } } },
                      { $out : "authors" }
                  ] )

输出结果
{ "_id" : "Homer", "books" : [ "The Odyssey", "Iliad" ] }
{ "_id" : "Dante", "books" : [ "The Banquet", "Divine Comedy", "Eclogues" ] }

参考文章：

$out (aggregation)​docs.mongodb.com图标
  • 九阴白骨爪之$replaceRoot

简介：把指定的新文档和内嵌文档，放到根部，替换原来所有的字段

语法：{ $replaceRoot: { newRoot: <replacementDocument> } }

版本：3.4

示例：
数据源
{
   "_id" : 1,
   "fruit" : [ "apples", "oranges" ],
   "in_stock" : { "oranges" : 20, "apples" : 60 },
   "on_order" : { "oranges" : 35, "apples" : 75 }
}
{
   "_id" : 2,
   "vegetables" : [ "beets", "yams" ],
   "in_stock" : { "beets" : 130, "yams" : 200 },
   "on_order" : { "beets" : 90, "yams" : 145 }
}

查询语句
db.produce.aggregate( [
   {
     $replaceRoot: { newRoot: "$in_stock" }
   }
] )

输出结果
{ "oranges" : 20, "apples" : 60 }
{ "beets" : 130, "yams" : 200 }

参考文章：

$replaceRoot (aggregation)​docs.mongodb.com图标
  • 九阴白骨爪之$sortByCount

简介：按照指定的表达式分组，然后按组来统计

语法：{ $sortByCount: <expression> }

版本：3.4

示例：
数据源
{ "_id" : 1, "title" : "The Pillars of Society", "artist" : "Grosz", "year" : 1926, "tags" : [ "painting", "satire", "Expressionism", "caricature" ] }
{ "_id" : 2, "title" : "Melancholy III", "artist" : "Munch", "year" : 1902, "tags" : [ "woodcut", "Expressionism" ] }
{ "_id" : 3, "title" : "Dancer", "artist" : "Miro", "year" : 1925, "tags" : [ "oil", "Surrealism", "painting" ] }
{ "_id" : 4, "title" : "The Great Wave off Kanagawa", "artist" : "Hokusai", "tags" : [ "woodblock", "ukiyo-e" ] }
{ "_id" : 5, "title" : "The Persistence of Memory", "artist" : "Dali", "year" : 1931, "tags" : [ "Surrealism", "painting", "oil" ] }
{ "_id" : 6, "title" : "Composition VII", "artist" : "Kandinsky", "year" : 1913, "tags" : [ "oil", "painting", "abstract" ] }
{ "_id" : 7, "title" : "The Scream", "artist" : "Munch", "year" : 1893, "tags" : [ "Expressionism", "painting", "oil" ] }
{ "_id" : 8, "title" : "Blue Flower", "artist" : "O'Keefe", "year" : 1918, "tags" : [ "abstract", "painting" ] }
查询语句
db.exhibits.aggregate( [ { $unwind: "$tags" },  { $sortByCount: "$tags" } ] )

输出结果

{ "_id" : "painting", "count" : 6 }
{ "_id" : "oil", "count" : 4 }
{ "_id" : "Expressionism", "count" : 3 }
{ "_id" : "Surrealism", "count" : 2 }
{ "_id" : "abstract", "count" : 2 }
{ "_id" : "woodblock", "count" : 1 }
{ "_id" : "woodcut", "count" : 1 }
{ "_id" : "ukiyo-e", "count" : 1 }
{ "_id" : "satire", "count" : 1 }
{ "_id" : "caricature", "count" : 1 }

参考文章：

$sortByCount (aggregation)​docs.mongodb.com图标
  • 九阴白骨爪之$facet

简介：在同一输入文档集上的单个阶段内处理多个聚合管道。每个子管道在输出文档中都有自己的字段，其结果作为文档数组存储。

语法：{ $facet:
{
<outputField1>: [ <stage1>, <stage2>, ... ],
<outputField2>: [ <stage1>, <stage2>, ... ],
...
}
}

版本：3.4

示例：
{ "_id" : 1, "title" : "The Pillars of Society", "artist" : "Grosz", "year" : 1926,
  "price" : NumberDecimal("199.99"),
  "tags" : [ "painting", "satire", "Expressionism", "caricature" ] }
{ "_id" : 2, "title" : "Melancholy III", "artist" : "Munch", "year" : 1902,
  "price" : NumberDecimal("280.00"),
  "tags" : [ "woodcut", "Expressionism" ] }
{ "_id" : 3, "title" : "Dancer", "artist" : "Miro", "year" : 1925,
  "price" : NumberDecimal("76.04"),
  "tags" : [ "oil", "Surrealism", "painting" ] }
{ "_id" : 4, "title" : "The Great Wave off Kanagawa", "artist" : "Hokusai",
  "price" : NumberDecimal("167.30"),
  "tags" : [ "woodblock", "ukiyo-e" ] }
{ "_id" : 5, "title" : "The Persistence of Memory", "artist" : "Dali", "year" : 1931,
  "price" : NumberDecimal("483.00"),
  "tags" : [ "Surrealism", "painting", "oil" ] }
{ "_id" : 6, "title" : "Composition VII", "artist" : "Kandinsky", "year" : 1913,
  "price" : NumberDecimal("385.00"),
  "tags" : [ "oil", "painting", "abstract" ] }
{ "_id" : 7, "title" : "The Scream", "artist" : "Munch", "year" : 1893,
  "tags" : [ "Expressionism", "painting", "oil" ] }
{ "_id" : 8, "title" : "Blue Flower", "artist" : "O'Keefe", "year" : 1918,
  "price" : NumberDecimal("118.42"),
  "tags" : [ "abstract", "painting" ] }


db.artwork.aggregate( [
  {
    $facet: {
      "categorizedByTags": [
        { $unwind: "$tags" },
        { $sortByCount: "$tags" }
      ],
      "categorizedByPrice": [
        // Filter out documents without a price e.g., _id: 7
        { $match: { price: { $exists: 1 } } },
        {
          $bucket: {
            groupBy: "$price",
            boundaries: [  0, 150, 200, 300, 400 ],
            default: "Other",
            output: {
              "count": { $sum: 1 },
              "titles": { $push: "$title" }
            }
          }
        }
      ],
      "categorizedByYears(Auto)": [
        {
          $bucketAuto: {
            groupBy: "$year",
            buckets: 4
          }
        }
      ]
    }
  }
])


{
  "categorizedByYears(Auto)" : [
    // First bucket includes the document without a year, e.g., _id: 4
    { "_id" : { "min" : null, "max" : 1902 }, "count" : 2 },
    { "_id" : { "min" : 1902, "max" : 1918 }, "count" : 2 },
    { "_id" : { "min" : 1918, "max" : 1926 }, "count" : 2 },
    { "_id" : { "min" : 1926, "max" : 1931 }, "count" : 2 }
  ],
  "categorizedByPrice" : [
    {
      "_id" : 0,
      "count" : 2,
      "titles" : [
        "Dancer",
        "Blue Flower"
      ]
    },
    {
      "_id" : 150,
      "count" : 2,
      "titles" : [
        "The Pillars of Society",
        "The Great Wave off Kanagawa"
      ]
    },
    {
      "_id" : 200,
      "count" : 1,
      "titles" : [
        "Melancholy III"
      ]
    },
    {
      "_id" : 300,
      "count" : 1,
      "titles" : [
        "Composition VII"
      ]
    },
    {
      // Includes document price outside of bucket boundaries, e.g., _id: 5
      "_id" : "Other",
      "count" : 1,
      "titles" : [
        "The Persistence of Memory"
      ]
    }
  ],
  "categorizedByTags" : [
    { "_id" : "painting", "count" : 6 },
    { "_id" : "oil", "count" : 4 },
    { "_id" : "Expressionism", "count" : 3 },
    { "_id" : "Surrealism", "count" : 2 },
    { "_id" : "abstract", "count" : 2 },
    { "_id" : "woodblock", "count" : 1 },
    { "_id" : "woodcut", "count" : 1 },
    { "_id" : "ukiyo-e", "count" : 1 },
    { "_id" : "satire", "count" : 1 },
    { "_id" : "caricature", "count" : 1 }
  ]
}

参考文章：

$facet​docs.mongodb.com


也许学完了，才发现，学习的过程没那么难以坚持，加油，下期再见。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31537575/viewspace-3154902/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： SQL从0到1：窗口函数
下一篇： windows安装docker的一次有趣的排错经历
请登录后发表评论 登录
全部评论
浪花小雨滴

注册时间：2018-05-16

  • 博文量
    4259
  • 访问量
    39921

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593