0

I have 3 collections in a MongoDB instance. I need to join the 3 collections using the lookup operation and I need the collections nested within their respective parent objects.

For example, I have the following mongo tables:

db.opera.insert([
    { "tlocal" : 1, "name" : "A" },
    { "tlocal" : 2, "name" : "B"}
])


db.orders.insert([
    { "olocal" : 1, "name" : "ABC", "tlocal": 1},
    { "olocal" : 2, "name" : "DEF",  "tlocal": 1},
    { "olocal" : 3, "name" : "LMN", "tlocal": 2},
    { "olocal" : 4, "name" : "OPY",  "tlocal": 2}
])


db.olives.insert([
    { "_id" : 1, "name" : "abc", "olocal": 1},
    { "_id" : 2, "name" : "def",  "olocal": 1},
    { "_id" : 3, "name" : "xyz", "olocal": 2},
    { "_id" : 4, "name" : "opq",  "olocal": 2},
    { "_id" : 5, "name" : "lmn", "olocal": 3},
    { "_id" : 6, "name" : "ghi",  "olocal": 3},
    { "_id" : 7, "name" : "jkl", "olocal": 3},
    { "_id" : 8, "name" : "uvw",  "olocal": 4}
])

Now performing a looking on the 3 collections, like this

db.getCollection('opera').aggregate([
    {
    $lookup: {
        from: "orders",
        localField: "tlocal",    
        foreignField: "tlocal",  
        as: "orders"
    }
    },

    {
    $lookup: {
        from: "olives",
        localField: "orders.olocal",    
        foreignField: "olocal", 
        as: "olives"
    }
    },
])

This result is:

{
    "_id" : ObjectId("5ae258de2b13b1c79901defb"),
    "tlocal" : 1.0,
    "name" : "A",
    "orders" : [ 
        {
            "_id" : ObjectId("5ae258e62b13b1c79901defd"),
            "olocal" : 1.0,
            "name" : "ABC",
            "tlocal" : 1.0
        }, 
        {
            "_id" : ObjectId("5ae258e62b13b1c79901defe"),
            "olocal" : 2.0,
            "name" : "DEF",
            "tlocal" : 1.0
        }
    ],
    "olives" : [ 
        {
            "_id" : 1.0,
            "name" : "abc",
            "olocal" : 1.0
        }, 
        {
            "_id" : 2.0,
            "name" : "def",
            "olocal" : 1.0
        }, 
        {
            "_id" : 3.0,
            "name" : "xyz",
            "olocal" : 2.0
        }, 
        {
            "_id" : 4.0,
            "name" : "opq",
            "olocal" : 2.0
        }
    ]
}

{
    "_id" : ObjectId("5ae258de2b13b1c79901defc"),
    "tlocal" : 2.0,
    "name" : "B",
    "orders" : [ 
        {
            "_id" : ObjectId("5ae258e62b13b1c79901deff"),
            "olocal" : 3.0,
            "name" : "LMN",
            "tlocal" : 2.0
        }, 
        {
            "_id" : ObjectId("5ae258e62b13b1c79901df00"),
            "olocal" : 4.0,
            "name" : "OPY",
            "tlocal" : 2.0
        }
    ],
    "olives" : [ 
        {
            "_id" : 5.0,
            "name" : "lmn",
            "olocal" : 3.0
        }, 
        {
            "_id" : 6.0,
            "name" : "ghi",
            "olocal" : 3.0
        }, 
        {
            "_id" : 7.0,
            "name" : "jkl",
            "olocal" : 3.0
        }, 
        {
            "_id" : 8.0,
            "name" : "uvw",
            "olocal" : 4.0
        }
    ]
}

So my question is, is there a way I can nest/group the 'olives' objects array with their respective 'order' objects arrays within the respected opera objects? Something like this:

{
    "_id" : ObjectId("5ae258de2b13b1c79901defb"),
    "tlocal" : 1.0,
    "name" : "A",
    "orders" : [ {
        "_id" : ObjectId("5ae258e62b13b1c79901defd"),
        "olocal" : 1.0,
        "name" : "ABC",
        "tlocal" : 1.0,
        "olives" : [ 
            {
                "_id" : 1.0,
                "name" : "abc",
                "olocal" : 1.0
            }, 
            {
                "_id" : 2.0,
                "name" : "def",
                "olocal" : 1.0
            }
        ]
    },
    {
        "_id" : ObjectId("5ae258e62b13b1c79901defe"),
        "olocal" : 2.0,
        "name" : "DEF",
        "tlocal" : 1.0,
        "olives" : [ 
            {
                "_id" : 3.0,
                "name" : "xyz",
                "olocal" : 2.0
            }, 
            {
                "_id" : 4.0,
                "name" : "opq",
                "olocal" : 2.0
            }
        ]
    }
    ]
}

{
    "_id" : ObjectId("5ae258de2b13b1c79901defc"),
    "tlocal" : 2.0,
    "name" : "B",
    "orders" : [ 
        {
            "_id" : ObjectId("5ae258e62b13b1c79901deff"),
            "olocal" : 3.0,
            "name" : "LMN",
            "tlocal" : 2.0,
            "olives" : [ 
                {
                    "_id" : 5.0,
                    "name" : "lmn",
                    "olocal" : 3.0
                }, 
                {
                    "_id" : 6.0,
                    "name" : "ghi",
                    "olocal" : 3.0
                }, 
                {
                    "_id" : 7.0,
                    "name" : "jkl",
                    "olocal" : 3.0
                }
            ]
        },
        {
            "_id" : ObjectId("5ae258e62b13b1c79901df00"),
            "olocal" : 4.0,
            "name" : "OPY",
            "tlocal" : 2.0,
            "olives" : [ 
                {
                    "_id" : 8.0,
                    "name" : "uvw",
                    "olocal" : 4.0
                }
            ]
        }
    ]
}

Thank you!

Sunil Kumar
  • 151
  • 1
  • 8
  • @NeilLunn I'm new to mongo and the answer posted here : https://stackoverflow.com/questions/49953780/lookup-multiple-levels-without-unwind is really difficult to understand. Could you please modify the above query and post the answer to here, for me to understand. Thank you. – Sunil Kumar Apr 26 '18 at 23:48
  • @SunilKumar The answer linked to is pretty explicit and is the same as what you are asking right down to the three joins linking each collection. The whole point of "duplicate" is that we don't post the same answer again ( or more commonly, someone copies an existing answer ). Try to take the time to actually attempt what is explained there in order to understand it. – Neil Lunn Apr 26 '18 at 23:52
  • @NeilLunn Thank you. – Sunil Kumar Apr 27 '18 at 00:35

0 Answers0