Mongodb Join having nested arrays and objects

Question

If I have a data like that in collection 1, Like nested array and object and then I have another collectoin with objects. How can I join these two collections and get the output as complete object with id,full name, its manager and depeartment for each seperate id. I have tried with unwind but i can't get to each single nested manager name and Id as i think if I can't get each single one of it, how can I join it with other collection id. How can I join these? Also, is this even possible?

--Collection 1 {
        "ïd": 1,
        "name": "Max ",
        "managers": [{
                "ïd": 2,
                "name": "Caroline "
            },
            {
                "ïd": 3,
                "name": "Cecilia ",
                "managers": [{
                        "ïd": 4,
                        "name": "Geraldine ",
                        "managers": [{
                                "ïd": 5,
                                "name": "Ingrid "
                            },
                            {
                                "ïd": 6,
                                "name": "Jacqueline "
                            }
                        ]
                    },
                    {
                        "ïd": 7,
                        "name": "Johannes "
                    }
                ]
            }
        ]
    }

    --Collection 2
        [{
            "ïd": 1,
            "fullName": "Max Born",
            "department": "Management"
        }, {
            "ïd": 2,
            "fullName": "Caroline Herschel",
            "department": "Management"
        }, {
            "ïd": 3,
            "fullName": "Cecilia Payne-Gaposchkin",
            "department": "Management"
        }, {
            "ïd": 4,
            "fullName": "Geraldine Seydoux",
            "department": "Sales"
        }, {
            "ïd": 5,
            "fullName": "Ingrid Daubechies",
            "department": "Sales"
        }, {
            "ïd": 6,
            "fullName": "Jacqueline K. Barton",
            "department": "Sales"
        }, {
            "ïd": 7,
            "fullName": "Johannes Kepler",
            "department": "Application Development"
        }]

My expected output is like

{
            "ïd": 1,
            "name": "Max ",
            "fullName": "Max Born",
            "department": "Management"
},
{            "ïd": 2,
             "name": "Caroline ",
             "fullName": "Caroline Herschel",
             "department": "Management"
}
..... and so on

ray · Accepted Answer · 2024-04-13 18:09:59Z

Here is the working code for your reference.

The basic idea is:

use $lookup to find fullName and department
use $zip to combine the original managers array and the looked up array
"flatten" the combined result
repeat steps 1-3 for different layers

However, despite being possible, the query is quite complex and I consider it being over-engineering. I would suggest you refactor your collection structure for easier processing.

-- edit

db.person.aggregate([
  {
    "$lookup": {
      "from": "employee",
      "localField": "managers.managers.managers._id",
      "foreignField": "_id",
      "as": "lookup"
    }
  },
  {
    "$addFields": {
      "final": {
        "$zip": {
          inputs: [
            {
              $first: {
                $first: "$managers.managers.managers"
              }
            },
            "$lookup"
          ]
        }
      }
    }
  },
  {
    "$addFields": {
      "final": {
        "$reduce": {
          "input": "$final",
          "initialValue": [],
          "in": {
            "$concatArrays": [
              "$$value",
              [
                {
                  "_id": {
                    "$first": "$$this._id"
                  },
                  "name": {
                    "$last": "$$this.name"
                  },
                  "department": {
                    "$last": "$$this.department"
                  },
                  "fullName": {
                    "$last": "$$this.fullName"
                  }
                }
              ]
            ]
          }
        }
      }
    }
  },
  {
    "$lookup": {
      "from": "employee",
      "localField": "managers.managers._id",
      "foreignField": "_id",
      "as": "lookup"
    }
  },
  {
    "$addFields": {
      "final2": {
        "$zip": {
          inputs: [
            {
              $first: "$managers.managers"
            },
            "$lookup"
          ]
        }
      }
    }
  },
  {
    "$addFields": {
      "final": {
        "$reduce": {
          "input": "$final2",
          "initialValue": "$final",
          "in": {
            "$concatArrays": [
              "$$value",
              [
                {
                  "_id": {
                    "$first": "$$this._id"
                  },
                  "name": {
                    "$last": "$$this.name"
                  },
                  "department": {
                    "$last": "$$this.department"
                  },
                  "fullName": {
                    "$last": "$$this.fullName"
                  }
                }
              ]
            ]
          }
        }
      }
    }
  },
  {
    "$lookup": {
      "from": "employee",
      "localField": "managers._id",
      "foreignField": "_id",
      "as": "lookup"
    }
  },
  {
    "$addFields": {
      "final2": {
        "$zip": {
          inputs: [
            "$managers",
            "$lookup"
          ]
        }
      }
    }
  },
  {
    "$addFields": {
      "managers": {
        "$reduce": {
          "input": "$final2",
          "initialValue": "$final",
          "in": {
            "$concatArrays": [
              "$$value",
              [
                {
                  "_id": {
                    "$first": "$$this._id"
                  },
                  "name": {
                    "$last": "$$this.name"
                  },
                  "department": {
                    "$last": "$$this.department"
                  },
                  "fullName": {
                    "$last": "$$this.fullName"
                  }
                }
              ]
            ]
          }
        }
      }
    }
  },
  {
    "$lookup": {
      "from": "employee",
      "localField": "_id",
      "foreignField": "_id",
      "as": "lookup"
    }
  },
  {
    "$addFields": {
      "final2": {
        "$zip": {
          inputs: [
            [
              "$$ROOT"
            ],
            "$lookup"
          ]
        }
      }
    }
  },
  {
    "$project": {
      "final": {
        "$reduce": {
          "input": "$final2",
          "initialValue": "$managers",
          "in": {
            "$concatArrays": [
              "$$value",
              [
                {
                  "_id": {
                    "$first": "$$this._id"
                  },
                  "name": {
                    "$last": "$$this.name"
                  },
                  "department": {
                    "$last": "$$this.department"
                  },
                  "fullName": {
                    "$last": "$$this.fullName"
                  }
                }
              ]
            ]
          }
        }
      }
    }
  }
])

New Mongo playgound as required by OP to flatten the result.

Your working is quite helpful. you are quite close. I have edited my post with my expected output maybe it help in understanding the use case.
Added another playground link to flatten the result as requested by you. I would like to restate the importance of remodel/restructure your collection.

Collectives™ on Stack Overflow

Mongodb Join having nested arrays and objects

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related