Group in mongo excluding null values

Question

I have a mongo request that performs a batch operation on documents.

I got almost expected results, except that I want to clarify 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 look 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 delete rows if any of the group values ​​by field is empty or zero in the actual database data.

Excluded 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 } 

Answer 1

You need the extra $match pipeline step, which filters out incoming documents based on the existing built-in field "$productAttribute.colour" , and not zero:

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

Answer 2

Perhaps you need to use $ match: {'color': {$ exists: true}} before the $ group operation. With a rare index, it will work quite quickly. And do not store “zero” fields in collections at all, which will reduce the db size and increase the search speed for sparse indexes (fewer documents in the index → ​​more speed)

Answer 3

this example includes two different collections. To do this, we use the aggregate function. I also use Mongoose

1. I join the cusmtom field using customfiellabels using $ lookup
2. Expand an array using $ unwind
3. $ match to exclude a name for which the text says INACTIVE (I use REGEX)

4. $ project to rename fields so that they display correctly 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; } 

   .