How to join deeply nested array?

Question

Here is my actual database schema. company_id is reference object of companies collection and booking_days.consultants.consultant_id is reference object of users collection.

I want to join embedded document with company_id and booking_days.consultants.consultant_id.

{
    "_id" : ObjectId("5a7040d664544e1bb877deae"),
    "company_id" : ObjectId("5a6eb43f437e6a0d9e00c92f"),
    "booking_days" : [ 
        {
            "booking_date" : ISODate("2018-01-31T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877deca"),
            "consultants" : [ 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
                    "_id" : ObjectId("5a7040d664544e1bb877decc")
                }, 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52f"),
                    "_id" : ObjectId("5a7040d664544e1bb877decb")
                }
            ]
        }, 
        {
            "booking_date" : ISODate("2018-02-01T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877dec6"),
            "consultants" : [ 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52f"),
                    "_id" : ObjectId("5a7040d664544e1bb877dec9")
                }, 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
                    "_id" : ObjectId("5a7040d664544e1bb877dec8")
                }, 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
                    "_id" : ObjectId("5a7040d664544e1bb877dec7")
                }
            ]
        }, 
        {
            "booking_date" : ISODate("2018-02-02T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877dec4"),
            "consultants" : [ 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
                    "_id" : ObjectId("5a7040d664544e1bb877dec5")
                }
            ]
        }, 

    ],
    "__v" : 0
}

I am using below query.

db.getCollection('booking_days').aggregate(
  [
     { $match: { company_id:ObjectId("5a6eb43f437e6a0d9e00c92f") } },
     { 
         $lookup: { 
                    localField: "company_id",
                    from: "companies",
                    foreignField: "_id",
                    as: "companies"
                 },

     },
      { 
         $lookup: { 
                    localField: "booking_days.consultants.consultant_id",
                    from: "users",
                    foreignField: "_id",
                    as: "userssss"
                 },

     },
      { 
        $unwind:"$companies"
      },


   ]
  )

Actual Output

{
    "_id" : ObjectId("5a7040d664544e1bb877deae"),
    "company_id" : ObjectId("5a6eb43f437e6a0d9e00c92f"),
    "booking_days" : [ 
        {
            "booking_date" : ISODate("2018-01-31T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877deca"),
            "consultants" : [ 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
                    "_id" : ObjectId("5a7040d664544e1bb877decc")
                }, 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52f"),
                    "_id" : ObjectId("5a7040d664544e1bb877decb")
                }
            ]
        }, 
        {
            "booking_date" : ISODate("2018-02-01T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877dec6"),
            "consultants" : [ 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52f"),
                    "_id" : ObjectId("5a7040d664544e1bb877dec9")
                }, 

            ]
        }, 
    ],
    "__v" : 0,
    "companies" : {
        "_id" : ObjectId("5a6eb43f437e6a0d9e00c92f"),
        "first_name" : "Adrienne Runolfsson",
    },
    "users" : [ 
        {
            "_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
            "first_name" : "Christ Hamill",

        }, 
        {
            "_id" : ObjectId("5a6f2854ce7d6938de1dd52e"),
            "first_name" : "Miss Dina Kovacek",

        }, 

    ]
}

Excepted output. consultant data will come in booking_days.consultants array.

{
    "_id" : ObjectId("5a7040d664544e1bb877deae"),
    "company_id" : ObjectId("5a6eb43f437e6a0d9e00c92f"),
    "booking_days" : [ 
        {
            "booking_date" : ISODate("2018-01-31T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877deca"),
            "consultants" : [ 
                {
                    "consultant_id" : {
                        "_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
                        "first_name" : "Christ Hamill",
                    },
                    "_id" : ObjectId("5a7040d664544e1bb877decc")
                }, 
                {
                    "consultant_id" :  {
                        "_id" : ObjectId("5a6f2854ce7d6938de1dd52e"),
                        "first_name" : "Miss Dina Kovacek",
                    },
                    "_id" : ObjectId("5a7040d664544e1bb877decb")
                }
            ]
        }, 
        {
            "booking_date" : ISODate("2018-02-01T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877dec6"),
            "consultants" : [ 
                {
                    "consultant_id" :  {
                        "_id" : ObjectId("5a6f2854ce7d6938de1dd52e"),
                        "first_name" : "Miss Dina Kovacek",
                    }, 
                    "_id" : ObjectId("5a7040d664544e1bb877dec9")
                }, 

            ]
        }, 
    ],
    "__v" : 0,
    "companies" : {
        "_id" : ObjectId("5a6eb43f437e6a0d9e00c92f"),
        "first_name" : "Adrienne Runolfsson",
    },

}

s7vr · Accepted Answer · 2018-02-01 14:17:14Z

1

As such you have to $unwind the localField when it is an embedded document array expect in some cases where localField is an array of scalar ids.

$unwind twice as consultant array is two levels deep followed by $lookup to get the name and $group to get back the expected output.

db.getCollection('booking_days').aggregate([
  {"$match":{"company_id":ObjectId("5a6eb43f437e6a0d9e00c92f")}},
  {"$lookup":{"localField":"company_id","from":"companies","foreignField":"_id","as":"companies"}},
  {"$unwind":"$companies"},
  {"$unwind":"$booking_days"},
  {"$unwind":"$consultants"},
  {"$lookup":{
    "localField":"booking_days.consultants.consultant_id",
    "from":"users",
    "foreignField":"_id",
    "as":"booking_days.consultants.consultant_id"
  }},
  {"$group":{
    "_id":{"_id":"$_id","booking_days_id":"$booking_days._id"},
    "company_id":{"$first":"$company_id"},
    "booking_date":{"$first":"$booking_days.booking_date"},
    "companies":{"$first":"$companies"},
    "consultants":{"$push":"$booking_days.consultants"}
  }},
  {"$group":{
    "_id":"$_id._id",
    "company_id":{"$first":"$company_id"},
    "companies":{"$first":"$companies"},
    "booking_days":{
      "$push":{
        "_id":"$_id.booking_days_id",
        "booking_date":"$booking_date",
        "consultants":"$consultants"
      }
    }
  }}
])

answered Feb 1, 2018 at 14:17

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

1 Comment

Nits Over a year ago

Thanks. It really help me out.

Pawan Vivekananda · Accepted Answer · 2018-08-30 18:46:47Z

{"Id": "5b87a4c79a9c3feac943fc6c",
"comments" : [ 
        {
            "likes" : [],
            "_id" : ObjectId("5b87a4c79a9c3feac943fc6c"),
            "comment" : "string",
            "accountId" : "a426d0da-ac72-4932-828e-3af99a998bc7",
            "commentId" : "7d2a05d1-2026-4a13-a5c1-318ed80d1b38",
            "reply" : [ 
                {
                    "_id" : ObjectId("5b87b61e97585ef1d0d22108"),
                    "comment" : "string",
                    "accountId" : "a426d0da-ac72-4932-828e-3af99a998bc7",
                    "replyId" : "ec220fd7-3440-44dc-9178-7a1183879463"
                },
                {
                    "_id" : ObjectId("5b87b61e97585ef1d0d22108"),
                    "comment" : "string klllll",
                    "accountId" : "a426d0da-ac72-4932-828e-3af99a998bc7",
                    "replyId" : "ec220fd7-3440-44dc-9178-7a1183879463"
                }
            ]
        }, 
        {
            "likes" : [],
            "_id" : ObjectId("5b87c301c8a07efa2599c29e"),
            "comment" : "testing",
            "accountId" : "cfd29f53-d73e-480c-9cfa-ea42b4119266",
            "commentId" : "0676047b-1712-4f70-89d5-29c1abe03eaf",
            "reply" : [
                      {
                    "_id" : ObjectId("5b87b61e97585ef1d0d22108"),
                    "comment" : "string",
                    "accountId" : "a426d0da-ac72-4932-828e-3af99a998bc7",
                    "replyId" : "ec220fd7-3440-44dc-9178-7a1183879463"
                },
                {
                    "_id" : ObjectId("5b87b61e97585ef1d0d22108"),
                    "comment" : "string klllll",
                    "accountId" : "a426d0da-ac72-4932-828e-3af99a998bc7",
                    "replyId" : "ec220fd7-3440-44dc-9178-7a1183879463"
                }
            ]
        }
    ]
}

accountId is in differnt connection

// Expected Out Put
{"Id": "5b87a4c79a9c3feac943fc6c",
"comments" : [ 
        {
            "likes" : [],
            "_id" : ObjectId("5b87a4c79a9c3feac943fc6c"),
            "comment" : "string",
            "name" : "apple",
            "reply" : [ 
                {
                    "_id" : ObjectId("5b87b61e97585ef1d0d22108"),
                    "comment" : "string",
                    "name" : "apple",
                },
                {
                    "_id" : ObjectId("5b87b61e97585ef1d0d22108"),
                    "comment" : "string klllll",
                    "name" : "apple",
                }
            ]
        }, 
        {
            "likes" : [],
            "_id" : ObjectId("5b87c301c8a07efa2599c29e"),
            "comment" : "testing",
            "name" : "ball",
            "reply" : [
                      {
                    "_id" : ObjectId("5b87b61e97585ef1d0d22108"),
                    "comment" : "string",
                    "name" : "apple",
                },
                {
                    "_id" : ObjectId("5b87b61e97585ef1d0d22108"),
                    "comment" : "string klllll",
                    "name" : "apple",                }
            ]
        }
    ]
}

Collectives™ on Stack Overflow

How to join deeply nested array?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related