How to filter some pattern in aggregate pipeline?

Working with Data
#1

Hello

Data is like this,

{subject : "abc-value",
 update_time: "20220720 23:00:01"},
{subject : "abc-key",
 update_time: "20220720 23:00:01"},

I want to exclude null and “*-key” in subject field

I tried as below but it didn’t work

$match: 
{
  $and: [
    {
      subject: {
        $ne: null
      }
    },
    {
      subject: {
        $ne: {
          $regex: RegExp("\\/-key$\\/i")
        }
      }
    }
  ]
}

anyone help please
thank you

#2

Rather than And you should use Or operator as you are trying to exclude both the records null and subject contains -key.

#3

thanks for the reply Shyam_Sohane

i changed like this as you said

$match: 
{
  $or: [
    {
      subject: {
        $ne: null
      }
    },
    {
      subject: {
        $ne: {
          $regex: RegExp("\\/-key$\\/i")  <- not working here
        }
      }
    }
  ]
}

could you please fix or give me advice?

#4

please try below.

db.collection.find({$or: [{"subject" : { $not : { $regex : /.*-key/i }}}, {"subject" : { $not : { $eq : null }}}]})
#5

Not really, not at least using

There is a major flaw in the logic since the above seems to match all documents.

For all documents with subject=null,

{"subject" : { $not : { $regex : /.*-key/i }}}

will be true, as the $regex will be false, and $not:false will be true.

For all documents with subject:*-key, $eq:null will be false, so $not:false will be true.

@kIL_Yoon, you were pretty close to the correct solution. The only issue was the use of RegEx() function call and the use of $ne rather than $not.

Try with:

{ $match: { $and: [ { subject: { $ne: null } }, { subject: { $not : { $regex: "-key$" } } } ] }}

You may even forgo the explicit $and and use the implicit and version:

{ $match: { "subject" : { $ne: null , $not : { $regex: "-key$" } } } }
#6

but thats what he asked.

I want to exclude null and “*-key” in subject field. If we have only regex need to check if it returns null or not.

#7

Yes correct it has to be and else not null will match -key as well. My bad.