Mongodb，有条件的$ sum [英] Mongodb, $sum with condition

问题描述

文档:

[
  {
    name: 'abc'
    length: 25,
    area: 10
  },
  {
    name: 'abc',
    length: 5
  }
]

汇总查询后的输出:

[
  {
     count: 2,
     summarizedLength: 30,
     summarizedArea: null,
     _id: {
       name: 'abc'
     }
  }
]

应该总结length和area.但仅当所有文档都具有area或length属性时.

The length and area should summarized. But only if all documents have the area or length property.

因此，如果分组属性中缺少任何length属性，则summarizedLength值应为null/undefined/not exisitng，并且与area相同.

So if any length property is missing by the grouped properties, the summarizedLength value should be null/undefined/not exisitng, and same with area.

我尝试过:

let query = mongoose.model('mycollection').aggregate([
    {
      $group: {
        _id: {
          name: $name
        },
        count: {
          $sum: 1
        },
        summarizedLength: { $sum: "$length" },
        summarizedArea: { $sum: "$area" },
      }
    }
  ]);

问题是，如果缺少任何属性，我需要取消$sum.这可能吗?

Problem is, I need to cancel the $sum if any property is missing. Is this possible?

推荐答案

来自Mongo文档

如果在包含数字和非数字值的字段上使用， $ sum忽略非数字值并返回数字的总和 值.

If used on a field that contains both numeric and non-numeric values, $sum ignores the non-numeric values and returns the sum of the numeric values.

如果用于不存在于文档中任何文档中的字段 集合，$ sum对该字段返回0.

If used on a field that does not exist in any document in the collection, $sum returns 0 for that field.

如果所有操作数均为非数字，则$ sum返回0.

If all operands are non-numeric, $sum returns 0.

我们可以$push所有面积和长度到数组，并将count与数组的长度进行比较

we can $push all area and length to array, and compare count with the length of array

db.n.aggregate(

db.n.aggregate(

    [ 
        {
            $group: {
                _id: { name: "$name" }, 
                count: { $sum: 1 }, 
                area : {$push : "$area"}, 
                length : {$push : "$length"} } 
        },
        {
            $project:{
                _id: "$_id",
                count: "$count",
                summarizedLength: { $cond : [ {$eq : [ "$count", {$size : "$length"} ]} , { $sum : ["$length"] }, "not all numbers" ] },
                summarizedArea: { $cond : [ {$eq : [ "$count", {$size : "$area"} ]} , { $sum : ["$area"] }, "not all numbers" ] },
            }
        }
    ] 
)

或者，我们可以计算defined长度和面积的数量，以及count的总数，如果计数匹配，则所有数字都未定义.

or, we can count the number of defined length and area, along with total count, if counts matching then all numbers else some undefined.

要严格检查类型，如果区域和长度可能包含非数字数据，则可以代替undefined进行$type检查

To strictly check the type, in case if area and length may contain non numeric data, instead of undefined we can do $type check

db.n.aggregate(
    [
        {
            $group: {
                _id: { name: "$name" },
                count: { $sum: 1 },
                areaCount : { $sum : { $cond : [ {$eq : [ "$area", undefined ]} , 0, 1 ] } },
                lengthCount : { $sum : { $cond : [ {$eq : [ "$length", undefined ]} , 0, 1 ] } },
                summarizedLength: { $sum: "$length"  },
                summarizedArea: { $sum: "$area"  }
            }
        },
        {
            $project : {
                _id : "$_id",
                count: "$count",
                summarizedLength: { $cond : [ {$eq : [ "$count", "$lengthCount" ]} , "$summarizedLength", "not all numbers" ] },
                summarizedArea: { $cond : [ {$eq : [ "$count", "$areaCount" ]} , "$summarizedArea", "not all numbers" ] },
            }
        }
    ]
).pretty()

输出

{
    "_id" : {
        "name" : "abc"
    },
    "count" : 2,
    "summarizedLength" : 30,
    "summarizedArea" : "not all numbers"
}

这篇关于Mongodb，有条件的$ sum的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！