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

  {
  "name": "",
  "surname": "",
  "email": "",
  "phone_number": "",
  "address": "",
  "birth_date": "",
  "ssn": "",
  "emergency_contact_name": "",
  "emergency_contact_relationship": "",
  "emergency_contact_phone": "",
  "tests": [
    {
      "date": "",
      "place": "",
      "outcome": "",
      "attending": [
        {
          "name": "",
          "surname": "",
          "id": "",
          "role": ""
        }
      ],
      "authorized_body": {
        "gps_position": "",
        "name": "",
        "type_of_entity": "",
        "department": {
          "address": ""
        }
      }
    }
  ]
}

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?

CodePudding user response：

Query

• from tests keep only those
• or
  • date < "2019-02-22"
  • nurse != "0187"
    This way a test to pass it must be in before date or done from another nurse.
• nurse != "0187" is checked using a path "$test.attending.id" to get all the ids of that test(an array), and test that the intersection with ["0187"] is empty => that nurse didn't do the test.

*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"}}}}])