mongodb $unwind 空数组

Question

有了这些数据:

{
    "_id" : ObjectId("576948b4999274493425c08a"),
    "virustotal" : {
        "scan_id" : "4a6c3dfc6677a87aee84f4b629303c40bb9e1dda283a67236e49979f96864078-1465973544",
        "sha1" : "fd177b8c50b457dbec7cba56aeb10e9e38ebf72f",
        "resource" : "4a6c3dfc6677a87aee84f4b629303c40bb9e1dda283a67236e49979f96864078",
        "response_code" : 1,
        "scan_date" : "2016-06-15 06:52:24",
        "results" : [ 
            {
                "sig" : "Gen:Variant.Mikey.29601",
                "vendor" : "MicroWorld-eScan"
            }, 
            {
                "sig" : null,
                "vendor" : "nProtect"
            }, 
            {
                "sig" : null,
                "vendor" : "CAT-QuickHeal"
            }, 
            {
                "sig" : "HEUR/QVM07.1.0000.Malware.Gen",
                "vendor" : "Qihoo-360"
            }
        ]
    }
},
{
    "_id" : ObjectId("5768f214999274362f714e8b"),
    "virustotal" : {
        "scan_id" : "3d283314da4f99f1a0b59af7dc1024df42c3139fd6d4d4fb4015524002b38391-1466529838",
        "sha1" : "fb865b8f0227e9097321182324c959106fcd8c27",
        "resource" : "3d283314da4f99f1a0b59af7dc1024df42c3139fd6d4d4fb4015524002b38391",
        "response_code" : 1,
        "scan_date" : "2016-06-21 17:23:58",
        "results" : [ 
            {
                "sig" : null,
                "vendor" : "Bkav"
            }, 
            {
                "sig" : null,
                "vendor" : "ahnlab"
            }, 
            {
                "sig" : null,
                "vendor" : "MicroWorld-eScan"
            }, 
            {
                "sig" : "Mal/DrodZp-A",
                "vendor" : "Qihoo-360"
            }
        ]
    }
}

当 sig 不为 null 时，我试图对供应商进行分组和计数，以获得类似的信息:

{
    "_id" : "Qihoo-360",
    "count" : 2
},
{
    "_id" : "MicroWorld-eScan",
    "count" : 1
},
{
    "_id" : "Bkav",
    "count" : 0
},
{
    "_id" : "CAT-QuickHeal",
    "count" : 0
}

目前使用这段代码:

db.analysis.aggregate([ 
    { $unwind: "$virustotal.results"  },
    {
        $group : {
             _id : "$virustotal.results.vendor", 
             count : { $sum : 1 }
        }
    },
    { $sort : { count : -1 } }
])

我得到了一切:

{
    "_id" : "Qihoo-360",
    "count" : 2
},
{
    "_id" : "MicroWorld-eScan",
    "count" : 2
},
{
    "_id" : "Bkav",
    "count" : 1
},
{
    "_id" : "CAT-QuickHeal",
    "count" : 1
}

如果 sig 为 null，我如何计算为 0？

Answer 1

您的 $sum 中需要一个条件表达式 运算符将使用比较运算符 $gt 检查 "$virustotal.results.sig" 键是否为空(如 documentation's BSON comparsion order 中指定)

您可以通过添加如下表达式来重构您的管道:

db.analysis.aggregate([
    { "$unwind": "$virustotal.results" },
    {
        "$group" : {
            "_id": "$virustotal.results.vendor", 
            "count" : { 
                "$sum": {
                    "$cond": [
                        { "$gt": [ "$virustotal.results.sig", null ] },
                        1, 0
                    ]
                }
            }
        }
    },
    { "$sort" : { "count" : -1 } }
])

示例输出

/* 1 */
{
    "_id" : "Qihoo-360",
    "count" : 2
}

/* 2 */
{
    "_id" : "MicroWorld-eScan",
    "count" : 1
}

/* 3 */
{
    "_id" : "Bkav",
    "count" : 0
}

/* 4 */
{
    "_id" : "CAT-QuickHeal",
    "count" : 0
}

/* 5 */
{
    "_id" : "nProtect",
    "count" : 0
}

/* 6 */
{
    "_id" : "ahnlab",
    "count" : 0
}