MongoDB aggregation skip null value key

Question

I am trying to remove duplicate value from array which I am able to successfully achieve with below query however having difficulties skip key where values is null. I am using following code

db.mobile_data.aggregate([{$unwind: '$All_Participants'}, 
{$group: {_id:'$_id',All_Participants: {$addToSet: '$All_Participants'}, 
  Chat_group: {$first: '$Chat_group'}, Message_id: {$first: '$Message_id'} }}]);

my output result as follow

{ 
    "_id" : ObjectId("5856b1e39a47e6d13dab370b"), 
    "All_Participants" : [
        "user1", 
        "user4"
    ], 
    "Chat_group" : 67.0, 
    "Message_id" : Null
}

How can ignore Message_id if value is null? Expected output should be

{ 
    "_id" : ObjectId("5856b1e39a47e6d13dab370b"), 
    "All_Participants" : [
        "user1", 
        "user4"
    ], 
    "Chat_group" : 67.0
}

I think this can help: http://stackoverflow.com/questions/33123396/group-in-mongo-excluding-null-values — Hedegare, Dec 18 '16 at 21:01

Mandeep Singh · Answer 1 · 2019-11-05T17:21:29.193

Unfortunately, there is no way to remove 'null' fields during $group stage. We would need to add an additional $project stage after the $group stage to remove the null fields:

db.mobile_data.aggregate([
{$unwind: '$All_Participants'}, 
{$group: {_id:'$_id',All_Participants: {$addToSet: '$All_Participants'}, 
  Chat_group: {$max: '$Chat_group'}, Message_id: {$first: '$Message_id'} }},

{$project: {'All_Participants': 1, 'Chat_group': 1, 'Message_id': {
            $cond: {
               if: { $ifNull: [ "$Message_id", true ] },
               then: "$$REMOVE",
               else: "$Message_id"
            }}}}]);

If any other attributes (like 'Chat_group') can be null, then again a similar conditional projection would be required for it. Also note that I have used $max to group 'Chat_group' instead of $first so that it doesn't consider null values but it could lead to degraded performance.

score 0 · Answer 2 · answered Dec 19 '16 at 07:35

0

While unwinding 'All_Participants' you can use -> preserveNullAndEmptyArrays which will not unwind the null field if set to false. You can refer this link-> https://docs.mongodb.com/v3.2/reference/operator/aggregation/unwind/

answered Dec 19 '16 at 07:35

Riya Saxena

99
8

This answer is not completely correct as it can happen that the data doesn't have 'Message_id' attribute itself. Then in $group stage it would result in showing "Message_id" : Null. Therefore, we need to add a $project stage after the $group stage. – Mandeep Singh Nov 05 '19 at 17:25

MongoDB aggregation skip null value key

2 Answers2