MongoDB在对象数组内联接数据 [英] MongoDB join data inside an array of objects

问题描述

我在名为诊断的集合中有这样的文档:

I have document like this in a collection called diagnoses :

   {
        "_id" : ObjectId("582d43d18ec3f432f3260682"),
        "patientid" : ObjectId("582aacff3894c3afd7ad4677"),
        "doctorid" : ObjectId("582a80c93894c3afd7ad4675"),
        "medicalcondition" : "high fever, cough, runny nose.",
        "diagnosis" : "Viral Flu",
        "addmissiondate" : "2016-01-12",
        "dischargedate" : "2016-01-16",
        "bhtno" : "125",
        "prescription" : [ 
            {
                "drug" : ObjectId("58345e0e996d340bd8126149"),
                "instructions" : "Take 2 daily, after meals."
            }, 
            {
                "drug" : ObjectId("5836bc0b291918eb42966320"),
                "instructions" : "Take 1 daily, after meals."
            }
        ]
    }

处方对象数组中的药物 ID来自一个名为 drugs 的单独集合，请参见下面的示例文档:

The drug id inside the prescription object array is from a separate collection called drugs, see sample document below :

{
    "_id" : ObjectId("58345e0e996d340bd8126149"),
    "genericname" : "Paracetamol Tab 500mg",
    "type" : "X",
    "isbrand" : false
}

我正在尝试使用本机node.js驱动程序创建mongodb查询，以得到如下结果:

I am trying to create a mongodb query using the native node.js driver to get a result like this:

        {
                "_id" : ObjectId("582d43d18ec3f432f3260682"),
                "patientid" : ObjectId("582aacff3894c3afd7ad4677"),
                "doctorid" : ObjectId("582a80c93894c3afd7ad4675"),
                "medicalcondition" : "high fever, cough, runny nose.",
                "diagnosis" : "Viral Flu",
                "addmissiondate" : "2016-01-12",
                "dischargedate" : "2016-01-16",
                "bhtno" : "125",
                "prescription" : [ 
                    {
                        "drug" : 
                        {
                          "_id" : ObjectId("58345e0e996d340bd8126149"),
                          "genericname" : "Paracetamol Tab 500mg",
                          "type" : "X",
                          "isbrand" : false
                        },
                        "instructions" : "Take 2 daily, after meals."
                    },
                    ...
                ]
            }

非常感谢您提供有关如何获得类似结果的建议.

Any advice on how to approach a similar result like this is much appreciated, thanks.

推荐答案

使用MongoDB 3.4.4和更高版本

通过聚合框架， 运算符支持数组

With the aggregation framework, the $lookup operators supports arrays

db.diagnoses.aggregate([
    { "$addFields": { 
        "prescription": { "$ifNull" : [ "$prescription", [ ] ] }    
    } },
    { "$lookup": {
        "from": "drugs",
        "localField": "prescription.drug",
        "foreignField": "_id",
        "as": "prescription.drug"
    } }
])

对于较旧的MongoDB版本:

您可以创建一个管道，该管道首先使用 $unwind 运算符和 $unwind 操作". $group 在第一个管道中以前拼合的文档，其中 $unwind 运算符为处方数组中的每个元素输出一个文档.

You can create a pipeline that first flattens the prescription array using the $unwind operator and a $lookup subsequent pipeline step to do a "left outer join" on the "drugs" collection. Apply another $unwind operation on the created array from the "joined" field. $group the previously flattened documents from the first pipeline where there $unwind operator outputs a document for each element in the prescription array.

组装以上管道，运行以下聚合操作:

Assembling the above pipeline, run the following aggregate operation:

db.diagnoses.aggregate([
    { 
        "$project": {               
            "patientid": 1,
            "doctorid": 1,
            "medicalcondition": 1,
            "diagnosis": 1,
            "addmissiondate": 1,
            "dischargedate": 1,
            "bhtno": 1,
            "prescription": { "$ifNull" : [ "$prescription", [ ] ] } 
        }
    },
    {
       "$unwind": {
           "path": "$prescription",
           "preserveNullAndEmptyArrays": true
        }
    },      
    {
        "$lookup": {
            "from": "drugs",
            "localField": "prescription.drug",
            "foreignField": "_id",
            "as": "prescription.drug"
        }
    },
    { "$unwind": "$prescription.drug" },
    { 
        "$group": {
            "_id": "$_id",
            "patientid" : { "$first": "$patientid" },
            "doctorid" : { "$first": "$doctorid" },
            "medicalcondition" : { "$first": "$medicalcondition" },
            "diagnosis" : { "$first": "$diagnosis" },
            "addmissiondate" : { "$first": "$addmissiondate" },
            "dischargedate" : { "$first": "$dischargedate" },
            "bhtno" : { "$first": "$bhtno" },
            "prescription" : { "$push": "$prescription" }
        }
    }
])

示例输出

{
    "_id" : ObjectId("582d43d18ec3f432f3260682"),
    "patientid" : ObjectId("582aacff3894c3afd7ad4677"),
    "doctorid" : ObjectId("582a80c93894c3afd7ad4675"),
    "medicalcondition" : "high fever, cough, runny nose.",
    "diagnosis" : "Viral Flu",
    "addmissiondate" : "2016-01-12",
    "dischargedate" : "2016-01-16",
    "bhtno" : "125",
    "prescription" : [ 
        {
            "drug" : {
                "_id" : ObjectId("58345e0e996d340bd8126149"),
                "genericname" : "Paracetamol Tab 500mg",
                "type" : "X",
                "isbrand" : false
            },
            "instructions" : "Take 2 daily, after meals."
        }, 
        {
            "drug" : {
                "_id" : ObjectId("5836bc0b291918eb42966320"),
                "genericname" : "Paracetamol Tab 100mg",
                "type" : "Y",
                "isbrand" : false
            },
            "instructions" : "Take 1 daily, after meals."
        }
    ]
}

这篇关于MongoDB在对象数组内联接数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！