Filtering Array with Array Mongodb-CodePudding

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

CodePudding user response：

$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

CodePudding user response：

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

CodePudding user response：

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.