Mongodb - aggregation $push if conditional

Question

I am trying to aggregate a batch of documents. There are two fields in the documents I would like to $push. However, lets say they are "_id" and "A" fields, I only want $push "_id" and "A" if "A" is $gt 0.

I tried two approaches.

First one.

db.collection.aggregate([{
"$group":{
    "field": {
        "$push": {
            "$cond":[
                {"$gt":["$A", 0]},
                {"id": "$_id", "A":"$A"},
                null
            ]
        }
    },
    "secondField":{"$push":"$B"}
}])

But this will push a null value to "field" and I don't want it.

Second one.

db.collection.aggregate([{
"$group":
    "field": {
        "$cond":[
            {"$gt",["$A", 0]},
            {"$push": {"id":"$_id", "A":"$A"}},
            null
        ]
    },
    "secondField":{"$push":"$B"}
}])

The second one simply doesn't work...

Is there a way to skip the $push in else case?

ADDED:

Expected documents:

{
    "_id":objectid(1),
    "A":2,
    "B":"One"
},
{
    "_id":objectid(2),
    "A":3,
    "B":"Two"
},
{
    "_id":objectid(3),
    "B":"Three"
}

Expected Output:

{
    "field":[
        {
            "A":"2",
            "_id":objectid(1)
        },
        {
            "A":"3",
            "_id":objectid(2)
        },
    ],
    "secondField":["One", "Two", "Three"]
}

I think your approach cannot work https://docs.mongodb.com/manual/reference/operator/aggregation/push/ > $push is only available in the $group stage. — libik, Apr 06 '18 at 06:58

Elad Amsalem · Answer 1 · 2020-07-29T11:43:21.603

51

You can use "$$REMOVE":

This system variable was added in version 3.6 (mongodb docs)

db.collection.aggregate([{
   $group:{
       field: {
          $push: {
              $cond:[
                { $gt: ["$A", 0] },
                { id: "$_id", A:"$A" },
                "$$REMOVE"
            ]
          }
        },
        secondField:{ $push: "$B" }
    }
])

In this way you don't have to filter nulls.

edited Jul 29 '20 at 11:43

answered Feb 06 '20 at 13:49

Elad Amsalem

1,490
1
12
14

1

$$REMOVE is a system variable, not operator – Nate Scarlet May 13 '20 at 02:57
1

Thanks, this approach save 1 additional stage of $project, just to remove null – Nikhil Anand Aug 21 '22 at 20:35

Artorias · Accepted Answer · 2019-12-02T06:22:45.360

24

This is my answer to the question after reading the post suggested by @Veeram

db.collection.aggregate([{
"$group":{
    "field": {
        "$push": {
            "$cond":[
                {"$gt":["$A", 0]},
                {"id": "$_id", "A":"$A"},
                null
            ]
        }
    },
    "secondField":{"$push":"$B"}
},
{
    "$project": {
        "A":{"$setDifference":["$A", [null]]},
        "B":"$B"
    }
}])

edited Dec 02 '19 at 06:22

answered Apr 06 '18 at 15:27

Artorias

1,004
1
12
22

There is a typo in setDifference here. – Lucas Manzke Nov 27 '19 at 17:24
Hi there, found this while googling, how would you convert the `setDifference` arguments to the `mgo` library? Which type? – Andrea Richiardi Sep 11 '20 at 16:42

score 9 · Answer 3 · answered Apr 06 '18 at 14:55

One more option is to use $filter operator:

db.collection.aggregate([
{ 
    $group : {
        _id: null,
        field: { $push: { id: "$_id", A : "$A"}},
        secondField:{ $push: "$B" }
    }
},
{
    $project: {
        field: {
            $filter: {
                input: "$field",
                as: "item",
                cond: { $gt: [ "$$item.A", 0 ] }
            }
        },
        secondField: "$secondField"
    }       
}])

On first step you combine your array and filter them on second step

score 1 · Answer 4 · answered Aug 24 '22 at 17:19

 $group: {
        _id: '$_id',
        tasks: {
          $addToSet: {
            $cond: {
              if: {
                $eq: [
                  {
                    $ifNull: ['$tasks.id', ''],
                  },
                  '',
                ],
              },
              then: '$$REMOVE',
              else: {
                id: '$tasks.id',
                description: '$tasks.description',
                assignee: {
                  $cond: {
                    if: {
                      $eq: [
                        {
                          $ifNull: ['$tasks.assignee._id', ''],
                        },
                        '',
                      ],
                    },
                    then: undefined,
                    else: {
                      id: '$tasks.assignee._id',
                      name: '$tasks.assignee.name',
                      thumbnail: '$tasks.assignee.thumbnail',
                      status: '$tasks.assignee.status',
                    },
                  },
                },
              },
            },
          },
        },
     }

Mongodb - aggregation $push if conditional

4 Answers4

Linked