# Strict order operator not working

**URL:** <https://discuss.elastic.co/t/strict-order-operator-not-working/219612>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [February 17, 2020, 12:08pm UTC](https://discuss.elastic.co/t/strict-order-operator-not-working/219612 "2020-02-17T12:08:30Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![KrishK](https://avatars.discourse-cdn.com/v4/letter/k/c89c15/32.png) [@KrishK](https://discuss.elastic.co/u/KrishK)\
**Post date:** [February 17, 2020, 12:08pm UTC](https://discuss.elastic.co/t/strict-order-operator-not-working/219612/1 "2020-02-17T12:08:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![KrishK](https://avatars.discourse-cdn.com/v4/letter/k/c89c15/32.png) [@KrishK](https://discuss.elastic.co/u/KrishK)\
**Post date:** [February 18, 2020, 9:37am UTC](https://discuss.elastic.co/t/strict-order-operator-not-working/219612/2 "2020-02-18T09:37:38Z")

</div>

Anyone can help me on this?

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [February 18, 2020, 9:58am UTC](https://discuss.elastic.co/t/strict-order-operator-not-working/219612/3 "2020-02-18T09:58:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![KrishK](https://avatars.discourse-cdn.com/v4/letter/k/c89c15/32.png) [@KrishK](https://discuss.elastic.co/u/KrishK)\
**Post date:** [February 18, 2020, 10:01am UTC](https://discuss.elastic.co/t/strict-order-operator-not-working/219612/4 "2020-02-18T10:01:10Z")

</div>

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')"""  
}

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [February 18, 2020, 10:37am UTC](https://discuss.elastic.co/t/strict-order-operator-not-working/219612/5 "2020-02-18T10:37:23Z")

</div>

your mapping explicitely disabled a keyword for exact matches.

try

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

```

this also allows you to query like

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

```

---

<div class="post-metadata">

**Author:** ![KrishK](https://avatars.discourse-cdn.com/v4/letter/k/c89c15/32.png) [@KrishK](https://discuss.elastic.co/u/KrishK)\
**Post date:** [February 18, 2020, 10:54am UTC](https://discuss.elastic.co/t/strict-order-operator-not-working/219612/6 "2020-02-18T10:54:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [February 19, 2020, 1:55pm UTC](https://discuss.elastic.co/t/strict-order-operator-not-working/219612/7 "2020-02-19T13:55:10Z")

</div>

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](https://www.elastic.co/guide/en/elasticsearch/guide/current/index.html)

hope this helps as a start.

---

<div class="post-metadata">

**Author:** ![KrishK](https://avatars.discourse-cdn.com/v4/letter/k/c89c15/32.png) [@KrishK](https://discuss.elastic.co/u/KrishK)\
**Post date:** [February 20, 2020, 12:54pm UTC](https://discuss.elastic.co/t/strict-order-operator-not-working/219612/8 "2020-02-20T12:54:14Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/0/7/07e50574e0a459072fe39d7f301396b30388db2b.png)

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

Thanks

---

<div class="post-metadata">

**Author:** ![KrishK](https://avatars.discourse-cdn.com/v4/letter/k/c89c15/32.png) [@KrishK](https://discuss.elastic.co/u/KrishK)\
**Post date:** [February 27, 2020, 12:53pm UTC](https://discuss.elastic.co/t/strict-order-operator-not-working/219612/9 "2020-02-27T12:53:14Z")

</div>

Anything possible?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 26, 2020, 12:53pm UTC](https://discuss.elastic.co/t/strict-order-operator-not-working/219612/10 "2020-03-26T12:53:16Z")

</div>

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