How to make a mongodb query with nested arrays-CodePudding

I have a mongo db collection in which each document has two nested arrays (each document has an element that stores an array which stores documents that store another array) and I only want some fields of the deepest document. I have tried different types of projection elements but none of them worked. Right now I am trying the following:

let answered_exam = await exams_table.findOne(
{_id: new ObjectId(req.body.course_id)}, 
{projection: { 
  _id: 1, 
  "exams": {
    "$map": {
      "input": "$exams",
      "in": { 
        "$this.exam_name": req.body.exam_name,
        "students_exams": {  
          "$map": {
            "input": "$$this.students_exams",
            "in": { "student_email": req.body.student_email },
        }},
      },
    }}
  } 
});

The format of an entry in the database is the following: The block of attributes in the middle are the documents stored in the exams array (each element is like this). The block from below is the format of the documents.

CodePudding user response：

Query1
(unwind)

you can do a nested map, and filter but here we select 1 exam only so data i guess are small, and we can use double unwind for simplicity
match _id
unwind exams
match exam_name
unwind student_exams
match email
project to keep only the professor_notes and the mark
if you want only 1 result max, add also in the end one more stage
{"limit" : 1}

Test code here

aggregate(
[{"$match": {"$expr": {"$eq": ["$_id", 1]}}},
  {"$unwind": {"path": "$exams"}},
  {"$match": {"$expr": {"$eq": ["$exams.exam_name", "exam1"]}}},
  {"$unwind": {"path": "$exams.student_exams"}},
  {"$match": 
    {"$expr": {"$eq": ["$exams.student_exams.student_email", "email1"]}}},
  {"$project": 
    {"_id": 0,
      "professor_notes": "$exams.student_exams.professor_notes",
      "mark": "$exams.student_exams.mark"}}])

Query2
(nested map/filters)

map if not match null else map again doing the same
on return filter to remove the nulls
get the first , of the first and replace root

*query is big, it does the same thing, map instead of unwind, and filter instead of match (2 map/2 unwind , 2 filter/2 match)

Test code here

aggregate(
[{"$match": {"$expr": {"$eq": ["$_id", 1]}}},
  {"$replaceRoot": 
    {"newRoot": 
      {"$arrayElemAt": 
        [{"$arrayElemAt": 
            [{"$filter": 
                {"input": 
                  {"$map": 
                    {"input": "$exams",
                      "in": 
                      {"$cond": 
                        [{"$not": [{"$eq": ["$$exam.exam_name", "exam1"]}]}, null,
                          {"$filter": 
                            {"input": 
                              {"$map": 
                                {"input": "$$exam.student_exams",
                                  "in": 
                                  {"$cond": 
                                    [{"$not": 
                                        [{"$eq": 
                                            ["$$students_exam.student_email", "email1"]}]},
                                      null,
                                      {"professor_notes": 
                                        "$$students_exam.professor_notes",
                                        "mark": "$$students_exam.mark"}]},
                                  "as": "students_exam"}},
                              "cond": {"$ne": ["$$students_exam", null]},
                              "as": "students_exam"}}]},
                      "as": "exam"}},
                  "cond": {"$ne": ["$$exam", null]},
                  "as": "exam"}}, 0]}, 0]}}}])