CODEWITHSUNDEEP
mongodb
Chris Wolcott
Chris Wolcott

Reputation: 402

Mongodb aggregate ifNull against array elements

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?

Upvotes: 3

Views: 10744

Answers (1)

Andriy Simonov
Andriy Simonov

Reputation: 1288

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"}
                                                  }
                                         }
                                   }
                           }
    }
  }
])

Upvotes: 3

Related Questions