How to make a nested relationship on mongodb?

Question

For example i have 4 collections. There are:

"company" collection

{
 "id_company": "C01"
 "company_name": "Sidomuncul"
 "like": [
  "123",
  "121"
 ]
}

"user" collection

{
  "id_user": "123",
  "name": "Astra",
  "major": "111",
  "language": [{
     "id_language": "101",
     "level": "Expert"
  }]
},
{
  "id_user": "121",
  "name": "Bibi",
  "id_major": "112",
  "language": [{
     "id_language": "102",
     "level": "Intermediate"
  }]
}

"major" collection

{
   "id_major": "111",
   "name": "IT" 
},
{
   "id_major": "112",
   "name": "Designer" 
}

"language" collection

{
   "id_language": "101",
   "name": "English" 
},
{
   "id_language": "102",
   "name": "Chinese" 
}

And when i make a route for get who are like company by id_company "C01", i want show the result relation id_user in "like" field with user collection. Example result:

{
   "id_company": C01",
   "like": [
    {
     "id_user": "123",
     "name": "Astra",
     "major": "IT",
     "language": [{
        "id_language": "English",
        "level": "Expert"
     }]
    },
    {
     "id_user": "121",
     "name": "Bibi",
     "id_major": "Designer",
     "language": [{
        "id_language": "Chinese",
        "level": "Intermediate"
     }]
     }
    ] //Close Like Field
}

Thanks before.

You need to have a foreign key in users collection referring to company collection to perform join operation. Read here https://stackoverflow.com/questions/26008555/foreign-key-mongoose. — Dheemanth Bhat, Feb 16 '21 at 15:44
After u link user and company collection perform a series of nested $lookup operation to achieve your result. For nested $lookup read here https://stackoverflow.com/questions/52712240/how-to-do-nested-lookup-search-in-mongodb. Post your attempt, will help u if stuck. — Dheemanth Bhat, Feb 16 '21 at 15:47
thanks, i have already tried. But there is still not work. When i lookup another collection, the data before from user is not showing — ASHafizullah, Feb 16 '21 at 23:07

score 0 · Answer 1 · answered Feb 16 '21 at 23:37

0

SOLVED

Use lookup and group also push

answered Feb 16 '21 at 23:37

ASHafizullah

603
1
9
22

score 0 · Answer 2 · answered Feb 17 '21 at 06:37

Using Mongo aggregate pipeline you can collect required result in multiple stages. For mongo aggregate query you can use $lookup, $set, $unwind and $group stages. Try this query, you might get required result set:

db.company.aggregate([
  {
    "$unwind": "$like"
  },
  {
    $lookup: {
      from: "user",
      localField: "like",
      foreignField: "id_user",
      as: "like"
    }
  },
  {
    "$unwind": "$like"
  },
  {
    $lookup: {
      from: "major",
      localField: "like.major",
      foreignField: "id_major",
      as: "major"
    }
  },
  {
    "$unwind": "$major"
  },
  {
    $set: {
      "like.major": "$major.name"
    }
  },
  {
    $lookup: {
      from: "language",
      localField: "like.language.id_language",
      foreignField: "id_language",
      as: "lang"
    }
  },
  {
    "$unwind": "$lang"
  },
  {
    $set: {
      "like.language.id_language": "$lang.name"
    }
  },
  {
    $group: {
      _id: "id_company",
      company_name: {
        "$first": "$company_name"
      },
      like: {
        $push: {
          id_user: "$like.id_user",
          major: "$like.major",
          name: "$like.name",
          language: "$like.language"
        }
      }
    }
  }
])

How to make a nested relationship on mongodb?

2 Answers2