$lookup aggregation

Working with Data
1

I have three collection and every collection has relation between them.So when I get one collection data I expect it will give all related data.
Example:

organization collection:{
orgId:kjdk-khsk,
orgDetails:"all details will go here"
}

operation collection:{
operationId:kieush-jhsih,
orgId:kjdk-khsk,
categoryId:111111
}

category collection:{
categoryId:111111,
categoryDetails:"category details will go here"
}

so I want to join this three collection data like this :

{
orgId:kjdk-khsk,
orgDetails:"details will go here",
operations:[{
operationId:jksjkskj,
category:[{categoryId:"",categoryDetails:""}]
}]
}

I want this in a single aggregation. Is this possible in MongoDB?

2

Please update your documents in such a way that we can cut-n-paste them directly into our setup. We cannot use documents with things like:

Please use valid JSON values.

3

Here you go:

organization: {
    "name": "Popular Diagnostic",
    "status": "active",
    "username": "popular",
    "orgId": "6b40d-1cfd-459f-b086",
    "tagline": "To serve is our ultimate goal",
    "email": "hello@popular.com"
  }

 operation:  {
    "categoryId": "d1e01-8398-4b61-9363",
    "operationName": "Acute Appendicitis\t",
    "price": "15000",
    "orgId": "6b40d-1cfd-459f-b086",
    "serial": 2,
    "uid": "ee223-17f3-404c-84a7"
  }

category : {
    "orgId": "6b40d-1cfd-459f-b086",
    "categoryName": "Appendicectomy",
    "serial": 2,
    "uid": "d1e01-8398-4b61-9363"
  }
4

The first step is to do the $lookup to get all the information.

lookup_operations = { "$lookup" : {
    "from" : "operation" ,
    "localField" : "orgId" ,
    "foreignField" : "orgId" ,
    "as" : "operations" ,
    "pipeline" : [ { "$project" : {
        "categoryId" : 1 ,
        "operationName" : 1 , /* In your original post you shared a redacted document
                                 with a field operationId but not such field exists in your
                                 real documents. The field operationName was the one
                                 that is the closest.
                              */
    } } ]
} }

This lookup will forward the following document from your sample documents to the next stage.

{
    "name": "Popular Diagnostic",
    "status": "active",
    "username": "popular",
    "orgId": "6b40d-1cfd-459f-b086",
    "tagline": "To serve is our ultimate goal",
    "email": "hello@popular.com" ,
    "operations" : [ 
        {
            "categoryId": "d1e01-8398-4b61-9363",
            "operationName": "Acute Appendicitis\t" 
        }
    ]
}

The next stage is a $lookup in category collection. Note that we do not need to $unwind.

lookup_categories = { "$lookup" : {
    "from" : "category" ,
    "localField" : "operations.categoryId" ,
    "foreignField" : "uid" ,
    "as" : "categories"
    /* You have no field named categoryDetails in your real category collection document,
       so I assume you want it all.
   */
} }

This 2nd lookup will produce document like:

{
    "name": "Popular Diagnostic",
    "status": "active",
    "username": "popular",
    "orgId": "6b40d-1cfd-459f-b086",
    "tagline": "To serve is our ultimate goal",
    "email": "hello@popular.com" ,
    "operations" : [ 
        {
            "categoryId": "d1e01-8398-4b61-9363",
            "operationName": "Acute Appendicitis\t" 
        }
    ] ,
    "categories" : [
        {
            "orgId": "6b40d-1cfd-459f-b086",
            "categoryName": "Appendicectomy",
            "serial": 2,
            "uid": "d1e01-8398-4b61-9363"
        }
    ]
}

Personally, I stop here as I have all the information. I feel the application layer can deal with the cosmetic of putting the category information with the operation information.

But you could do it on the aggregation pipeline with a $set stage that uses $map on operations to $mergeObjects $$this and the result of $reduce on categories to find the element with the corresponding uid.

2 Likes
5

@Moyen_Islam,

In your original post, the example result you provided looked like this…

{
    orgId:kjdk-khsk,
    orgDetails:"details will go here",
    operations:[{
        operationId:jksjkskj,
        category:[{
            categoryId:"",
            categoryDetails:""
        }]
    }]
}

…implying that the relationships are Organization → Operation → Category.

However, the example data you provided at @steevej’s request makes it appear that the relationships are actually Organization → Category → Operation. This is based on the following analysis.

  • The key of the Organization document, organization.orgId, appears in the Category document (i.e., category.orgId)
  • The key of the Category document, category.uid, appears in the Operation document (i.e., operation.categoryId)

The solution offered below structures the documents in the result in this way in order to limit the complexity of the pipeline.

// Requires official MongoShell 3.6+
db = db.getSiblingDB("mongo_forums");
db.getCollection("organization").aggregate([{
    "$lookup": { 
        "from": "category",
        "as": "categories",
        "let": { "req_orgId": "$orgId" },
        "pipeline": [{
            "$match": { "$expr": { "$eq": [ "$orgId", "$$req_orgId" ] } }
        }, {
            "$lookup": {
                "from": "operation",
                "as": "operations",
                "localField": "uid",
                "foreignField": "categoryId"
            }
        }]
    }
}],
{
    "allowDiskUse": false
});

NOTES

  • Organization. The aggregation query starts from the organization collection. If filtering of the organization documents is needed, a $match stage can be added before the initial $lookup.
  • Category. The initial $lookup stage uses advanced syntax that uses a pipeline to retrieve the Category(ies) related to the Organization. The pipeline is, in fact, another aggregation query. The $match stage filters the Category(ies) to match the Organization on the orgId. The result will be a property, category in each organization document that is an array containing one object for each matching Category.
  • Operation. The $lookup stage in the second aggregation query uses the simplified syntax to retrieve the Operation(s) related to the Category using the categoryId. The result will be a property, operation, in each category document (in the category array) that is an array containing one object for each matching Operation.

Example Result

{
    "_id" : ObjectId("63c7eb12325f5cc4223e812e"),
    "name" : "Popular Diagnostic",
    "status" : "active",
    "username" : "popular",
    "orgId" : "6b40d-1cfd-459f-b086",
    "tagline" : "To serve is our ultimate goal",
    "email" : "hello@popular.com",
    "categories" : [
        {
            "_id" : ObjectId("63c7eb12325f5cc4223e8130"),
            "orgId" : "6b40d-1cfd-459f-b086",
            "categoryName" : "Appendicectomy",
            "serial" : NumberInt(2),
            "uid" : "d1e01-8398-4b61-9363",
            "operations" : [
                {
                    "_id" : ObjectId("63c7eb12325f5cc4223e812f"),
                    "categoryId" : "d1e01-8398-4b61-9363",
                    "operationName" : "Acute Appendicitis\t",
                    "price" : "15000",
                    "orgId" : "6b40d-1cfd-459f-b086",
                    "serial" : NumberInt(2),
                    "uid" : "ee223-17f3-404c-84a7"
                }
            ]
        }
    ]
}
3 Likes
6

Nice and clean.

This was your first post. I hope it will not be the last.

2 Likes
7

Thanks, @steevej !

I’m not one to post often – I’ll never be a top contributor by volume – but sometimes a question motivates me to answer based on my own experience. I’ve been working a lot with aggregation queries over the last few months and this question hit the sweet-spot of my recent experience.

1 Like
8

Thanks a lot my dear @Rick_Culpepper
I got this and it’s working for me as I wanted.I realy appreciate you.
God bless you.

1 Like
9

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.