mongodb 在 mongo 中分组，不包括空值

group in mongo excluding null values

提问by starkk92

I have mongo query which does the group operation on the documents.

我有 mongo 查询，它对文档进行组操作。

I have almost got the expected results except that I want to refine the results without empty or null values.

我几乎得到了预期的结果，只是我想在没有空值或空值的情况下优化结果。

Currently my query looks like this:

目前我的查询是这样的：

db.productMetadata.aggregate([{$group:{"_id":{"color":"$productAttribute.colour","gender":"$productAttribute.gender"},"count" : {$sum : 1}}}]);

And the results looks something like this:

结果如下所示：

{ "_id" : { "color" : "BLUE", "gender" : "MEN" }, "count" : 1 }
{ "_id" : {  }, "count" : 4 }
{ "_id" : { "color" : "NA", "gender" : "WOMEN" }, "count" : 1 }
{ "_id" : { "color" : "BLACK", "gender" : "MEN" }, "count" : 1 }
{ "_id" : { "color" : "BROWN", "gender" : "WOMEN" }, "count" : 1 }
{ "_id" : { "gender" : "MEN" }, "count" : 2 }
{ "_id" : { "color" : "BEIGE", "gender" : "MEN" }, "count" : 1 }
{ "_id" : { "color" : "BROWN", "gender" : "MEN" }, "count" : 1 }

I want to remove the rows if any of the group by field values are empty or null in the actual data of DB.

如果 DB 的实际数据中的任何 group by 字段值为空或 null，我想删除这些行。

Excepted results should look something like this:

异常结果应如下所示：

{ "_id" : { "color" : "BLUE", "gender" : "MEN" }, "count" : 1 }
{ "_id" : { "color" : "NA", "gender" : "WOMEN" }, "count" : 1 }
{ "_id" : { "color" : "BLACK", "gender" : "MEN" }, "count" : 1 }
{ "_id" : { "color" : "BROWN", "gender" : "WOMEN" }, "count" : 1 }
{ "_id" : { "color" : "BEIGE", "gender" : "MEN" }, "count" : 1 }
{ "_id" : { "color" : "BROWN", "gender" : "MEN" }, "count" : 1 }

回答by chridam

You need an extra $matchpipeline step that will filter the incoming documents based on the embedded field "$productAttribute.colour"existing and not null:

您需要一个额外的$match管道步骤，它将根据"$productAttribute.colour"现有的嵌入字段而不是 null过滤传入的文档：

    db.productMetadata.aggregate([
    { 
        "$match": {
            "productAttribute.colour": { 
                "$exists": true, 
                "$ne": null 
            }
        }    
    },
    { 
        "$group": {
            "_id": {
                "color": "$productAttribute.colour",
                "gender": "$productAttribute.gender"
            },
            "count": { 
                "$sum": 1 
            }
        }   
    }        
]);

回答by Sergey Mazur

Perhaps you should use $match: {'color': {$exists: true}} before $group operation. With sparse index it will work pretty fast. And do not store "null" fields in collections at all, that will reduce db size and will increase search speed for sparseindexes (less documents in index -> more speed)

也许您应该在 $group 操作之前使用 $match: {'color': {$exists: true}} 。使用稀疏索引它会工作得非常快。并且根本不要在集合中存储“空”字段，这将减少数据库大小并提高稀疏索引的搜索速度（索引中的文档更少 -> 速度更快）

回答by Sebastian Castaldi

this example includes two different Collections. For this we use aggregate function. I am also using Mongoose

这个例子包括两个不同的集合。为此，我们使用聚合函数。我也在用猫鼬

1. I am joining the cusmtomfield with customfiellabels with $lookup
2. Flat the array with $unwind
3. $match to exclude the name that have INACTIVE in the text (I'm using REGEX)

4. $project to rename the Fields to show properly on the client

   . async getAllMasterDataCustomFields(req) {

       let response = {};
       try {
   
         response = await customfieldsModel.aggregate([
           {
             $lookup: {
               from: 'customfieldlabels',
               localField: 'cfId',
               foreignField: 'cfId',
               as: 'info'
             }
           },
           { '$unwind': { 'path': '$info', 'preserveNullAndEmptyArrays': true } },
           { '$match': { 'childs.name': { $not: /INACTIVE/ }}},
           {
             $project: {
               'cfId': 1,
               'label': '$info.label',
               'type': '$info.type',
               'childs': 1
             }
           }]).exec();
   
       } catch (e) {
         logger.log('error', `Error while getting response ${e.meesage}`);
       }
   
       return response;
     }
   

   .

1. 我正在使用 $lookup 的 customfiellabels 加入 cusmtomfield
2. 用 $unwind 平铺数组
3. $match 排除文本中包含 INACTIVE 的名称（我使用的是 REGEX）

4. $project 重命名字段以在客户端上正确显示

   . 异步 getAllMasterDataCustomFields(req) {

       let response = {};
       try {
   
         response = await customfieldsModel.aggregate([
           {
             $lookup: {
               from: 'customfieldlabels',
               localField: 'cfId',
               foreignField: 'cfId',
               as: 'info'
             }
           },
           { '$unwind': { 'path': '$info', 'preserveNullAndEmptyArrays': true } },
           { '$match': { 'childs.name': { $not: /INACTIVE/ }}},
           {
             $project: {
               'cfId': 1,
               'label': '$info.label',
               'type': '$info.type',
               'childs': 1
             }
           }]).exec();
   
       } catch (e) {
         logger.log('error', `Error while getting response ${e.meesage}`);
       }
   
       return response;
     }
   

   .