mongodb - 如何从mongoDB中的两个字段进行计数

Question

{
    "_id" : ObjectId("56bd8e9de517259412a743ab"),
    "user_token" : "mzXhdbCu",
    "sender_details" : {
        "location" : "XYZ",
        "zipcode" : "610208"
    },
    "shipping_address" : {
        "location" : "ABC",
        "zipcode" : "602578
    }
}

我一直在尝试计算两个邮政编码中每个唯一邮政编码的实例数

$sender_details.zipcode

和

$shipping_address.zipcode

我尝试使用下面的代码

db.ac_consignments.aggregate({
    $group: {
        _id: {
            "zipcode":"$sender_details.zipcode", 
            "szipcode":"$shipping_address.zipcode"
        }, 
        count: {"$sum":1}
    }
})

我收到的输出是这样的

{
    "result" : [ 
        {
            "_id" : {
                "zipcode" : "610208",
                "szipcode" : "602578"
            },
            "count" : 7
        }, 
        {
            "_id" : {
                "zipcode" : "602578",
                "szipcode" : "678705"
            },
            "count" : 51
        }
    ],
    "ok" : 1
}

但我需要的是 $sender_details.zipcode 和 $shipping_address.zipcode 中每个邮政编码的总数。所以像这样的输出

{
    "result" : [ 
        {
            "_id" : {
                "zipcode" : "610208",
            },
            "count" : 7
        }, 
        {
            "_id" : {
                "zipcode" : "602578"
            },
            "count" : 51
        }
        {
            "_id" : {
                "zipcode" : "678705"
            },
            "count" : 51
        }
    ],
    "ok" : 1
}

Answer 1

以下管道应该适合您

db.getCollection('ac_consignments').aggregate([
    {       
        $project: {
            zipcode: [ "$sender_details.zipcode", "$shipping_address.zipcode" ]
        }
    },
    {
        $unwind: "$zipcode"
    },
    {
        $group: {
            _id: "$zipcode",
            count: { $sum: 1 }
        }
    }
])

产生这样的输出

/* 1 */
{
    "_id" : "610208",
    "count" : 1.0
}

/* 2 */
{
    "_id" : "610209",
    "count" : 2.0
}

/* 3 */
{
    "_id" : "602578",
    "count" : 1.0
}

/* 4 */
{
    "_id" : "602579",
    "count" : 2.0
}

当使用以下作为示例数据时

/* 1 */
{
    "_id" : ObjectId("56bd8e9de517259412a743ab"),
    "user_token" : "mzXhdbCu",
    "sender_details" : {
        "location" : "XYZ",
        "zipcode" : "610208"
    },
    "shipping_address" : {
        "location" : "ABC",
        "zipcode" : "602578"
    }
}

/* 2 */
{
    "_id" : ObjectId("56bd8e9de517259412a743ac"),
    "user_token" : "mzXhdbCu",
    "sender_details" : {
        "location" : "XYZ",
        "zipcode" : "610209"
    },
    "shipping_address" : {
        "location" : "ABC",
        "zipcode" : "602579"
    }
}

/* 3 */
{
    "_id" : ObjectId("56bd8e9de517259412a753ac"),
    "user_token" : "mzXhdbCu",
    "sender_details" : {
        "location" : "XYZ",
        "zipcode" : "610209"
    },
    "shipping_address" : {
        "location" : "ABC",
        "zipcode" : "602579"
    }
}  

---

看下面的动图

---

旧版本更新

db.getCollection('ac_consignments').aggregate([
    {
        $project: {
            sender_zip: "$sender_details.zipcode",
            shipping_zip: "$shipping_address.zipcode",
            party: { $literal: ["sender_zip", "shipping_zip"] }
        }
    },
    {
        $unwind: "$party"
    },
    {
        $group: {
            _id: "$_id",
            zipcode: {
                $push: {
                    $cond: [
                        { $eq: ["$party", "sender_zip"] },
                        "$sender_zip",
                        "$shipping_zip"
                    ]
                }
            }
        }
    },
    {
        $unwind: "$zipcode"
    },
    {
        $group: {
            _id: "$zipcode",
            count: { $sum: 1 }
        }
    }
])