Strict order operator not working

[KrishK (K P)]

Hi Support,

I have two documents in index.

Document 1.
12-34-56
12/34/56

Document 2.
56-12-34
56/12/34

I just need result for Document 1, if i search with "12-34-56" this keyword.

The Problem is that when i search "12-34-56", I get document1 and Doc2 in result.

The result should be only document 1.

Can you help me on this?

Example :

    DELETE testindex
    PUT testindex
    PUT testindex/_mappings
    {
    "properties": {    
    	  "filename": {
            "type": "text"
          },
          "fileid": {
            "type": "long"
          }
        }
    }
  POST testindex/_doc
    {
      "fileid" : 1,
      "filename" :"12-34-56"
    }
    POST testindex/_doc
    {
      "fileid" : 2,
      "filename" :"56-12-34"
    }
    GET testindex/_search

    GET _sql?format=txt
    {
      "query":"""select fileid,filename from testindex where QUERY('(filename:(12-34-56))','default_operator=AND')"""
    } 

Thanks

[KrishK (K P)]

Anyone can help me on this?

[spinscale (Alexander Reelsen)]

try using filename.keyword in the where clause as otherwise your field is analyzed and split into tokens.

[KrishK (K P)]

I tried but not working. Also filename is text type.

GET _sql?format=txt
{
"query":"""select fileid,filename from testindex where QUERY('(filename.keyword:(12-34-56))','default_operator=AND')"""
}

[spinscale (Alexander Reelsen)]

your mapping explicitely disabled a keyword for exact matches.

try

PUT testindex/_mappings
{
  "properties": {
    "filename": {
      "type": "text",
      "fields" : {
        "keyword" : {"type" : "keyword"}
      }
    },
    "fileid": {
      "type": "long"
    }
  }
}

this also allows you to query like

GET _sql?format=txt
{
  "query": "select fileid,filename from testindex where filename='12-34-56'"
} 

[KrishK (K P)]

Thanks
Its working. but i have still confusion.
Suppose, I have text field type and again i want to use same way.
Is it possible?

Lets see with real example:

PUT testindex/_mappings
{
"properties": {    
	  "filename": {
      "type": "text",
      "fields" : {
        "keyword" : {"type" : "keyword"}
      }
    },
    "filecontent": {
        "type": "text"
      },
      "fileid": {
        "type": "long"
      }
    }
}
POST testindex/_doc
{
  "fileid" : 1,
  "filename" :"abcde-101.pdf",
  "filecontent":"This is testing document with metadata 12-34-56"
}
POST testindex/_doc
{
  "fileid" : 2,
  "filename" :"abcde-102.pdf",
  "filecontent":"This is testing document with metadata 56-12-34"
}
GET testindex/_search

POST _sql?format=txt
{
  "query": """SELECT * FROM testindex WHERE QUERY('(filecontent:(12-34-56) OR filecontent:(12-34-56))','default_operator=AND')"""
}

What will happen in this case?
Thanks

[spinscale (Alexander Reelsen)]

please take some time to sit down and understand how search is working, as this can be quite confusing when coming from the SQL world. You are running a full text search here, which means that a term like 12-34-56 is split into several terms like 12 AND 34 AND 56, which will match for both documents. If you need exact matches here, you will need. to sit down and understand the steps doing tokenization and filtering when terms are stored in the inverted index used for search.

Even though a bit aged, one of the best resources IMO is still the free to read definitive guide

hope this helps as a start.

[KrishK (K P)]

Hi Alex,

I have already implemented in Sphinx. and its working.

I just want to know that is there any way, so that we can search in same way like sphinx.

If you are not understood, tell me i will post with an example.

Thanks

[KrishK (K P)]

Anything possible?