in regards to my earlier question ElasticSearch Query where mapping and doc sample is given I want to convert this MySql query to Elasticsearch. Here is the mysql query
Select * from us_data where phone!=0 AND city_code IN ('Homestead','Hialeah','Key Biscayne','Miami Beach','Miami','North Miami Beach','Ochopee','Opa Locka') AND state_code='FL' AND (name like '%appliance%' or city_code like '%appliance%' or address like '%appliance%' or phone like '%appliance%')
Looking forward for your help
Below query should help you. Note that in your mapping you have contact_no while in the above query you've mentioned phone.
Nevertheless, I've made use of contact_no so that it sticks with the mapping.
POST <your_index_name>/_search
{
"query": {
"bool": {
"must": [
{
"terms": {
"city_code.keyword": [
"Homestead",
"Hialeah",
"Key Biscayne",
"Miami Beach",
"Miami",
"North Miami Beach",
"Ochopee",
"Opa Locka"
]
}
},
{
"term": {
"state_code.keyword": "FL"
}
},
{
"multi_match": {
"query": "*appliance*",
"fields": ["name","city_code","address","contact_no"]
}
}
],
"must_not": [
{
"term": {
"contact_no.keyword": "0"
}
}
]
}
}
}
I've used variations of Terms Query, Term Query and Multi-Match query.
Note that I've used Term Query/Terms Query on the keyword fields as from the sql-query it appears you want an exact match, while I've used multi-match on the text fields.
Let me know if this helps!
