MongoDB join data inside an array of objects

Question

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

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
}

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.

chridam · Accepted Answer · 2021-01-05 13:02:11Z

31

Using MongoDB 3.4.4 and newer

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": "drugs"
    } },
    { "$addFields": {
        "prescription": {
            "$map": {
                "input": "$prescription",
                "in": {
                    "$mergeObjects": [
                        "$$this",
                        { "drug": {
                            "$arrayElemAt": [
                                "$drugs",
                                { 
                                    "$indexOfArray": [
                                        "$drugs._id",
                                        "$$this.drug"
                                    ] 
                                }
                            ]
                        } }
                    ]
                }
            }
        }
    } },
    { "$project": { "drugs": 0 } }
])

For older MongoDB versions:

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

Sample Output

{
    "_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."
        }
    ]
}

edited Jan 5, 2021 at 13:02

answered Dec 6, 2016 at 10:11

chridam

104k26 gold badges246 silver badges243 bronze badges

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

7 Comments

VindulaF Over a year ago

I came across a small issue with this query, in case there is no prescription data meaning the array is not there in the document, the query wont return anything, is there some way to make this work like a left join?

chridam Over a year ago

@VindulaFernando Updated answer with an approach. Please check.

VindulaF Over a year ago

Still getting the same result my diagnosis document looks like this in that scenario ` { "_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" }`

VindulaF Over a year ago

any reason that you can see ?

Andy Lorenz Over a year ago

I have followed this answer for 3.4.4+ and applied it to my own collections/fields. And I'm finding that my equivalent of the "prescription" array only contains the 'drug' object after the $lookup (prescription.drug) - the original contents of the array (i.e. the 'instructions' string) are gone. I think there is a modification required to this answer to produce the sample output as shown ?

|

Daniel Lee · Accepted Answer · 2020-11-17 10:06:37Z

In MongoDB 3.6 or later versions

It seems that $lookup will overwrite the original array instead of merging it. A working solution (a workaround, if you prefer) is to create a different field, and then merge two fields, as shown below:

db.diagnoses.aggregate([
    { "$lookup": {
        "from": "drugs",
        "localField": "prescription.drug",
        "foreignField": "_id",
        "as": "prescription_drug_info"
    } },
    { "$addFields": {
        "merged_drug_info": {
            "$map": {
                "input": "$prescription",
                "in": {
                    "$mergeObjects": [
                        "$$this",
                        { "$arrayElemAt": [
                            "$prescription_drug_info._id",
                            "$$this._id"
                        ] }
                    ] 
                }
            }
        }
    } }
])

This would add two more fields and the name of the desired field will be merged_drug_info. We can then add $project stage to filter out excessive fields and $set stage to rename the field:

...
{ "$set": { "prescription": "$merged_drug_info" } },
{ "$project": { "prescription_drug_info": 0, "merged_drug_info": 0 } }
...

Collectives™ on Stack Overflow

MongoDB join data inside an array of objects

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related