How to write elasticsearch multiple query condition?

Question

I have stored some data in elasticsearch module and structure is very simple.

[
   {
      "country_id":1,
      "city_id":12,
      "city_name":"Kolkata"
   },
   {
      "country_id":1,
      "city_id":55,
      "city_name":"Delhi"
   },
   {
      "country_id":2,
      "city_id":18,
      "city_name":"Las Vegas"
   },
   {
      "country_id":3,
      "city_id":22,
      "city_name":"Sydney"
   }
]

I need a search query like

"Select * from table_name where country_id = 1 and city_name like %k%"

If any one there please help me to find out the exact elasticsearch query for the above sql query.

I have tried with this query but it is producing errors.

curl -XGET "http://xxx.xxx.xxx.x:9200/xxxx/location_details/_search?size=10" -d '{"query":{"bool":{"must":{"term":{"country_id":"101"}}},{"match_phrase":{"city_name":"a"}}}}'

score 1 · Accepted Answer · answered Aug 05 '16 at 11:34

1

That's a good start

Try this instead:

curl -XPOST "http://xxx.xxx.xxx.x:9200/xxxx/location_details/_search" -d '{
  "size": 10,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "country_id": "101"
          }
        },
        {
          "query_string": {
            "query": "city_name:*a*"
          }
        }
      ]
    }
  }
}

answered Aug 05 '16 at 11:34

Val

207,596
13
358
360

Thanks @Val but I am getting error for this query: `curl -XGET "http://xxx.xxx.xxx.x:9200/location/location_details/_search?size=5000" -d '{"query":{"bool":{"must":[{"term":{"country_id":"101"}},{"query_string":{"query":{"city_name":"*s*"}}}]}}}'` – Dixit Sourav Aug 05 '16 at 12:27
Error Generating : `{"error":{"root_cause":[{"type":"query_parsing_exception","reason":"[query_string] query does not support [city_name]","index":"xx","line":1,"col":83}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"rudra","node":"4e1L2eHtQ5SCPhDeHw-IGg","reason":{"type":"query_parsing_exception","reason":"[query_string] query does not support [city_name]","index":"rudra","line":1,"col":83}}]},"status":400}` – Dixit Sourav Aug 05 '16 at 12:32
You're doing it wrong, check again my answer, your query should be like this: `curl -XGET "http://xxx.xxx.xxx.x:9200/location/location_details/_search?size=5000" -d '{"query":{"bool":{"must":[{"term":{"country_id":"101"}},{"query_string":{"query‌":"city_name":"*s*"}}]}}}'`, i.e. no `{...}` around `"city_name":"*s*"` – Val Aug 05 '16 at 12:37
Thank you so much @Val. It's working now. I was wrong. – Dixit Sourav Aug 05 '16 at 12:51

How to write elasticsearch multiple query condition?

1 Answers1