How to make a mongodb query with nested arrays

Question

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.

your drawing is the best i have seen, but text JSON is easier, so people can test their queries on them — Takis
– Takis, Commented Dec 3, 2021 at 22:09

Takis · Accepted Answer · 2021-12-04 00:09:05Z

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

Why do you use $expr? Simply use {$match: {_id: 1}} and {$match: {"exams.exam_name": "exam1"}}, {$match: {"exams.student_exams.student_email": "email1"}}?
i didnt wrote those by hand, and in my library is equal complex in code, so i forget sometimes to use the simpler versions.

Collectives™ on Stack Overflow

How to make a mongodb query with nested arrays

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related