How can i use sql like and clauses in ElasticSearch?

Question

i read this document to understand equality of sql in elasticsearch.(https://taohiko.wordpress.com/2014/07/18/query-dsl-elasticsearch-vs-sql/) i developed a kinda elasticsearch application it is making indexes from my data if i call below post query by using postman :


{
  "query": {
    "multi_match" : {
      "query":      "TÜRKİYE iş 3124904300",
      "type":       "cross_fields",
      "fields":     [ "title", "tcknVkn","phone","townName","cityName","poiDesc","district","street","avenue","buildingName","addressName" ],
      "operator":   "and" 
    }
  }
}

it is working perfect. But i wanna do that;


{
  "query": {
    "multi_match" : {
      "query":      "TÜRKİYE iş 312*",
      "type":       "cross_fields",
      "fields":     [ "title", "tcknVkn","phone","townName","cityName","poiDesc","district","street","avenue","buildingName","addressName" ],
      "operator":   "and" 
    }
  }
}

means:


select * from mytable where title like 'TÜRKİYE%' and addressName like 'iş%' and 
tcknVkn like '312%'

but. if i write above query elasticsearch can not understand numerical value. So it returns me empty data? How can i solve this problem. How can i convert above sql query to elasticsearch query?

(BE CAREFUL : "title", "tcknVkn","phone","townName","cityName","poiDesc","district","street","avenue","buildingName","addressName" -> all fields are indexed as string )

I guess, you should create a string value of the `tcknVkn` value while creating your mapping. — hkulekci, Feb 27 '17 at 08:16
@hkulecki tcknVkn is string . Actually everything is indexed as string — loki, Feb 27 '17 at 08:32
You can use regexp. Look [this](https://stackoverflow.com/questions/6467067/how-to-search-for-a-part-of-a-word-with-elasticsearch/51843146#51843146) please. — Ali Moshiri, Aug 16 '18 at 06:36
You also can use regexp. https://stackoverflow.com/questions/6467067/how-to-search-for-a-part-of-a-word-with-elasticsearch/51843146#51843146 It worked for me. — Ali Moshiri, Aug 16 '18 at 06:44

hkulekci · Accepted Answer · 2017-02-27T16:27:45.857

multi_match query essentially create multiple match query. And your query create like following query:

{
  "query": {
    "bool": {
      "should": [
        { "match": { "title":   "TÜRKİYE iş 312*" }},
        { "match": { "tcknVkn": "TÜRKİYE iş 312*" }},
        { "match": { "phone":   "TÜRKİYE iş 312*" }},
        ...
      ]
    }
  }
}

It is not exactly like above example but similar. But you want to separate your query keywords. I create following example to exampling your situation:

POST test2/test/_mapping
{
  "properties": {
    "tcNo": {
      "type": "long",
      "fields": {
        "text": {
          "type": "text"
        },
        "numeric": {
          "type": "integer"
        }
      }
    }
  }
}

POST test2/test
{
  "id": 0,
  "tcNo": 23432344,
  "name": "hay0"
}

POST test2/test
{
  "id": 1,
  "tcNo": 23442344,
  "name": "haydar1"
}

POST test2/test
{
  "id": 2,
  "tcNo": 23432344,
  "name": "haydar2"
}

POST test2/test
{
  "id": 3,
  "tcNo": 23542344,
  "name": "haydar3"
}


GET test2/_search
{
  "query": {
    "multi_match" : {
      "query":      "haydar* 234*",
      "type":       "phrase_prefix",
      "fields":     ["tcNo.text", "name"],
      "operator":   "AND" 
    }
  }
}

POST test2/test/_search
{
  "query": {
    "query_string": {
      "fields": ["tcNo.text", "name"],
      "query": "haydar* AND 234*"
    }
  }
}

# similar with last one
POST test2/test/_search
{
  "query": {
    "query_string": {
      "query": "(tcNo.text:234* OR name.text:234*) AND (tcNo.text:haydar* OR name:haydar*)"
    }
  }
}

Yes I know all of your fields string. But I prefer use long or integer type for integer data. At the end of the queries, multi_match one is return no result but query_string return two correct result. So, you can use query_string query to search.

Hi; it is great answer. you saved my life. Thanks man(eyvallah) — loki, Feb 27 '17 at 10:23
But i realized something. all queries working with AND" produces data like OR( And var ama or gibi davranıyor). — loki, Feb 27 '17 at 11:17
I added extra query. Second query is like `(tcNo.text:234* OR name.text:234*) AND (tcNo.text:haydar* OR name:haydar*)` this one. You can change second query as third one. — hkulekci, Feb 27 '17 at 16:08

How can i use sql like and clauses in ElasticSearch?

1 Answers1