Mongo DB query with $regex (or $not) does not work

[paolov]

Hi,

i'm trying to use a mongo db query with a $regex operator as condition for one of the field but while the same query used directly on a mongo db client works correctly, it does show any result in jasper.
here and extract of the query i'm using:

{
     'collectionName' : 'myColl',
          findQuery :{ 
               MOV : 'SCO-DDT',
               "CODE":{"$regex" : '^(?!GEN)(?!LOC)w*$'},
               CODSTO : {"$in" : $P{Store}},
               CODAGGRSECT_RPT03 : {"$in":$P{AggrSector}},
               LIBNOLIB : "LIB",
               DATE : {'$gte' : $P{Date_From}, $lte : $P{Date_To}} 
          }
}
As said, the same query executed directly from the mongo db shell works perfectly.
i've also tried using $not instead of $regex
{
     'collectionName' : 'myColl',
          findQuery :{ 
               MOV : 'SCO-DDT',
               "CODE":{"$not" : '(^GEN|^LOC)w*$'},
               CODSTO : {"$in" : $P{Store}},
               CODAGGRSECT_RPT03 : {"$in":$P{AggrSector}},
               LIBNOLIB : "LIB",
               DATE : {'$gte' : $P{Date_From}, $lte : $P{Date_To}} 
          }
}
but in this case Jasper return a mongo db error: "$not needs a regex or a document"

anyone else have these problems with $regex and $not?

thanks

[sthomas_3]

You may try as below:

 

{
collectionName:'myColl',
findQuery:{
'code':{'$regex' : '^((?!GEN)(?!LOC).)*$'}
}
}