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

Val · Accepted Answer · 2016-08-05 11:34:09Z

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 5, 2016 at 11:34

Val

218k14 gold badges376 silver badges383 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dixit Sourav Over a year ago

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 Over a year ago

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}

Val Over a year ago

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*"

Dixit Sourav Over a year ago

Thank you so much @Val. It's working now. I was wrong.

Collectives™ on Stack Overflow

How to write elasticsearch multiple query condition?

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related