I have this output data from aggregation $lookup

     [
      {
        _id: 1,
        name: "Abraham",
        class: "V",
        question_answered: [
          {
            id: "quest1",
            answer: "A",
            score: 10,
            question: {
              soal: "apa judul lagu?",
              correct_answer: "A",
              type_question: "Essay"
            }
          },
          {
            id: "quest2",
            answer: "C",
            score: null,
            question: {
              soal: "apa judul lagu B?",
              correct_answer: "B",
              type_question: "Essay"
            }
          },
          {
            id: "quest3",
            answer: "C",
            score: 10,
            question: {
              soal: "apa judul lagu C?",
              correct_answer: "C",
              type_question: "essay_pg"
            }
          },
          
        ]
      },
      {
        _id: 2,
        name: "Brenda",
        class: "V",
        question_answered: [
          {
            id: "quest1",
            answer: "A",
            score: 10,
            question: {
              soal: "apa judul lagu A?",
              correct_answer: "A",
              type_question: "Essay"
            }
          },
          {
            id: "quest2",
            answer: "C",
            score: 0,
            question: {
              soal: "apa judul lagu B?",
              correct_answer: "B",
              type_question: "Essay"
            }
          }
        ]
      }
    ]

I need to add additional field formated_status_evaluation_essay and formated_status_evaluation_essay_pg in each data that i get with some few condition if,elseif, else. i'll give one of example addfield condition, more or less like this one:

IF(question_answered.question.type_question == 'Essay' and no score is null in every essay type question) then, formated_status_evaluation_essay = "complete scoring".

ELSEIF(there's essay type question and have at least one null score) then, formated_status_evaluation_essay = "Incomplete scoring"

ELSEIF(if theres no essay type question) then, formated_status_evaluation_essay = "no question"

Same goes to formated_status_evaluation_essay_pg. The output that i expected is like this.

    [
      {
        _id: 1,
        name: "Abraham",
        class: "V",
        question_answered: [....],
        formated_status_evaluation_essay: incomplete scoring,
        formated_status_evaluation_essay_pg: complete scoring,
    
      },
      {
        _id: 2,
        name: "Brenda",
        class: "V",
        question_answered: [....],
        formated_status_evaluation_essay: complete scoring,
        formated_status_evaluation_essay_pg: no question,
      }
    ]

The explanation about the output. _id:1, get evaluation_essay incomplete because it has one object that contain null score. But the evaluation_essay_pg contain complete scoring because essay_pg type all of it have a score.

_id:2, evaluation_essay is complete because all question with type essay have a score. But essay_pg contain no question because theres no essay_pg type in question_answer.question.type_question.

I've tried this and still confuse to code three condition like i've explained before. I put code like this in the end of $lookup aggregation.

    { 
        '$addFields': { 
          'formated_status_evaluation_essay': { 
               '$cond': [ 
                   {
                    '$and': [ 
                      {'$$question_answer.question.type_soal ': 
                      'essay'},
                      {'$$question_answer.nilai':{$ne:null}},
                      ]    
                    },
                   'already scoring', 
                   'havent scoring' 
               ] 
            } 
        } 
    }  

i almost get what i expected but, seems still have a wrong syntax i wrote. I would be very thankfull if you guys can help me. Been working for two days still got no answer.

CodePudding user response：

Try to make the code a little bit more readable by using $switch to handle the branching.

db.collection.aggregate([
  {
    "$addFields": {
      "formated_status_evaluation_essay": {
        "$filter": {
          "input": "$question_answered",
          "as": "q",
          "cond": {
            $eq: [
              "$$q.question.type_question",
              "Essay"
            ]
          }
        }
      },
      "formated_status_evaluation_essay_pg": {
        "$filter": {
          "input": "$question_answered",
          "as": "q",
          "cond": {
            $eq: [
              "$$q.question.type_question",
              "essay_pg"
            ]
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "formated_status_evaluation_essay": {
        "$switch": {
          "branches": [
            {
              "case": {
                $and: [
                  {
                    "$allElementsTrue": [
                      {
                        "$map": {
                          "input": "$formated_status_evaluation_essay.score",
                          "as": "s",
                          "in": {
                            $ne: [
                              "$$s",
                              null
                            ]
                          }
                        }
                      }
                    ]
                  },
                  {
                    $ne: [
                      {
                        $size: "$formated_status_evaluation_essay"
                      },
                      0
                    ]
                  }
                ]
              },
              "then": "complete scoring"
            },
            {
              "case": {
                "$anyElementTrue": [
                  {
                    "$map": {
                      "input": "$formated_status_evaluation_essay.score",
                      "as": "s",
                      "in": {
                        $eq: [
                          "$$s",
                          null
                        ]
                      }
                    }
                  }
                ]
              },
              "then": "incomplete scoring"
            }
          ],
          default: "no question"
        }
      },
      "formated_status_evaluation_essay_pg": {
        "$switch": {
          "branches": [
            {
              "case": {
                $and: [
                  {
                    "$allElementsTrue": [
                      {
                        "$map": {
                          "input": "$formated_status_evaluation_essay_pg.score",
                          "as": "s",
                          "in": {
                            $ne: [
                              "$$s",
                              null
                            ]
                          }
                        }
                      }
                    ]
                  },
                  {
                    $ne: [
                      {
                        $size: "$formated_status_evaluation_essay_pg"
                      },
                      0
                    ]
                  }
                ]
              },
              "then": "complete scoring"
            },
            {
              "case": {
                "$anyElementTrue": [
                  {
                    "$map": {
                      "input": "$formated_status_evaluation_essay_pg.score",
                      "as": "s",
                      "in": {
                        $eq: [
                          "$$s",
                          null
                        ]
                      }
                    }
                  }
                ]
              },
              "then": "incomplete scoring"
            }
          ],
          default: "no question"
        }
      }
    }
  }
])

Here is the Mongo playground for your reference.