Mongodb 针对数组元素聚合 ifNull

Question

我有以下数据集:

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

当我执行以下聚合时，一切看起来都很棒。

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

结果(正是我想要的):

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

然后我们想添加 ifNull 以将 null 更改为默认值，但这会使结果变得糟糕。

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

ifNull 的结果(不是我所希望的):

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

我缺少或不理解什么？

Answer 1

要将作为数组元素的文档的属性设置为默认值，您需要 $unwind数组然后到group检查 null 的属性后，一切就绪。这是查询:

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

更新:还有另一个更简洁的查询可以实现相同的目的。它使用 map因此，计算每个数组元素的运算符不需要展开。

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