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: stackoverflow.com/questions/33123396/… — Hedegare
– Hedegare, Commented Dec 18, 2016 at 21:01

Mandeep Singh · Accepted Answer · 2019-11-05 17:21:29Z

1

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.

edited Nov 5, 2019 at 17:21

answered Nov 5, 2019 at 17:12

Mandeep Singh

3653 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Riya Saxena · Accepted Answer · 2016-12-19 07:35:23Z

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, 2016 at 7:35

Riya Saxena

998 bronze badges

1 Comment

Mandeep Singh Over a year ago

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.

Collectives™ on Stack Overflow

MongoDB aggregation skip null value key

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related