Filtering Array with Array Mongodb

Question

I have one data example like this one in MongoDB

{
  id: 628a8683fc489c315b330ade,
  examName : 'Final Exam',
  schedule: 
  {
    scheduleDate : '2021-10-18T17:00:00.000+00:00'
    scheduleName : 'Package Test'
    package:[
       {
         exclude_question:[1,2],
         namePackage:testing
         packageId:628a89c2fc489c315b330b01      
       }
        
    ]
  },
  question:[
    {
       id:1,
       question:'hello can u test it?'
       answer:'No'
    }
    {
       id:3,
       question:'Is it working?'
       answer:'No'
    }
    {
       id:2,
       question:'Should i repeat?'
       answer:'Yes'
    }
  ]
}

Condition: Basically at schedule.package it is possible to have more than one value, but I have already filtered it so only one value is in the array.

Now I need to filter the question with exclude_question. Value of exlcude_question is an array id of question and now i need to only show the question that not in exlcude_question soo the output that i expect is like this one

{
  id: 628a8683fc489c315b330ade,
  examName : 'Final Exam',
  schedule: {.......},
  question:[
    {
       id:3,
       question:'Is it working?'
       answer:'No'
    }
  ]
}

it only shows the question with id: 3 because it is not in exclude_question. I'm kinda confused on how to get the value from array inside the array to filter the question array. If you have any suggestion i will very thankful

user_8017690 · Answer

$set - Create exclude_questions field:

1.1. $reduce - Iterate every document of schedule.package, merge ($concatArrays) exclude_question array value into an new array and return it.
$set - Set the value for question field.

2.1. $filter - Filter the document in question array field with the document's id $not $in exclude_questions array.
$unset - Remove exclude_questions field.

db.collection.aggregate([
  {
    $set: {
      exclude_questions: {
        $reduce: {
          input: "$schedule.package",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this.exclude_question"
            ]
          }
        }
      }
    }
  },
  {
    $set: {
      question: {
        $filter: {
          input: "$question",
          cond: {
            $not: {
              $in: [
                "$$this.id",
                "$exclude_questions"
              ]
            }
          }
        }
      }
    }
  },
  {
    $unset: "exclude_questions"
  }
])

Sample Mongo Playground

user_7975771 · Answer

You can use the aggregation like following,

you already mentioned shedule.package has always one element, so the arrays what you need to exclude. For safety operator we use $ifNull
$filter to exclude the questions that you need to exclude
$unset to remove the varaible that we set in the first step

here is the code

db.collection.aggregate([
  {
    "$addFields": {
      "exe_qus": {
        "$ifNull": [
          { "$arrayElemAt": [ "$schedule.package", 0 ] },
          []
        ]
      }
    }
  },
  {
    "$addFields": {
      "question": {
        "$filter": {
          "input": "$question",
          "cond": {
            $not: {
              $in: [ "$$this.id", "$exe_qus.exclude_question" ]
            }
          }
        }
      }
    }
  },
  {
    "$unset": "exe_qus"
  }
])

Working Mongo playground

user_1409374 · Answer

Here's another way to do it.

db.collection.aggregate([
  {
    "$set": {
      "question": {
        "$filter": {
          "input": "$question",
          "as": "q",
          "cond": {
            "$not": [
              {"$in": ["$$q.id", {"$first": "$schedule.package.exclude_question"}]}
            ]
          }
        }
      }
    }
  }
])

Try it on mongoplayground.net.

Filtering Array with Array Mongodb

3 Answers