mongodbmongodb-queryaggregation-frameworksubdocument

How to remove a subdocument matching a condition from an array of subdocuments in Mongo DB


I have a MongoDB database containing documents in the following format:

  {
  "name": "",
  "surname": "",
  "ssn": "",
  "tests": [
    {
      "date": "",
      "attending": [
        {
          "id": "",
          "role": ""
        }
      ]
    }
  ]
}

A document contains, for each person, a list of tests with their relative information. I woud like to remove from each document only the subdocuments (belonging to the tests array) which were performed after a certain date (2019-02-22) and by a certain nurse (id = 0187).

Since all the dates are saved in string format, for date comparison I used the $dateFromString operator in the following way:

db.Vaccinations.aggregate([
"$match": {
  "$expr": {
    "$and": [
      {
      "$gte": [
        { "$dateFromString": { "dateString": "$tests.date"}},
        ISODate("2019-02-22T00:00:00Z")
      ]},
      {
        "tests.attending.id" : "0187"
      }
    ]
    }
  }
]) 

However I wasn't able to use the $pull operator to remove the coerresponding subdocuments. Is it possible to do so or is $pull the wrong operator?


Solution

  • Query

    *you can use updateOne or updateMany depending on your needs

    *i didn't use $dateFromString if dates have the same format like YYYY-MM-DD comparison will work, but in general save dates in the database, it makes things easier and faster.

    Test code here

    update(
    {},
    [{"$set": 
       {"tests": 
         {"$filter": 
           {"input": "$tests",
            "cond": 
             {"$or": 
               [{"$lt": ["$$test.date", "2019-02-22"]},
                {"$eq": 
                  [{"$setIntersection": [["0187"], "$$test.attending.id"]}, []]}]},
            "as": "test"}}}}])