MongoDB - Filter array with array

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 exclude_question is an array id of the question and now I need to only show the question that is not in exlcude_question.

So 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 about how to get the value from the array inside the array to filter the question array. If you have any suggestions I will very thankful.

Yong Shun · Accepted Answer · 2022-05-23 05:46:56Z

$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

rickhg12hs · Accepted Answer · 2022-05-23 15:49:00Z

1

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.

answered May 23, 2022 at 15:49

rickhg12hs

12k6 gold badges31 silver badges50 bronze badges

Comments

varman · Accepted Answer · 2022-05-23 05:56:41Z

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

Collectives™ on Stack Overflow

MongoDB - Filter array with array

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related