CODEWITHSUNDEEP
mongodbmongodb-query
Drasungor
Drasungor

Reputation: 85

How to make a mongodb query with nested arrays

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. enter image description here

Upvotes: 1

Views: 296

Answers (1)

Takis
Takis

Reputation: 8705

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]}}}])

Upvotes: 1

Related Questions