How to push all values in single array in mongodb

Question

Colleges

/* 1 createdAt:5/9/2019, 7:00:04 PM*/
{
    "_id" : ObjectId("5cd42b5c65b41027845938ae"),
    "clgID" : "100",
    "name" : "Anna University"
},

/* 2 createdAt:5/9/2019, 7:00:04 PM*/
{
    "_id" : ObjectId("5cd42b5c65b41027845938ad"),
    "clgID" : "200",
    "name" : "National"
}

Subjects:

/* 1 createdAt:5/9/2019, 7:03:24 PM*/
{
    "_id" : ObjectId("5cd42c2465b41027845938b0"),
    "name" : "Hindi",
    "members" : {
        "student" : [
            "123"
        ]
    },
    "college" : {
        "collegeID" : "100"
    }
},

/* 2 createdAt:5/9/2019, 7:03:24 PM*/
{
    "_id" : ObjectId("5cd42c2465b41027845938af"),
    "name" : "English",
    "members" : {
        "student" : [
            "456",
            "789"
        ]
    },
    "college" : {
        "collegeID" : "100"
    }
}

Here i am having two collection and i want to join Colleges table is clgID and Subjects table iscollege.collegeID , then i want to take members.student values and push into single array based on college.collegeID.

My Expected Output

{
    "GroupDetails" : [ ],
    "clgName" : "National"
},
{
    "GroupDetails" : [
        "123",
        "456",
        "789"
    ],
    "clgName" : "Anna University"
}

My Code

db.Colleges.aggregate([
    { $match : { "clgID" : { $in :  ["100", "200"]  } } },
    { $lookup: { from: "Subjects", localField: "clgID", foreignField: "college.collegeID", as: "GroupDetails" } },
    //{ $unwind: "$GroupDetails" },
    { $project: { '_id' : false, 'clgName' : '$name', 'GroupDetails.members.student' : true } }
])

I am getting like this

/* 1 */
{
    "GroupDetails" : [ ],
    "clgName" : "National"
},

/* 2 */
{
    "GroupDetails" : [
        {
            "members" : {
                "student" : [
                    "456"
                ]
            }
        },
        {
            "members" : {
                "student" : [
                    "123"
                ]
            }
        }
    ],
    "clgName" : "Anna University"
}

Ashh · Accepted Answer · 2019-05-10 06:48:29Z

2

You can use below aggregation with mongodb 3.6 and above

db.Colleges.aggregate([
  { "$match": { "clgID": { "$in": ["100", "200"] } } },
  { "$lookup": {
    "from": "Subjects",
    "let": { "clgId": "$clgID" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$$clgId", "$college.collegeID"] } } },
      { "$group": {
        "_id": "$college.collegeID",
        "groupDetails": { "$push": "$members.student" }
      }},
      { "$project": {
        "groupDetails": {
          "$reduce": {
            "input": "$groupDetails",
            "initialValue": [],
            "in": { "$concatArrays": ["$$this", "$$value"] }
          }
        }
      }}
    ],
    "as": "clg"
  }},
  { "$unwind": { "path": "$clg", "preserveNullAndEmptyArrays": true } },
  { "$project": {
    "clgName": "$name",
    "groupDetails": { "$ifNull": ["$clg.groupDetails", []] }
  }}
])

MongoPlayground

Or with the mongodb 3.4 and below

db.Colleges.aggregate([
  { "$match": { "clgID": { "$in": ["100", "200"] }}},
  { "$lookup": {
    "from": "Subjects",
    "localField": "clgID",
    "foreignField": "college.collegeID",
    "as": "clg"
  }},
  { "$unwind": { "path": "$clg", "preserveNullAndEmptyArrays": true }},
  { "$group": {
    "_id": { "clgId": "$clg.college.collegeID", "_id": "$_id" },
    "groupDetails": { "$push": "$clg.members.student" },
    "clgName": { "$first": "$name" }
  }},
  { "$project": {
    "_id": "$_id._id",
    "clgName": 1,
    "groupDetails": {
      "$reduce": {
        "input": "$groupDetails",
        "initialValue": [],
        "in": { "$concatArrays": ["$$this", "$$value"] }
      }
    }
  }}
])

MongoPlayground

edited May 10, 2019 at 6:48

answered May 9, 2019 at 15:47

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

3 Comments

Bhuvan M Over a year ago

Kindly check this link

Bhuvan M Over a year ago

above link related to this question only, please check

Bhuvan M Over a year ago

Can you please check my question

Collectives™ on Stack Overflow

How to push all values in single array in mongodb

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related