OR and AND Operators in Elasticsearch query

Question

I have few json document with the following format :-

    _source: {
            userId: "A1A1",
            customerId: "C1",
            component: "comp_1",
            timestamp: 1408986553,
     }

I want to query the document based on the following :-

(( userId == currentUserId) OR ( customerId== currentCustomerId) OR (currentRole ==ADMIN) )  AND component= currentComponent)

I tried using the SearchSourceBuilder and QueryBuilders.matchQuery, but I wasnt able to put multiple sub queries with AND and OR operators.

SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
searchSourceBuilder.query(QueryBuilders.matchQuery("userId",userId)).sort("timestamp", SortOrder.DESC).size(count);

How we query elasticsearch using OR and AND operators?

score 51 · Accepted Answer · edited Sep 23 '21 at 09:30

I think in this case the Bool query is the best shot.

Something like :

{
    "bool" : {
        "must" : { "term" : { "component" : "comp_1" } },
        "should" : [
            { "term" : { "userId" : "A1A1" } },
            { "term" : { "customerId" : "C1" } },
            { "term" : { "currentRole" : "ADMIN" } }
        ],
        "minimum_should_match" : 1
    }
}

Which gives in Java:

QueryBuilder qb = QueryBuilders
    .boolQuery()
    .must(termQuery("component", currentComponent))
    .should(termQuery("userId", currentUserId))
    .should(termQuery("customerId", currentCustomerId))
    .should(termQuery("currentRole", ADMIN))
    .minimumNumberShouldMatch(1)

The must parts are ANDs, the should parts are more or less ORs, except that you can specify a minimum number of shoulds to match (using minimum_should_match), this minimum being 1 by default I think (but you could set it to 0, meaning that a document matching no should condition would be returned as well).

If you want to do more complex queries involving nested ANDs and ORs, simply nest other bool queries inside must or should parts.

Also, as you're looking for exact values (ids and so on), maybe you can use term queries instead of match queries, which spare you the analysis phase (if those fields are analyzed at all, which doesn't necessarily make sense for ids). If they are analyzed, you still can do that, but only if you know exactly how your terms are stored (standard analyzer stores them lower cased for instance).

`.minimumShouldMatch` takes a string only as an argument, right? — BairDev, Mar 30 '15 at 13:24
@malte You're right. In this case it should be `minimumNumberShouldMatch` with an `int`, or `minimumShouldMatch` with a `string`. Corrected. — xlecoustillier, Mar 30 '15 at 13:31

score 3 · Answer 2 · answered Aug 29 '14 at 08:43

3

If you use a query_string query, your ANDs and ORs will be interpreted as such by the Lucene library.

This allows you to search for

(currentUserId OR currentCustomerId) AND currentComponent

for instance. By default, the values will be searched for in all fields.

answered Aug 29 '14 at 08:43

Alix Martin

332
1
5

But something like `QueryBuilders.queryString("(userId: "+currentUserId+" OR customerId: "+currentCustomerId+" OR currentRole: "+ADMIN+") AND component: "+currentComponent+")")` should work the way intended. – xlecoustillier Aug 29 '14 at 09:08
from the linked page of ElasticSearch documentation : "The default field for query terms if no prefix field is specified. Defaults to the index.query.default_field index settings, which in turn defaults to _all." Apparently, ElasticSearch adds the _all field at index time, to which we can then apply Lucene syntax. – Alix Martin Aug 29 '14 at 09:09

score 1 · Answer 3 · answered Jun 15 '23 at 13:32

Here's an example of query with AND, OR and NOT:

GET cities/_search
{
  "query": {"bool": {"must": [
    {"bool": {"should": [
      {"bool": {"must": [
        {"match": {"description": "great"}},
        {"match": {"description": "orange"}}
      ]}},
      {"match": {"description": "popular"}}
    ]}},

    {"bool": {"must_not": [
      {"match": {"description": "poor"}}
    ]}},

    {"bool": {"should": [
      {"match": {"city": "London"}},
      {"match": {"city": "Paris"}},
    ]}}
  ]}},
"size": 20,
"from": 0
}

It searches among specified cities only cities, which description includes ("great" AND "orange") OR "popular" words but excludes results, where "poor" word found

must means AND, should means OR, must_not means NOT

Other examples you can find here:

OR and AND Operators in Elasticsearch query

3 Answers3

Linked