aggregate mongodb search in array-CodePudding

my documents in mongodb collection :

{ 
  'id' : 'ID1',
  'status' : 'ST1',
  'logs' : [
    {
      'id' : 'ID2',
      'status_old' : 'ST2',
      'status_new' : 'ST3',
    },
    {
      'id' : 'ID3',
      'status_old' : 'ST3',
      'status_new' : 'ST4',
    }
  ]
},
{ 
  'id' : 'ID4',
  'status' : 'ST4',
  'logs' : [
    {
      'id' : 'ID5',
      'status_old' : 'ST2',
      'status_new' : 'ST3',
    }
  ]
}

I want to pass the documents through the following two filters

filter1 : 
where
   ( status    = 'ST1'  OR
    status_old = 'ST1'  OR
    status_new = 'ST1'  )

Then the answer obtained should be placed in the following filter (condition):

filter2 : 
where
   ( status    = 'ST2'  OR
    status_old = 'ST2'  OR
    status_new = 'ST2'  )

The answer is stated with two conditions and according to the documents provided above, the following is the case:

{ 
  'id' : 'ID1',
  'status' : 'ST1',
  'logs' : [
    {
      'id' : 'ID2',
      'status_old' : 'ST2',
      'status_new' : 'ST3',
    },
    {
      'id' : 'ID3',
      'status_old' : 'ST3',
      'status_new' : 'ST4',
    }
  ]
},

CodePudding user response：

since you are not filtering out any elements from the logs array this looks like an aggregation with 2 $match stages with $or in it

db.collection.aggregate([
  {
    $match: {
      $or: [
        {
          "status": "ST1"
        },
        {
          "logs.status_old": "ST1"
        },
        {
          "logs.status_new": "ST1"
        }
      ]
    }
  },
  {
    $match: {
      $or: [
        {
          "status": "ST2"
        },
        {
          "logs.status_old": "ST2"
        },
        {
          "logs.status_new": "ST2"
        }
      ]
    }
  }
])

demo