Elasticsearch OR query

Question

Can anyone tell me how do I write the below Mysql query in elastisearch

Select * from `table` WHERE `Name`='A' OR `Name`='B' order by `rank` DESC

I have tried multiple solutions the internet like

{

"sort":{"rank":{"order":"desc"}}, 

"query": {

    "query_string" : {
        "fields" : ["Name"],
        "query" : "A OR B"
    }


}

and also tried the below code

{

"sort":{"rank":{"order":"desc"}}, 

  "query" : {
    "bool": {
      "should": [
        {
          "match_phrase" : {
            "Name" : "A"
          }
        },
        {
          "match_phrase": {
            "Name": "B"
          }
        }
      ]

    } 
  }
}

In general, you can't translate SQL queries to Elasticsearch queries. What's the Elasticsearch query that doesn't work for you? Read http://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html — Jakub Kotowski, May 07 '15 at 11:47

sven.kwiotek · Answer 1 · 2015-05-08T09:00:44.137

1

You could do it with Bool-Filter and Order on all Documents:

{
  "query": {
    "match_all": {}
  },
  "filter": {
    "bool": {
      "should": [
        {
          "term": {
            "Name": "A"
          }
        },{
          "term": {
            "Name": "B"
          }
        }
      ]
    }
  },"sort": [
    {
      "rank": {
        "order": "desc"
      }
    }
  ]
}

Or have a subset with Range Query:

"query": {
    "range": {
      "JoinDate": {
        "lte": 1431051540
      }
    }
  }

edited May 08 '15 at 09:00

answered May 07 '15 at 13:02

sven.kwiotek

1,459
15
22

Do you have defined a mapping?, if so please post it here. How is your indexing document looks like? – sven.kwiotek May 07 '15 at 14:59
What do you want me to post here, can you please be specific ? – Ironman May 07 '15 at 15:44
Thanks. I have mentioned it in previous post. What if you search the term in lowercase (e.g. "Name": "b")? How is your input looks like, or could you give an example of one document you have indexed? – sven.kwiotek May 08 '15 at 06:01
Thanks. the lowercase thing worked perfectly but how can I add `"range" : { "JoinDate" : { "lte": "1431051540" } } }` to the query ? – Ironman May 08 '15 at 07:55
Please have a look, I edited previous post. You could change the entirely query like second example. – sven.kwiotek May 08 '15 at 09:01

score 0 · Answer 2 · answered May 07 '15 at 16:29

This is my current mappings are

{
    "class": {
        "mappings": {
            "students": {
                "properties": {
                    "Name": {
                        "type": "string"
                    },
                    "rank": {
                        "type": "string"
                    },
                    "Description": {
                        "type": "string"
                    },
                    "Image": {
                        "type": "string"
                    },
                    "JoinDate": {
                        "type": "date",
                        "format": "dateOptionalTime"
                    }
                }
            }
        }
    }
}

score 0 · Answer 3 · answered May 15 '15 at 00:14

Try Terms filter. Below is equivalent of SQL query you wrote.

curl -XGET 'http://localhost:9200/_search?pretty' -d '{
    "filter": {
        "terms": {
            "Name": ["A", "B"]
        }
    },
    "sort": {
        "rank": {
            "order": "desc"
        }
    }
}'

Elasticsearch OR query

3 Answers3

Linked