Combine array from embedded document based on condition in MongoDB

Question

I have a collection of student details like below:

  {
    "Student_id": 1,
    "StudentName": "ABC",
    "TestDetails": [{
            "SubtestName":"Reading", "TestSeq":1, "SubTestDetails":1, 
            "Scores":[{"ScoreType":"YY","ScoreValue":"100"},{"ScoreType":"XX","ScoreValue":"100"},
            {"ScoreType": "ZZ","ScoreValue":"100"}]}]
  ,
    "TestDetails": [{
            "SubtestName":"Writing", "TestSeq":1, "SubTestDetails":2, 
            "Scores":[{"ScoreType":"YY","ScoreValue":"200"},{"ScoreType":"XX","ScoreValue":"200"},
            {"ScoreType": "ZZ","ScoreValue":"200"}]}]
  ,
    "TestDetails": [{
            "SubtestName":"Listning", "TestSeq":2, "SubTestDetails":3, 
            "Scores":[{"ScoreType":"YY","ScoreValue":"300"},{"ScoreType":"XX","ScoreValue":"300"},
            {"ScoreType": "ZZ","ScoreValue":"300"}]}]
  ,
    "TestDetails": [{
            "SubtestName":"Speaking", "TestSeq":2, "SubTestDetails":4, 
            "Scores":[{"ScoreType":"YY","ScoreValue":"400"},{"ScoreType":"XX","ScoreValue":"400"},
            {"ScoreType": "ZZ","ScoreValue":"400"}]}]
  ,
    "TestDetails": [{
            "SubtestName":"Smartness", "TestSeq":3, "SubTestDetails":5, 
            "Scores":[{"ScoreType":"YY","ScoreValue":"500"},{"ScoreType":"XX","ScoreValue":"500"},
            {"ScoreType": "ZZ","ScoreValue":"500"}]}]
  },

  {
    "Student_id": 2,
    "StudentName": "XYZ",
    "TestDetails": [{
            "SubtestName":"Smartness", "TestSeq":1, "SubTestDetails":1, 
            "Scores":[{"ScoreType":"YY","ScoreValue":"100"},{"ScoreType":"XX","ScoreValue":"100"},
            {"ScoreType": "ZZ","ScoreValue":"100"}]}]
  ,
    "TestDetails": [{
            "SubtestName":"Writing", "TestSeq":1, "SubTestDetails":2, 
            "Scores":[{"ScoreType":"YY","ScoreValue":"200"},{"ScoreType":"XX","ScoreValue":"200"},
            {"ScoreType": "ZZ","ScoreValue":"200"}]}]
  ,
    "TestDetails": [{
            "SubtestName":"Listning", "TestSeq":2, "SubTestDetails":3, 
            "Scores":[{"ScoreType":"YY","ScoreValue":"300"},{"ScoreType":"XX","ScoreValue":"300"},
            {"ScoreType": "ZZ","ScoreValue":"300"}]}]
  ,
    "TestDetails": [{
            "SubtestName":"Speaking", "TestSeq":2, "SubTestDetails":4, 
            "Scores":[{"ScoreType":"YY","ScoreValue":"400"},{"ScoreType":"XX","ScoreValue":"400"},
            {"ScoreType": "ZZ","ScoreValue":"400"}]}]
  ,
    "TestDetails": [{
            "SubtestName":"Reading", "TestSeq":3, "SubTestDetails":5, 
            "Scores":[{"ScoreType":"YY","ScoreValue":"100"},{"ScoreType":"XX","ScoreValue":"100"},
            {"ScoreType": "ZZ","ScoreValue":"1000"}]}]
  }, 
  .
  .
  .
)

How can I create aggregate query to generate document like below:

{Student:1, "TestSeq" : 1, [{Subtest_name: Reading},{Subtest_name: Writing}]},
{Student:1,"TestSeq" :  2, [{Subtest_name: Listning},{Subtest_name: Speaking}]},
{Student:1, "TestSeq" : 3, [{Subtest_name: Smartness}]},
{Student:2, "TestSeq" : 1, [{Subtest_name: Smartness},{Subtest_name: Writing}]},
{Student:2, "TestSeq" : 2, [{Subtest_name: Listning},{Subtest_name: Speaking}]},
{Student:2, "TestSeq" : 3, [{Subtest_name: Reading}]},
{Student:3, "TestSeq" : 1, [{Subtest_name: Subtest1},{Subtest_name: Subtest2}]},
{Student:3, "TestSeq" : 2, [{Subtest_name: Subtest3},{Subtest_name: Subtest4}]},
{Student:3, "TestSeq" : 3, [{Subtest_name: Subtest5}]}

Logic is to combine/group Subtest name based on TestSeq values. For example Subtest names are combined for TestSeq = 1, for value 2 it's in 2nd row and 3 for last Subtest name for each student.

How can I implement that?

I have tried as below -

db.students.aggregate([ 
{$unwind: "$SubtestAttribs"},
{ $project: { student_name: 1, student_id : 1,
 print_ready : "$SubtestAttribs.TestSeq",
 Subtest_names :$SubtestAttribs.SubtestName" } } ])

But I am unable to form array based on condition. Above snippet giving data for each test seq. But how to combine two sub test name based on test seq?

Can you show us what you have tried? We're not really in the habbit of just writing your code for you. — David says Reinstate Monica
– David says Reinstate Monica, Commented Nov 25, 2015 at 14:57
db.students.aggregate([ {$unwind: "$SubtestAttribs"}, { $project: { student_name: 1, student_id : 1, print_ready : "$SubtestAttribs.TestSeq", Subtest_names : "$SubtestAttribs.SubtestName" } } ]) I am unable to form array based on condition. above snippet giving data for each test seq. But how to combine two sub test name based on test seq? — Iman M
– Iman M, Commented Nov 25, 2015 at 15:08
I noticed also that your documents have the key TestDetails multiple times in a single document. Thats not really legal JSON since the keys are not unique... are you sure thats what your schema really looks like? I think its suppose to be a single key TestDetails that is an array of all the TestDetails you currently have in one. — David says Reinstate Monica
– David says Reinstate Monica, Commented Nov 25, 2015 at 15:19
Also, the output you want isn't legal JSON either: the array (ie [{Subtest_name: Subtest5}]) is missing a key. — David says Reinstate Monica
– David says Reinstate Monica, Commented Nov 25, 2015 at 15:22

David says Reinstate Monica · Accepted Answer · 2015-11-25 15:33:00Z

1

Note: I'm making a couple assumptions because your question has some illegal JSON in it. Let me know if I guessed wrong. Also, I'm not on a computer with Mongo right now, so I might have some syntax issues.

db.students.aggregate([
{ $unwind: "$TestDetails" },
{
    $group:{
        _id: { Student: "$Student_id", TestSeq: "$TestDetails.TestSeq},
        Subtest_names: { $addToSet: "$TestDetails.Subtestname" }
    }
},
{
    $project:{
        Student: "$_id.Student",
        TestSeq: "$_id.TestSeq,
        Subtest_names: "$Subtest_names"
    }
}
])

answered Nov 25, 2015 at 15:33

David says Reinstate Monica

20.2k22 gold badges84 silver badges124 bronze badges

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

7 Comments

chridam Over a year ago

Would give it to you since $addToSet is more appropriate here than $push, so +1 from me even though the OP's JSON is invalid :)

Iman M Over a year ago

Appreciated your help. I will check and let you know.

Iman M Over a year ago

I am getting below o/p - { "_id" : { "Student" : 2, "TestSeq" : 3 }, "Subtest_names" : [ "Reading" ], "Student" : 2, "TestSeq" : 3 } { "_id" : { "Student" : 1, "TestSeq" : 3 }, "Subtest_names" : [ "Smartness" ], "Student" : 1, "TestSeq" : 3 } ..... Here I am getting one data for each student, while I need to find list of sub test names based on each test seq.

chridam Over a year ago

Change the line Subtest_names: { $addToSet: "$TestDetails.Subtestname" } to "details": { "$addToSet": { "Subtest_name": "$TestDetails.SubtestName" } } for it to work.

Iman M Over a year ago

Thanks. It formatted the o/p. But how can I get multiple rows for same student depending on TestSeq? For exapml : {Student:1, "TestSeq" : 1, [{Subtest_name: Reading},{Subtest_name: Writing}]}, {Student:1,"TestSeq" : 2, [{Subtest_name: Listning},{Subtest_name: Speaking}]}, {Student:1, "TestSeq" : 3, [{Subtest_name: Smartness}]}, {Student:2, "TestSeq" : 1, [{Subtest_name: Smartness},{Subtest_name: Writing}]}, {Student:2, "TestSeq" : 2, [{Subtest_name: Listning},{Subtest_name: Speaking}]}, {Student:2, "TestSeq" : 3, [{Subtest_name: Reading}]},

|

Collectives™ on Stack Overflow

Combine array from embedded document based on condition in MongoDB

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related