MongoDb将$group '_id' null值作为不同的值。

I have the following data structure:

我有以下数据结构:

{
    "_superBill": {
        "$oid": "568b250ba082dfc752b20021"
    },
    "paymentProviderTxID": "aaaa",
    "transactionRaw": "abcdef",
    "commissionRate": 0.2,
    "visitDate": {
        "$date": "2016-12-26T13:04:05.544Z"
    }  
},
{
    "_superBill": {
        "$oid": "568b250ba082dfc752b20021"
    },
    "paymentProviderTxID": "bbbb",
    "transactionRaw": "abcdef",
    "commissionRate": 0.2,
    "visitDate": {
        "$date": "2016-12-26T13:04:05.544Z"
    }  
},
{
    "_superBill": null,
    "paymentProviderTxID": "cccc",
    "transactionRaw": "abcdef",
    "commissionRate": 0.2,
    "visitDate": {
        "$date": "2016-12-27T13:04:05.544Z"
    }
},
{
    "_superBill": null,
    "paymentProviderTxID": "dddd",
    "transactionRaw": "abcdef",
    "commissionRate": 0.2,
    "visitDate": {
        "$date": "2016-12-28T13:04:05.544Z"
    }
}

I have an Aggregate function that groups by the referenced _superBill field. It works great until I run into entries that have null values for _superBill and groups them all into one.

我有一个聚合函数，它通过引用的_superBill字段进行分组。它非常有用，直到我遇到具有_superBill空值的条目并将它们分组为一个。

Is there a way that I can group only the entries that have valid _superBill while still including the values that have 'null' so that I can do this in one query and sort them by visitDate?

是否有一种方法可以只对具有有效_superBill的条目进行分组，同时仍然包含具有'null'的值，以便我可以在一个查询中进行分组并按visitDate对它们进行排序?

Aggregate function:

聚合函数:

Transaction.aggregate([
    { $match: { paymentDate: { $gte: period.periodStart, $lt: period.periodEnd }} },
    {
        $group: {
            _id: "$_superBill",
            visits: { $sum: 1 }             
        }
    },
    {
        $project: {
            _id: '$_id',
            superBill: '$_id',
            visits: '$visits',          
            visitDate: '$visitDate',
            commissionRate: '$commissionRate'

        }
    }
], function(err,results) {
    // Process results
});

The result set would look like this. Note that the first 2 are grouped because they have the same _superBill _id and the other two are left ungrouped.

结果集是这样的。注意，前两个是分组的，因为它们有相同的_superBill _id，而其他两个没有分组。

{
    "_superBill": {
        "$oid": "568b250ba082dfc752b20021"
    },
    "visits": 1,
    "commissionRate": 0.2,
    "visitDate": {
        "$date": "2016-12-26T13:04:05.544Z"
    }  
},
{
    "_superBill": null,
    "visits": 1,
    "commissionRate": 0.2,
    "visitDate": {
        "$date": "2016-12-27T13:04:05.544Z"
    }
},
{
    "_superBill": null,
    "visits": 1,
    "commissionRate": 0.2,
    "visitDate": {
        "$date": "2016-12-28T13:04:05.544Z"
    }
}

Thanks for looking and appreciate any help.

谢谢您的关心和帮助。

1 个解决方案

#1

You can use the _id to $group your documents if _superBill equals null. To do that you can use the $ifNull operator.

如果_superBill等于null，可以使用_id对文档进行$组。为此，可以使用$ifNull操作符。

Transaction.aggregate(
    [ 
        { "$group": { 
             "_id": { "$ifNull": [ "$_superBill", "$_id" ] },  
             "visits": { "$sum": 1 }, 
             "visitDate": { "$first": "$visitDate" },
             "commissionRate": { "$first": "$commissionRate" }, 
             "_superBill": { "$first": "$_superBill" } 
        }}
    ],  function(err,results) {
           // Process results
        }
)

Of course you can also use the $cond operator.

当然，您也可以使用$cond操作符。

"_id": { 
    "$cond": [ 
        { "$eq": [ "$_superBill", null ] }, 
        "$_id", 
        "$_superBill" 
    ] 
}

#1