How to join two table in mongodb and push into array

Question

Here i am having two collection Organizations & Groups , my requirement is i want to check oldOrgID in Organizations table and i have to take schoolCode & schoolName and push into mainData upto i had completed,

Now my question is Organizations table i have to take orgID and i have to check in Groups table otherIds.orgID, if suppose match means i have to take the name in Groups table push in to mainData

Organizations:

{
    "_id" : ObjectId("5c11efebd9cb4d35f47d6bd0"),
    "orgID" : "5b6c82462fb9ca35444d0ba2",
    "name" : "The Punjab Public School",
    "oldOrgID" : "176348"
}

Groups:

/* 1 createdAt:12/13/2018, 11:06:02 AM*/
{
    "_id" : ObjectId("5c11efc2d9cb4d35f47d6bcf"),
    "groupID" : "2",
    "name" : "8 B",
    "otherIds" : {
        "orgID" : "ORG1"
    },
    "version" : NumberInt(1)
},

/* 2 createdAt:12/13/2018, 11:05:08 AM*/
{
    "_id" : ObjectId("5c11ef8cd9cb4d35f47d6bce"),
    "groupID" : "1",
    "name" : "8 A",
    "otherIds" : {
        "orgID" : "ORG1"
    },
    "version" : NumberInt(1)
}

My Code

 var mainData = {};
var schoolCode = 176348 ;

db.Organizations.find({"oldOrgID" : schoolCode})
// .limit(1)
// .skip(15)
.forEach(function(doc){
    var OrgID = doc.orgID;
    if(mainData[OrgID] === undefined )
    {
      mainData[OrgID] = {}; // org name undefined means we are making empty object here
    }  
     mainData[OrgID]['schoolCode']  = doc.oldOrgID;
     mainData[OrgID]['schoolName']  = doc.name;

});
mainData

Getting Output

{
    "5b6c82462fb9ca35444d0ba2" : {
        "schoolCode" : "176348",
        "schoolName" : "The Punjab Public School"
    }
}

Expected Output

{
    "5b6c82462fb9ca35444d0ba2" : {
        "schoolCode" : "176348",
        "schoolName" : "The Punjab Public School",
        "group-section" : 
                    [
                            {
                                "name" : "8 B"
                            },
                            {
                                "name" : "8 A"
                            }
                    ]
    }
}

Aggregation code

    db.Organizations.aggregate([
  {
    $match: {
      oldOrgID: "176348"
    }
  },
  {
    $lookup: {
      from: "Groups",
      localField: "orgID",
      foreignField: "otherIds.orgID",
      as: "group-section"
    }
  },
  {
    $project: {
      "group-section._id": 0,
      "group-section.groupID": 0,
      "group-section.otherIds": 0,
      "group-section.version": 0
    }
  }
])

Output

    {
    "_id" : ObjectId("5c11efebd9cb4d35f47d6bd0"),
    "orgID" : "5b6c82462fb9ca35444d0ba2",
    "name" : "The Punjab Public School",
    "oldOrgID" : "176348",
    "group-section" : [ ]
}

Can anyone update my code, because i am new in mongodb

Kanagaraj Sa
– Kanagaraj Sa

2018-12-13 07:03:58 +00:00
Commented Dec 13, 2018 at 7:03 — Kanagaraj Sa
– Kanagaraj Sa, Commented Dec 13, 2018 at 7:03

Mayuri S Kulkarni · Accepted Answer · 2018-12-13 07:57:51Z

1

$lookup is one of the pipeline stage of aggregation query so you need to use the Mongo Aggregation query as:

db.collection.aggregate([
  {
    $match: {
      oldOrgID: "176348"
    }
  },
  {
    $lookup: {
      from: "other",
      localField: "orgID",
      foreignField: "otherIds.orgID",
      as: "group-section"
    }
  },
  {
    $project: {
      "group-section._id": 0,
      "group-section.groupID": 0,
      "group-section.otherIds": 0,
      "group-section.version": 0
    }
  }
])

edited Dec 13, 2018 at 7:57

answered Dec 13, 2018 at 7:21

Mayuri S Kulkarni

7575 silver badges17 bronze badges

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

10 Comments

Kanagaraj Sa Over a year ago

Organizations = orgID & Groups = otherIds.orgID

Mayuri S Kulkarni Over a year ago

I just updated the Mongo Query in my last answer. Please have a look, it might help you.

Kanagaraj Sa Over a year ago

I have used your code but not working. i am getting group-section is empty array

Kanagaraj Sa Over a year ago

I had updated my Aggregation code kindly verify and modify my code based on my expected answer

Harshal Yeole Over a year ago

Good One! @MayuriSKulkarni

|

Harshal Yeole · Accepted Answer · 2018-12-13 06:51:41Z

1

You can try Aggregate Query and use the

LOOKUP operation:

Read more: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

You can something like this:

db.Organizations.aggregate([{
   $lookup:
     {
       from: "Groups" // <collection to join>,
       localField: "oldOrgID"  // <field from the input documents>,
       foreignField: "otherIds.orgId" // <field from the documents of the "from" collection>,
       as: "group-section" <output array field>
     }
}]);

Hope this give you idea.

answered Dec 13, 2018 at 6:51

Harshal Yeole

5,0311 gold badge24 silver badges43 bronze badges

1 Comment

Harshal Yeole Over a year ago

I do not have mongo setup in current machine. But I tried all I think you put above code, it should work.

Collectives™ on Stack Overflow

How to join two table in mongodb and push into array

2 Answers 2

10 Comments

LOOKUP operation:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

LOOKUP operation:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related