Project values of different columns into one field

Question

{
    "_id" : ObjectId("5ae84dd87f5b72618ba7a669"),
    "main_sub" : "MATHS",
    "reporting" : [ 
        {
            "teacher" : "ABC"
        }
    ],
    "subs" : [ 
        {
            "sub" : "GEOMETRIC",
            "teacher" : "XYZ",
        }
    ]
}
{
    "_id" : ObjectId("5ae84dd87f5b72618ba7a669"),
    "main_sub" : "SOCIAL SCIENCE",
    "reporting" : [ 
        {
            "teacher" : "XYZ"
        }
    ],
    "subs" : [ 
        {
            "sub" : "CIVIL",
            "teacher" : "ABC",
        }
    ]
}

I have simplified the structure of the documents that i have. The basic structure is that I have a parent subject with an array of reporting teachers and an array of sub-subjects(each having a teacher)

I now want to extract all the subject(parent/sub-subjects) along with the condition if they are sub-subjects or not which are taught by a particular teacher.

For eg:

for teacher ABC i want the following structure:

[{'subject':'MATHS', 'is_parent':'True'}, {'subject':'CIVIL', 'is_parent':'FALSE'}]

-- What is the most efficient query possible ..? I have tried $project with $cond and $switch but in both the cases I have had to repeat the conditional statement for 'subject' and 'is_parent'

-- Is it advised to do the computation in a query or should I get the data dump and then modify the structure in the server code? AS in, I could $unwind and get a mapping of the parent subjects with each sub-subject and then do a for loop.

I have tried

db.collection.aggregate(
    {$unwind:'$reporting'},
    {$project:{
        'result':{$cond:[
            {$eq:['ABC', '$reporting.teacher']}, 
            "$main_sub", 
            "$subs.sub"]}
    }}
) 

then I realised that even if i transform the else part into another query for the sub-subjects I will have to write the exact same thing for the property of is_parent

Answer 1

You have 2 arrays, so you need to unwind both - the reporting and the subs.

After that stage each document will have at most 1 parent teacher-subj and at most 1 sub teacher-subj pairs.

You need to unwind them again to have a single teacher-subj per document, and it's where you define whether it is parent or not.

Then you can group by teacher. No need for $conds, $filters, or $facets. E.g.:

db.collection.aggregate([
    { $unwind: "$reporting" },
    { $unwind: "$subs" },
    { $project: {
        teachers: [ 
            { teacher: "$reporting.teacher", sub: "$main_sub", is_parent: true }, 
            { teacher: "$subs.teacher", sub: "$subs.sub", is_parent: false }
        ]
    } },
    { $unwind: "$teachers" },
    { $group: {
        _id: "$teachers.teacher",
        subs: { $push: {
            subject: "$teachers.sub", 
            is_parent: "$teachers.is_parent"
        } }
    } }
])