Mongodb aggregate ifNull against array elements

Question

I have the following dataset:

{ 
    patientId: 228,
    medication: {
        atHome : [
        {
            "drug" : "tylenol", 
            "start" : "3", 
            "stop" : "7"
        }, 
        {
            "drug" : "advil", 
            "start" : "0", 
            "stop" : "2"
        }, 
        {
            "drug" : "vitaminK", 
            "start" : "0", 
            "stop" : "11"
        }
        ], 
    }
}

When I execute the following aggregate everything looks great.

db.test01.aggregate(
[
    {$match: {patientId: 228}},
    {$project: {
        patientId: 1,
        "medication.atHome.drug": 1
        }
    },
]);

Results (Exactly what I wanted):

{ 
"_id" : ObjectId("5a57b7d17af6772ebf647939"), 
"patientId" : NumberInt(228), 
"medication" : {
    "atHome" : [
        {"drug" : "tylenol"}, 
        {"drug" : "advil"}, 
        {"drug" : "vitaminK"}
    ]}
}

We then wanted to add ifNull to change nulls to a default value, but this bungled the results.

db.test01.aggregate(
[
    {$match: {patientId: 228}},
    {$project: {
         patientId: {$ifNull: ["$patientId", NumberInt(-1)]},
         "medication.atHome.drug": {$ifNull: ["$medication.atHome.drug", "Unknown"]}
        }
    },
]);

Results from ifNull (Not what I was hoping for):

{ 
"_id" : ObjectId("5a57b7d17af6772ebf647939"), 
"patientId" : NumberInt(228), 
"medication" : {
    "atHome" : [
        {"drug" : ["tylenol", "advil", "vitaminK"]}, 
        {"drug" : ["tylenol", "advil", "vitaminK"]}, 
        {"drug" : ["tylenol", "advil", "vitaminK"]}, 
    ]}
}

What am I missing or not understanding?

Depending on your use case may be transforming in $project stage may be helpful. Something like {$project:{"medication.atHome":{$map:{input: "$medication.atHome",as: "result",in:{"drug" : {$ifNull: [ "$$result.drug", "Unknown" ] },"start" : "$$result.start","stop" : "$$result.stop"}}}}} — s7vr
– s7vr, Commented Jan 13, 2018 at 7:57

Andriy Simonov · Accepted Answer · 2018-01-16 17:19:16Z

3

To set attributes of documents that are elements of an array to default values you need to $unwind the array and then to group everything up after you check the attributes for null. Here is the query:

db.test01.aggregate([
  // unwind to evaluete the array elements
  {$unwind: "$medication.atHome"},
  {$project: {
               patientId: {$ifNull: ["$patientId", -1]},
               "medication.atHome.drug": {$ifNull: ["$medication.atHome.drug", "Unknown"]}
             }
  },
  // group to put atHome documents to an array again
  {$group: {
             _id: {_id: "$_id", patientId: "$patientId"},
             "atHome": {$push: "$medication.atHome" }
           }
  },
  // project to get a document of required format
  {$project: {
               _id: "$_id._id",
               patientId: "$_id.patientId",
               "medication.atHome": "$atHome"
             }
  }
])

UPDATE: There is another more neat query to achieve the same. It uses the map operator to evaluate each array element thus does not require unwinding.

db.test01.aggregate([
  {$project:
    {
      patientId: {$ifNull: ["$patientId", -1]},
      "medication.atHome": {
                             $map: {
                                     input: "$medication.atHome",
                                     as: "e",
                                     in: { $cond: {
                                                    if: {$eq: ["$$e.drug", null]},
                                                    then: {drug: "Unknown"},
                                                    else: {drug: "$$e.drug"}
                                                  }
                                         }
                                   }
                           }
    }
  }
])

edited Jan 16, 2018 at 17:19

answered Jan 12, 2018 at 10:23

Andriy Simonov

1,3081 gold badge12 silver badges22 bronze badges

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

1 Comment

Chris Wolcott Over a year ago

Well that works, but it is ugly since the aggregate query is being built dynamically and with potentially 30+ fields. That is to bad there isn't internal logic to handle this situation. My alternate solution would be to process the results after the query.

Collectives™ on Stack Overflow

Mongodb aggregate ifNull against array elements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related