How to aggregate nested lookup array in mongoose?

Question

I have a problem with how to lookup nested array, for example i have 4 collections.

1. User Collection

"user": [
    {
      "_id": "1234",
      "name": "Tony",
      "language": [
        {
          "_id": "111",
          "language_id": "919",
          "level": "Expert"
        },
        {
          "_id": "111",
          "language_id": "920",
          "level": "Basic"
        }
      ]
    }
  ]

2. Language Collection

"language": [
    {
      "_id": "919",
      "name": "English"
    },
    {
      "_id": "920",
      "name": "Chinese"
    }
  ]

3. Job

"job": [
    {
      "_id": "10",
      "title": "Programmer",
      "location": "New York"
    }
  ],

4. CvSubmit Collection

"cvsubmit": [
    {
      "_id": "11",
      "id_user": "1234",
      "id_job": "11"
    }
  ]

And my query aggregation is:

db.cvsubmit.aggregate([
  {
    $lookup: {
      from: "user",
      localField: "id_user",
      foreignField: "_id",
      as: "id_user"
    }
  },
  {
    $lookup: {
      from: "language",
      localField: "id_user.language.language_id",
      foreignField: "_id",
      as: "id_user.language.language_id"
    }
  },
])

But the result is:

[
  {
    "_id": "11",
    "id_job": "11",
    "id_user": {
      "language": {
        "language_id": [
          {
            "_id": "919",
            "name": "English"
          },
          {
            "_id": "920",
            "name": "Chinese"
          }
        ]
      }
    }
  }
]

I want the result like this, also showing all user data detail like name:

[
  {
    "_id": "11",
    "id_job": "11",
    "id_user": {
      "_id": "1234",
      "name": "Tony"
      "language": [
          {
            "_id": "919",
            "name": "English",
            "Level": "Expert"
          },
          {
            "_id": "920",
            "name": "Chinese",
            "level": "Basic"
          }
        ]
    }
  }
]

Mongo Playground link https://mongoplayground.net/p/i0yCucjruey

Thanks before.

Answer 1

• $lookup with user collection
• $unwind deconstruct id_user array
• $lookup with language collection and return in languages field
• $map to iterate look of id_user.language array
• $reduce to iterate loop of languages array returned from collection, check condition if language_id match then return name

db.cvsubmit.aggregate([
  {
    $lookup: {
      from: "user",
      localField: "id_user",
      foreignField: "_id",
      as: "id_user"
    }
  },
  { $unwind: "$id_user" },
  {
    $lookup: {
      from: "language",
      localField: "id_user.language.language_id",
      foreignField: "_id",
      as: "languages"
    }
  },
  {
    $addFields: {
      languages: "$$REMOVE",
      "id_user.language": {
        $map: {
          input: "$id_user.language",
          as: "l",
          in: {
            _id: "$$l._id",
            level: "$$l.level",
            name: {
              $reduce: {
                input: "$languages",
                initialValue: "",
                in: {
                  $cond: [
                    { $eq: ["$$this._id", "$$l.language_id"] },
                    "$$this.name",
                    "$$value"
                  ]
                }
              }
            }
          }
        }
      }
    }
  }
])

Playground

You database structure is not accurate as per NoSQL, there should be max 2 collections, loot of join using $lookup and $unwind will cause performance issues.