ES regexp not working properly

Hello,

I want to query my index and get documents where "@" is present in my "cust_email" field.

GET my_index/_search
    {
"sort": [
    { "last_updated_date": {"order":"desc"}}
],
   "_source": [ "cust_email"],
      "from" : 0, "size" : 10,
  "query": {
    "bool": {
      "must": [ 
     {
        "regexp": {
          "cust_email": ".*@.*"
         }
    }
      ]
    }
  }
}

However ES gives result where "@" is not present in "cust_email" field.
see results, I m getting "cust_email" : "mbol" in some row, where "@" is not there

Can Anyone please suggest, why this is happening and, what's the solution to ignore records where "@" is not present.

{
  "took" : 687,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1714,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [
      {
        "_index" : "my_index",
        "_type" : "_doc",
        "_id" : "2f9e5f22a1bc2dc7e6dd419f0b55ca44d8eb2eed",
        "_score" : null,
        "_source" : {
          "cust_email" : "nasserhli888@live.com"
        },
        "sort" : [
          1613374232000
        ]
      },
      {
        "_index" : "my_index",
        "_type" : "_doc",
        "_id" : "95d9d77dfec631b48cb882d456318918d4212649",
        "_score" : null,
        "_source" : {
          "cust_email" : "aubrey.annelie@gmail.com"
        },
        "sort" : [
          1613374229000
        ]
      },
      {
        "_index" : "my_index",
        "_type" : "_doc",
        "_id" : "f3a79288541e14e90b48f2ed03fd94d8984f97d2",
        "_score" : null,
        "_source" : {
          "cust_email" : "joenabhan@hotmail.com"
        },
        "sort" : [
          1613374214000
        ]
      },
      {
        "_index" : "my_index",
        "_type" : "_doc",
        "_id" : "2c6c6fcb086628748b0d4cc97d313409bc05e5cb",
        "_score" : null,
        "_source" : {
          "cust_email" : "mbol"
        },
        "sort" : [
          1613374212000
        ]
      },
      {
        "_index" : "my_index",
        "_type" : "_doc",
        "_id" : "fbdd18ac0511e1e1c21ce4383585128b3d767614",
        "_score" : null,
        "_source" : {
          "cust_email" : "mbol"
        },
        "sort" : [
          1613374206000
        ]
      },
      {
        "_index" : "my_index",
        "_type" : "_doc",
        "_id" : "7230d8c151fccf0513c3a8d89ab980cf2c7b6eac",
        "_score" : null,
        "_source" : {
          "cust_email" : "nabulsihani@yahoo.com"
        },
        "sort" : [
          1613374202000
        ]
      },
      {
        "_index" : "my_index",
        "_type" : "_doc",
        "_id" : "0143409fc0255ce223c29146687f572136770948",
        "_score" : null,
        "_source" : {
          "cust_email" : "mbol"
        },
        "sort" : [
          1613374201000
        ]
      },
      {
        "_index" : "my_index",
        "_type" : "_doc",
        "_id" : "40784392aaa30c0c100794ed523d6bf92b592d3c",
        "_score" : null,
        "_source" : {
          "cust_email" : "joeybernardino@hotmail.com"
        },
        "sort" : [
          1613374198000
        ]
      },
      {
        "_index" : "my_index",
        "_type" : "_doc",
        "_id" : "0e693a29f6eab48b1971755702e2652a311a3598",
        "_score" : null,
        "_source" : {
          "cust_email" : "ninakalaw@yahoo.co.in"
        },
        "sort" : [
          1613374195000
        ]
      },
      {
        "_index" : "my_index",
        "_type" : "_doc",
        "_id" : "46dc21b4ab5291f7c7387c87b34c2fb2425834d3",
        "_score" : null,
        "_source" : {
          "cust_email" : "mbol"
        },
        "sort" : [
          1613374186000
        ]
      }
    ]
  }
}

This is because by default the @ symbol has a special meaning. See these docs.

To disable this interpretation you'll need to set parser flags e.g.

POST test/_search
{
  "query": {
    "regexp": {
      "email.keyword": {
        "value": ".*@.*",
        "flags": "COMPLEMENT|INTERSECTION|INTERVAL"
      }
    }
  }
}

Thank you @Mark_Harwood
Does .keyword is compulsory , if yes why??
I tried without .keyword then it gives nothing.
However with .keyword it works perfectly,

Thanks Again, Made my day!

Glad you got it working.
To understand keyword you need to understand about mappings (how your JSON content is organised into searchable indices).
This doc describes how JSON strings are mapped by default if you don't declare a mapping for your fields.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.