How to use AND codition in ElasticSearch?

Question

I want to search with two fields in ElasticSearch, fields are:

1) SearchTerm (Textfield)

2) City (Textfield)

If I use mysql Query would be:

if both has value

select * from table where term = 'abc' AND city = 'mexico'

if only term has value

select * from table where term = 'abc'

if only city has value

select * from table where city = 'mexico'

How can I use this in ElasticSearch I'm trying with the following:

"bool" : {
"should": [
    { 
    "multi_match": {
        "query":   '"'+term.toLowerCase()+'"',
        "fields": 
        [ 
                "title", "desc" 
        ],
        "operator" : "and"
    }
    }, 
    { 
    "wildcard": {
        "city": {
            value: city.toLowerCase()
        }
    }
    }  
],
}

Please help

Thanks

Randheer

MartinSchulze · Answer 1 · 2017-10-04T09:06:28.147

2

And conditions can be modeled with a must clause in your bool query

Edit: I understood your question and your city query is not right it should be:

{
  "bool": {
    "should": [{
        "multi_match": {
          "query": '"' + term.toLowerCase() + '"',
          "fields":
          [
            "title", "desc"
          ],
          "operator": "and"
        }
      }, {
        "wildcard": {
          "city": city.toLowerCase()
        }
      }
    ]
  }
}

edited Oct 04 '17 at 09:06

answered Oct 04 '17 at 08:25

MartinSchulze

889
1
7
14

I tried that, but if I use only city then it wont work, "must" work if both the value entered it dosn't work with single field !! – Randheer Singh Chouhan Oct 04 '17 at 08:34
Please provide the mapping of your index! – MartinSchulze Oct 04 '17 at 08:42
`bool:{ "must": [ { "multi_match": { "query": '"'+keyword.toLowerCase()+'"', "fields": [ "title", "desc" ], "operator" : "and" } }, { "match":{ "city": city.toLowerCase() } } ], }` – Randheer Singh Chouhan Oct 04 '17 at 08:47
If I use must as above it won't work with only search term – Randheer Singh Chouhan Oct 04 '17 at 08:47
That's your query. I think it could help, to see your index mapping. You could find it on your ```elasticurl/_mapping``` address. – MartinSchulze Oct 04 '17 at 08:49
It dosn't have mapping..we are inserting data directly – Randheer Singh Chouhan Oct 04 '17 at 08:54
I did use "match" instead of wildcards but it didn't work. – Randheer Singh Chouhan Oct 04 '17 at 09:10
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/155887/discussion-between-martinschulze-and-randheer-singh-chouhan). – MartinSchulze Oct 04 '17 at 09:13
sure, I'm joining chat – Randheer Singh Chouhan Oct 04 '17 at 09:15

NeeruKSingh · Answer 2 · 2017-10-05T09:10:09.087

Bool query with must clause(Using curl):-

1.) select * from table where term = 'abc' AND city = 'mexico'

curl -XGET 'localhost:9200/indexName/_search?pretty&size=50' -H 'Content-Type: application/json' -d'      {
"query":{
  "bool":{
    "must":[
      {
       "term":{ "city":"mexico" }
      },
      {
       "term":{ "term":"abc" }
      }
     ]
   }
  }
}';

2.) select * from table where term = 'abc'

curl -XGET 'localhost:9200/indexName/_search?pretty&size=50' -H 'Content-Type: application/json' -d'  {
 "query":{
   "bool":{
     "must":[{
       "term":{ "term":"abc" }
     }]
   }
 }

}';

3.) select * from table where city = 'mexico'

curl -XGET 'localhost:9200/indexName/_search?pretty&size=50' -H 'Content-Type: application/json' -d'   {
 "query":{
   "bool":{
     "must":[{
       "term":{ "city":"mexico" }
     }]
   }
 }
}';

for more details find here:- https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html

How to use AND codition in ElasticSearch?

2 Answers2