SQL IN equivalent with ElasticSearch

Question

I'm trying to figure out how to do the equivalent of a SQL IN clause with ElasticSearch.

The query below will work but when I change it to "query": ["1589", "1590"] it does not. I believe it's doing an AND on these 2 values for the same field and I would like it to do an OR or an WHERE IN.

Works

{
  "size": 10,
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "userId": {
              "query": ["1589"]
            }
          }
        }
      ]
    }
  }
}

Fails

{
  "size": 10,
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "userId": {
              "query": ["1589", "1590"]
            }
          }
        }
      ]
    }
  }
}

Vineeth Mohan · Accepted Answer · 2016-01-14 16:42:34Z

1

Well the below should work -

{
  "size": 10,
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "userId": {
              "query": ["1589"]
            }
          }
        },
        {
          "match": {
            "userId": {
              "query": ["1590"]
            }
          }
        }

      ]
    }
  }
}

Remember must is something like AND and should can be used ( alone ) to get something like OR

answered Jan 14, 2016 at 16:42

Vineeth Mohan

19.4k9 gold badges70 silver badges81 bronze badges

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

3 Comments

user3953989 Over a year ago

I guess I need to post another question specific to the C# library for ES. I would like to avoid looping through all of my users to generate an enormous query. Is there anyway to do this using an array style?

Vineeth Mohan Over a year ago

Try terms query , it works for your example but will work differently for normal strings.

NikoNyrh Over a year ago

Isn't this incorrect, he was asking to do OR not AND? This works if you use should instead of must.

NikoNyrh · Accepted Answer · 2016-01-14 20:18:08Z

0

You didn't mention which version you use, 1.7 has terms filter and in 2.x it is replaced by terms query. It seems that userId would be not_analyzed and match is "intended" for analyzed fields. They support various ways of combining the array of terms, at least the filter does.

answered Jan 14, 2016 at 20:18

NikoNyrh

4,2084 gold badges21 silver badges38 bronze badges

Collectives™ on Stack Overflow

SQL IN equivalent with ElasticSearch

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related