# Как сделать правильно запрос

**URL:** <https://discuss.elastic.co/t/topic/204344>\
**Category:** Вопросы на русском языке\
**Created:** [October 20, 2019, 9:49am UTC](https://discuss.elastic.co/t/topic/204344 "2019-10-20T09:49:06Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dimasp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimasp/32/56213_2.png) [@Dimasp](https://discuss.elastic.co/u/Dimasp)\
**Post date:** [October 20, 2019, 9:49am UTC](https://discuss.elastic.co/t/topic/204344/1 "2019-10-20T09:49:06Z")

</div>

Добрый день!  
Есть запрос SQL как правильно сделать в Elastic  
Ищем слова где and ( city\_id IN ( "12,34,56,76,67,") or city IN ( 'Киев', 'Харьков') )

---

<div class="post-metadata">

**Author:** ![Dimasp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimasp/32/56213_2.png) [@Dimasp](https://discuss.elastic.co/u/Dimasp)\
**Post date:** [October 21, 2019, 8:36am UTC](https://discuss.elastic.co/t/topic/204344/2 "2019-10-21T08:36:58Z")

</div>

Если сложный запрос подскажите где посмотреть.  
В данный момент я уже понял как делать and and, а вот как делать and ( city\_id IN ( "12,34,56,76,67,") or city IN ( 'Киев', 'Харьков') ) пока не ясно.  
Вот код  
{  
"query": {

```
"bool": {
  "must": [
    {
      "match": {
        "city": "Киев"
      }
    },{
      "match": {
        "category": "1"
      }
    },
     

    {
      
       
       "simple_query_string" : {
          "query": "Купить iPhone 6s",
    "fields": ["name"]
    
}
      }
    
  ]

}

```

}  
}

---

<div class="post-metadata">

**Author:** ![Denis\_Lamanov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/denis_lamanov/32/13111_2.png) [@Denis\_Lamanov](https://discuss.elastic.co/u/Denis_Lamanov)\
**Post date:** [October 21, 2019, 8:58am UTC](https://discuss.elastic.co/t/topic/204344/3 "2019-10-21T08:58:33Z")

</div>

`{ "query" : { "bool" : { "filter" : { "terms" : { "id" : [1, 2, 3] } } } } }`

---

<div class="post-metadata">

**Author:** ![Dimasp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimasp/32/56213_2.png) [@Dimasp](https://discuss.elastic.co/u/Dimasp)\
**Post date:** [October 21, 2019, 9:10am UTC](https://discuss.elastic.co/t/topic/204344/4 "2019-10-21T09:10:35Z")

</div>

Спасибо  
Оставлю здесь может кому-то пригодись код

```auto
    "query": {

    "bool": {
      "must": [
        {
          "match": {
            "city": "Киев"
          }
        },
        
        {
                "terms":{
                   "category_id":[
                      "1","11"
                   ]
                }
             },
                     {
                "terms":{
                   "city_id":[
                      "1","11"
                   ]
                }
             },

         
    
        {
          
           
           "simple_query_string" : {
              "query": "купить iPhone 6s ",
        "fields": ["name"]
        
    }
          }
        
      ]
    
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Dimasp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimasp/32/56213_2.png) [@Dimasp](https://discuss.elastic.co/u/Dimasp)\
**Post date:** [October 21, 2019, 12:28pm UTC](https://discuss.elastic.co/t/topic/204344/5 "2019-10-21T12:28:26Z")

</div>

Нет запрос не правильно делает выборку.  
Cкажите как сделать такой запрос из SQL в elasticsearch  
SELECT \* FROM `p` WHERE MATCH (name) AGAINST ('Купить' ) AND category IN ( 2,3,4,5,6,7,8,9,10,1) and (region = 2121 or city\_id IN ( 3331,3332,3343) or city IN ( 'Бар','Бершадь','Винница','Винницкая область' ) )

---

<div class="post-metadata">

**Author:** ![Dimasp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimasp/32/56213_2.png) [@Dimasp](https://discuss.elastic.co/u/Dimasp)\
**Post date:** [October 21, 2019, 1:52pm UTC](https://discuss.elastic.co/t/topic/204344/6 "2019-10-21T13:52:59Z")

</div>

Вижу запрос не очень легкий может есть что-то похоже

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [October 21, 2019, 2:01pm UTC](https://discuss.elastic.co/t/topic/204344/7 "2019-10-21T14:01:19Z")

</div>

А что не работает, и какой mapping у полей в этом индексе?

---

<div class="post-metadata">

**Author:** ![Dimasp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimasp/32/56213_2.png) [@Dimasp](https://discuss.elastic.co/u/Dimasp)\
**Post date:** [October 21, 2019, 2:07pm UTC](https://discuss.elastic.co/t/topic/204344/8 "2019-10-21T14:07:22Z")

</div>

```
"mappings" : {
      "properties" : {
        "@timestamp" : {
          "type" : "date"
        },
        "@version" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "category_id" : {
          "type" : "long"
        },
        "city" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "city_id" : {
          "type" : "long"
        },
        "description" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "id" : {
          "type" : "long"
        },
        "name" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "region" : {
          "type" : "long"
        }
      }
    }
```

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [October 21, 2019, 2:25pm UTC](https://discuss.elastic.co/t/topic/204344/9 "2019-10-21T14:25:32Z")

</div>

Если требуется точное совпадение, то надо использовать `city.keyword`. А запросы на `region`, `city_id` и `city` должны идти под отдельные `bool` с `should` вместо `must`.

---

<div class="post-metadata">

**Author:** ![Dimasp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimasp/32/56213_2.png) [@Dimasp](https://discuss.elastic.co/u/Dimasp)\
**Post date:** [October 21, 2019, 2:30pm UTC](https://discuss.elastic.co/t/topic/204344/10 "2019-10-21T14:30:25Z")

</div>

Можно получить наглядный пример

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [October 21, 2019, 2:51pm UTC](https://discuss.elastic.co/t/topic/204344/11 "2019-10-21T14:51:15Z")

</div>

Конечно, после того, как вы ответите на мой вопрос:

> [@Igor\_Motov](#):
>
> А что не работает?

---

<div class="post-metadata">

**Author:** ![Dimasp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimasp/32/56213_2.png) [@Dimasp](https://discuss.elastic.co/u/Dimasp)\
**Post date:** [October 21, 2019, 4:57pm UTC](https://discuss.elastic.co/t/topic/204344/12 "2019-10-21T16:57:53Z")

</div>

дело в том что все работает. Хотелось узнать как записать такой запрос в json для elasticsearch  
{  
"query": "SELECT \* FROM p WHERE MATCH(name,'купить iphone 6s') AND category\_id IN ( 2,3,4,5,6,7,8,9,10,1) and (region = 2121 or city\_id IN ( 3331,3332,3343) or city IN ( 'Бар','Бершадь','Винница','Винницкая область' ) ) "  
}

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [October 21, 2019, 5:09pm UTC](https://discuss.elastic.co/t/topic/204344/13 "2019-10-21T17:09:47Z")

</div>

Вы ранее написали:

> [@Dimasp](#):
>
> Нет запрос не правильно делает выборку.

теперь вы пишите

> [@Dimasp](#):
>
> все работает.

Я что-то не очень понимаю.

Если SQL работает и вам надо получить именно тот запрос, который SQL использует, то можно запустить

```auto
POST /_sql/translate
{
 "query": "SELECT * FROM p WHERE MATCH(name,'купить iphone 6s') AND category_id IN ( 2,3,4,5,6,7,8,9,10,1) and (region = 2121 or city_id IN ( 3331,3332,3343) or city IN ( 'Бар','Бершадь','Винница','Винницкая область' ) ) "
}

```

И тогда вы получите, большой запрос, который можно упростить до:

```auto
{
  "query" : {
    "bool" : {
      "must" : [
        {
          "match" : {
            "name" : {
              "query" : "купить iphone 6s"
            }
          }
        },
        {
          "terms" : {
            "category_id" : [2, 3, 4, 5, 6, 7, 8, 9, 10, 1]
          }
        },
        {
          "bool" : {
            "should" : [
              {
                "term" : {
                  "region" : {
                    "value" : 2121
                  }
                }
              },
              {
                "terms" : {
                  "city_id" : [3331, 3332, 3343]
                }
              },
              {
                "terms" : {
                  "city.keyword" : ["Бар", "Бершадь", "Винница", "Винницкая область"]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

```

---

<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:** [November 18, 2019, 5:09pm UTC](https://discuss.elastic.co/t/topic/204344/14 "2019-11-18T17:09:48Z")

</div>

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