I have this document:

[
  {
    "username_id": "user01",
    "passwordList": [
      {
        "tstamp": 101,
        "tempInd": 0,
        "pass": "aaaa"
      },
      {
        "tstamp": 102,
        "tempInd": 0,
        "pass": "bbbbb"
      },
      {
        "tstamp": 103,
        "tempInd": 0,
        "pass": "ccccc"
      },
      {
        "tstamp": 100,
        "tempInd": 1,
        "pass": "99999"
      }
    ]
  }
]

What I want is to remove from the passwordList the element which has the lowest tstamp with tempInd equal to 0. This is my expected output:

[
  {
    "username_id": "user01",
    "passwordList": [
      {
        "tstamp": 102,
        "tempInd": 0,
        "pass": "bbbbb"
      },
      {
        "tstamp": 103,
        "tempInd": 0,
        "pass": "ccccc"
      },
      {
        "tstamp": 100,
        "tempInd": 1,
        "pass": "99999"
      }
    ]
  }
]

This is my attempt:

db.collection.update([
        {"username_id": "user01" } ,        
        {"$pull":{"passwordList": { "$elemMatch": { "tempInd": 0 , "tstamp": {"$min": "$passwordList.tstamp"} } } } }
        ])

Any suggestion? Thanks!

CodePudding user response：

You can do it like this:

db.collection.update(
  { "username_id": "user01" } ,
  [
    {
      $set: {
        passwordList: {
          $filter: {
            input: '$passwordList',
            as: 'filter1Password',
            cond: {
              $ne: [
                '$$filter1Password',
                {
                  $first: {
                    $sortArray: {
                      input: {
                        $filter: {
                          input: '$passwordList',
                          as: 'filter2Password',
                          cond: {
                            $eq: ['$$filter2Password.tempInd', 0]
                          }
                        }
                      },
                      sortBy: {
                        tstamp: 1
                      }
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  ]
)

Working from the inside out:

1. The innermost $filter operator discards all array elements whose tempInd is not 0.
2. The $sortArray operator sorts the result of step 1 by tstamp, ascending. (note that $sortArray is only available in Mongo 5.2 and newer)
3. The $first operator returns the first element of the array returned by step 2 (this would be the element with the lowest tstamp whose tempInd is 0)
4. The $filter operator returns all elements of the passwordList array that are NOT equal to the result of step 3. (note that if the array has multiple elements that all match the result of step 3, all of them will be removed)
5. The $set operator sets passwordList to be the result of step 4.