在 MongoDB 中的另一个字段上分组的文档中计算一个字段中字符串的实例数? [英] Count no.of instances of string in a field across documents grouped on another field in MongoDB?

问题描述

我有一个特定的用例，我正在尝试在一个聚合管道中找到一种方法，最好不需要硬编码任何数据值.我想根据一个属性对文档进行分组，并查看文档中特定字段的值计数.

I've got a specific use case and I'm trying to find a way to do it in one aggregation pipeline and preferably without the need to hardcode any data values. I want to group documents based on one property and see a count of values for a particular field within the document.

示例数据:

{
    "flightNum": "DL1002",
    "status": "On time",
    "date": 20191001
},
{
    "flightNum": "DL1002",
    "status": "Delayed",
    "date": 20191002
},
{
    "flightNum": "DL1002",
    "status": "On time",
    "date": 20191003
},
{
    "flightNum": "DL1002",
    "status": "Cancelled",
    "date": 20191004
},
{
    "flightNum": "DL952",
    "status": "On time",
    "date": 20191003
},
{
    "flightNum": "DL952",
    "status": "On time",
    "date": 20191004
}

我想要一个聚合管道，它可以告诉我每个航班(按航班号分组)有多少航班是准时"、延迟"或取消"的.

I want an aggregation pipeline that can tell me, for each flight (group by flightNum) how many flights were "On time", "Delayed", or "Cancelled".

期望的响应:

{
    "flightNum": "DL1002",
    "numOnTime": 2,
    "numCancelled": 1,
    "numDelayed": 1
},
{
    "flightNum": "DL952",
    "numOnTime": 2
}

字段的命名并不重要，只要它们存在于一个文档中即可.我发现我可以使用 $cond 运算符来做到这一点，但这需要我硬编码状态"字段的预期值.对于这个任意示例，值并不多，但如果添加了另一个状态，我希望不必更新查询.由于Mongo中有很多漂亮的技巧，我觉得有可能实现这一点.

It doesn't really matter the naming of the fields, so much as they are there in one document. I found that I could do this with the $cond operator, but that would require me to hard code the expected values of "status" field. For this arbitrary example, there aren't many values, but if another status is added, I would like to not have to update the query. Since there are so many nifty tricks in Mongo, I feel there is likely a way to achieve this.

推荐答案

你可以试试下面的查询:

You can try below query :

db.collection.aggregate([
    /** Group all docs based on flightNum & status & count no.of occurances */
    {
        $group: {
            _id: {
                flightNum: "$flightNum",
                status: "$status"
            },
            count: {
                $sum: 1
            }
        }
    },
    /** Group on flightNum & push an objects with key as status & value as count */
    {
        $group: {
            _id: "$_id.flightNum",
            status: {
                $push: {
                    k: "$_id.status",
                    v: "$count"
                }
            }
        }
    },
    /** Recreate status field as an object of key:value pairs from an array of objects */
    {
        $addFields: {
            status: {
                $arrayToObject: "$status"
            }
        }
    },
    /** add flightNum inside status object */
    {
        $addFields: {
            "status.flightNum": "$_id"
        }
    },
    /** Replace status field as new root for each doc in coll */
    {
        $replaceRoot: {
            newRoot: "$status"
        }
    }
])

测试: MongoDB-Playground

这篇关于在 MongoDB 中的另一个字段上分组的文档中计算一个字段中字符串的实例数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！