工程笔记：mongodb按字段计算结果聚合排序

, , , , , 评论数:

需求

现有如下mongodb数据表demo，N/A表示该条数据没有对应字段（建表语句见文末附件）。
要求查询逻辑和步骤为：

  1. 先按条件筛选表数据
  2. 按照point_time计算后的值和create_time字段降序
  3. 取出第2~4条数据

其中，point_time计算方法为：如果字段存在且值不为null且值大于等于指定时间时，取1，否则取0。

_id type create_time point_time
1 t1 2019-03-25 2018-03-25
2 t2 2019-01-07 2019-01-01
3 t3 2019-01-07 2019-02-01
4 t1 2019-05-03 N/A
5 t1 2019-01-01 2018-06-01
6 t2 2019-03-01 N/A

备注：

  1. 2和3具有相同的create_time值
  2. 4和6没有字段point_time
  3. 指定时间为2018-09-29
  4. point_time计算后，2、3为1，1、4、5、6为0
  5. 执行排序后结果按_id列出应该为：2 3 4 1 6 5
  6. 最终结果按_id列出应该为：3 4 1

换作mysql时的实现语句

如果放在mysql中，这个SQL是没有难度的，筛选后用order by排序，point_time计算后再应用到排序中即可。就如下面SQL（where语句省略，下述语句均省略）。

1
2
3
select * from demo where ...
order by if(point_time is not null and point_time >= '2018-09-29', 1, 0) desc,
         create_time desc limit 1, 3;

或者预先构建一个排序字段is_top，然后再按字段排序：

1
2
3
select *, if(point_time is not null and point_time >= '2018-09-29', 1, 0) as is_top
from demo where ...
order by is_top desc, create_time desc limit 1, 3;

mongo实现语句

但是到了mongodb的查询就不同了：

  1. sort语句语法如下，sort字段没有提供计算语法。
1
db.demo.find().sort({ field: value })
  1. find语句没有提供像mysql一样添加自定义字段的语法。

如果需要实现上述需求，需要用到mongo的聚合操作。

聚合操作简单介绍

聚合操作（aggregation operations）可以理解为一系列计算操作的管道（pipeline，我认为翻译为流水线或执行流更容易理解），操作在管道中按阶段执行。操作有如：分组（$group）、修改数据结构($project)、排序($sort)、计算数量（$count）等，每个操作在管道中叫做阶段（stage），每个阶段执行的结果会给下一个阶段。当管道执行完毕后可以得到新的数据集合（doucments，mongon将一条数据或一条命令语句叫做document，都是BSON结构）。

聚合操作有的阶段操作和查询命令相似，但并不是同样的东西，前者是阶段操作，后者是命令，不论写法还是调用方式都有些区别的，比如阶段的名称前面有$符号，阶段是作为key被调用，而命令是类似.find()这种形式的，不要混淆了。

语法

聚合操作调用语法如下，每一个{}表示一个阶段。

1
db.demo.aggregate( [ {}, {}, ... ] )

阶段语法如下，各阶段的{语句}有所不同，具体参考mongo的文档。

1
{ $阶段名: {语句} }

$addFields例子

比如要添加字段的阶段名为：$addFields，{语句}的语法为：

1
{$字段名: 指定值}
  • 现要添加字段is_top，计算方法为point_time是否大于等于指定时间2018-09-29，调用聚合操作的语句如下。注意，调用point_time时前面必须添加$符号。
1
2
3
4
5
db.demo.aggregate([{
  $addFields: {
    is_top: {$gte: ["$point_time", "2018-09-29"]}
  }
}])

$match和$sort例子

再比如筛选（$match）和排序（$sort），{语句}的语法与查询（find语句）相同，即{字段名: {$操作名, 指定值}}：

  • 现要筛选create_time大于等于2018-09-29的数据，语句如下：
1
2
3
4
5
db.demo.aggregate([{
  $match: {
    create_time: {$gte: '2018-09-29'}
  }
}])
  • 现要按照create_time降序，语句如下。
1
2
3
4
5
db.demo.aggregate([{
  $sort: {
    create_time: -1
  }
}])

需求实现

考虑到聚合操作的$sort阶段依然无法对字段进行计算，因此采用类似mysql查询语句的第二种语句实现，即添加一个排序字段。按照需求处理排序字段：

  1. 排序字段命名为is_top
  2. 字段通过point_tmie计算得出，point_tmie字段在数据中可能不存在，因此需要先判空，如果字段不存在则使用"null"替代（null字符串）
  3. 根据point_tmie不为"null"并且大于等于2018-09-29来判断排序字段为1或0

下面实现的语句或命令均为聚合操作的语句。

1. 空字段处理操作：$ifNull

聚合操作中$ifNull操作用来做空判断，与mysql的ifnull函数类似，即字段不存在或为undified或为null即用第二个参数替代。语句如下。

1
{ $ifNull: [ 字段, 替代值 ] }

现$point_time可能不存在，所以使用下面语句将值替换为"null"，注意point_time前面必须添加$符号，否则会被认为是一个字符串，结果会总是非空的。

1
{$ifNull: ["$point_time", "null"]}

2. 排序字段的计算操作：$cond、$ne、$gte、$and

排序字段计算需要用到if判断，聚合操作中if条件判断使用$cond操作，与mysql的if函数类似，语句如下。

1
{ $cond: [ 条件语句, 为真的值, 为假的值 ] }

现要判断point_time（替换空后）的值不为”null”并且大于等于2018-09-29，判断操作的语法如下：

1
{ $操作名: [ "$字段名": 比较值 ] }

那么判断不为”null”语句如下：

1
{$ne: [{$ifNull: ["$point_time", "null"]}, "null"]}

判断大于等于2018-09-29语句如下：

1
{$gte: ["$point_time", "2018-09-29"]}

要执行且操作，语法如下：

1
{ $and: [ 表达式1, 表达式2, ... ]}

那么将且操作($and)和if操作（$cond）写入语句：

1
2
3
4
5
6
7
8
9
$cond: [
  // 判断1且判断2成立，返回1，否则返回0
  {$and: [
    // $ifNull将不存在的字段替换为“null”
    // 判断1：$ne判断值不为”null“
    {$ne: [{$ifNull: ["$point_time", "null"]}, "null"]},
    // 判断2：$gte判断值大于等于指定值
    {$gte: ["$point_time", "2018-09-29"]}
  ]}, 1, 0]

需要特别注意的是和查询操作的筛选语法进行区别，虽然操作名都是一样的，但语法不同，查询语法的操作名和字段名均为key，比如查询操作语句筛选point_time大于等于2018-09-29语句如下。而聚合操作中一些比较操作语句的操作名为key，值的第一个元素才是字段名，需要注意不要混淆了。

1
2
3
db.demo.find({
  point_time: {$gte: "2018-09-29"}
});

3. 添加字段阶段：$addFields

现需要将计算完成的排序字段添加到数据结果集中（与mysql类似，不会真正的写入数据库，只是新增一个展示字段）。考虑到之前的所有字段都要展示出来，因此采用$addFields，$addFields语法如下。

1
{ $addFields: { 自定义字段名: 字段值 } }

其中，字段值可以是bson（类似json的格式），就是说可以有复杂的判断等操作。

那么语句如下，语句中字段值为上述2. 排序字段的计算操作中的if判断后的结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
{$addFields: {
  // 定义排序字段is_top
  is_top: {
    $cond: [
      // 开始条件判断
      // 判断1且判断2成立，返回1，否则返回0
      {$and: [
        // $ifNull将不存在的字段替换为“null”
        // 判断1：$ne判断值不为”null“
        {$ne: [{$ifNull: ["$point_time", "null"]}, "null"]},
        // 判断2：$gte判断值大于等于指定值
        {$gte: ["$point_time", "2018-09-29"]}
      ]}, 1, 0]
  }
}}

4. 筛选阶段：$match

比较简单，只需要注意排序在聚合操作中是$match阶段，语法和find命令筛选语法类似，语法如下。

1
{ $match: { 字段名: { $操作名: 指定值 } } }

比如要筛选create_time大于2019-09-29，语句如下：

1
2
3
{$match: {
  create_time: {$gt: "2019-09-29"}
}}

5. 排序阶段：$sort

也是需要注意其在聚合操作中是$sort阶段，语法和sort命令类似。实例语句如下：

1
2
3
4
5
{$sort: {
  // 1表示升序，-1表示降序
  is_top: - 1,
  create_time: - 1
}}

6. 分页阶段：$skip、$limit

分页与mysql类似，只是语法上使用2组阶段完成，语句如下：

1
2
3
4
// skip从0开始，1表示从第二条数据开始
{skip: 1},
// 取2~4，共3条数据
{limit: 3}

7. 阶段装配

将$addFields、$match、$sort按照语法组合，最终语句如下（mongo都是啥语法呀，还是mysql语法简洁😂）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
db.demo.aggregate(
  // 筛选stage，略
  // {$match: {
  //   字段名: { $操作名: 指定值 }
  // }},
  // 添加自定义字段stage
  {$addFields: {
    // 定义排序字段is_top
    is_top: {
      $cond: [
        // 开始条件判断
        // 判断1且判断2成立，返回1，否则返回0
        {$and: [
          // $ifNull将不存在的字段替换为“null”
          // 判断1：$ne判断值不为”null“
          {$ne: [{$ifNull: ["$point_time", "null"]}, "null"]},
          // 判断2：$gte判断值大于等于指定值
          {$gte: ["$point_time", "2018-09-29"]}
        ]}, 1, 0]
    }
  }},
  // 排序stage
  {$sort: {
    // 1表示升序，-1表示降序
    is_top: - 1,
    create_time: - 1
  }},
  // skip从0开始，1表示从第二条数据开始
  {$skip: 1},
  // 取2~4，共3条数据
  {$limit: 3}
)

8. 运行测试

运行语句后，结果结果按_id列出为：2 4 1，嗯？为什么不是3 4 1？

测试发现当2条数据排序条件相同时，分页时如果只取其中一条数据时每次都会选取顺序更靠前的那条（也不知道是不是mongo的bug…）

解决方法是强制按照_id排序即可，即$sort阶段改为：

1
2
3
4
5
6
{$sort: {
  // 1表示升序，-1表示降序
  is_top: - 1,
  create_time: - 1,
  _id: 1
}}

聚合操作拾遗

$project用法

上述语句中添加自定义字段使用的$addFields，表示保留原先的字段并再添加一个自定义字段。但是如果想修改已有字段，如删除字段，可以使用另一个阶段操作：$project（我也很是疑惑为什么叫project，可能翻译为设计、投射吧）。

  • 表示只显示字段名1和字段名2，使用语法如下：
1
2
3
4
{ $project: {
  字段名1: 1,    // 1或0，true或false
  字段名2: 1
} }
  • 表示不显示字段名1和字段名2，可使用：
1
2
3
4
{ $project: {
  字段名1: 0,    // 1或0，true或false
  字段名2: 0
} }

需要注意1和0不能混用，否则会报错（仔细想想混用的话确是不合逻辑的）。但也有个特殊字段_id，这个字段默认是显示的，如果不要显示可以设置为0，此时就可以混用0和1，语句如下：

1
2
3
4
{ $project: {
  字段名1: 1,    // 1或0，true或false
  _id: 0
} }

参考

附件

  • 数据创建语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
db.demo.drop();
db.createCollection("demo");
db.demo.insert([
{
    _id: "1",
    type: "t1",
    "create_time": "2019-03-25",
    "point_time": "2018-03-25"
},
{
    _id: "2",
    type: "t2",
    "create_time": "2019-01-07",
    "point_time": "2019-01-01"
},
{
    _id: "3",
    type: "t3",
    "create_time": "2019-01-07",
    "point_time": "2019-02-01"
},
{
    _id: "4",
    type: "t1",
    "create_time": "2019-05-03"
},
{
    _id: "5",
    type: "t1",
    "create_time": "2019-01-01",
    "point_time": "2018-06-01"
},
{
    _id: "6",
    type: "t2",
    "create_time": "2019-03-01"
}]);