CODEWITHSUNDEEP
mongodbaggregation-framework
rmacqueen
rmacqueen

Reputation: 1071

How to conditionally project fields during aggregate in mongodb

I have a user document like:

{
    _id: "s0m3Id",
    _skills: ["skill1", "skill2"],
}

Now I want to unwind this document by the _skills field and add a score for each skill. So my aggregate looks like:

{ 
  '$unwind': {'path': '$_skills', 'preserveNullAndEmptyArrays': true},
},
{
  '$project': {
    '_skills':
      'label': '$_skills',
      'skill_score': 1
    },
  }
},

Sometimes the _skills field can be empty, however in this case I still want the user document to flow through the aggregation - hence the preserveNullAndEmptyArrays parameter. However, the problem I'm having is that it will project a skill_score (though with no label) onto documents which had empty _skills array fields. Thus, when I go to $group the documents later on, those documents now have a non-empty _skills array, containing a single object, namely {skill_score: 1}. This is not what I want - I want documents which had empty (or non-existent) _skills fields to not have any skill_score projected onto them.

So how can I conditionally project a field based on the existence of another field? Using $exists does not help, because that is intended for querying, not for boolean expressions.

Upvotes: 12

Views: 20967

Answers (1)

kkkkkkk
kkkkkkk

Reputation: 7748

Updated

This aggregation will set the value of skill_score to 0 if _skills does not exist, then use $redact to remove the subdocument having skill_score equals to 0:

db.project_if.aggregate([
  {
    $unwind: {
      path: '$_skills',
      preserveNullAndEmptyArrays: true,
    }
  },
  {
    $project: {
      _skills: {
        label: '$_skills',
        skill_score: {
          $cond: {
            if: {
              $eq: ['$_skills', undefined]
            },
            then: 0,
            else: 1,
          }
        }
      }
    }
  }, 
  {
    $redact: {
      $cond: {
        if: { $eq: [ "$skill_score", 0 ] },
        then: '$$PRUNE',
        else: '$$DESCEND'
      }
    }
  }
]);

Result would be like:

[
  { "_id" : '', "_skills" : { "label" : "skill1", "skill_score" : 1 } },
  { "_id" : '', "_skills" : { "label" : "skill2", "skill_score" : 1 } },
  { "_id" : '' },
]

Upvotes: 13

Related Questions