CODEWITHSUNDEEP
elasticsearch
Helenus the Seer
Helenus the Seer

Reputation: 737

Elasticsearch OR condition with Multiple Criteria

I need to query specific results in elasticsearch, using a set of global filters, and an extended OR clause, where each case in the OR clause consists of a specific tuple of conditions. In words, it would be something like this: 'Fetch records which match these global filters: category = 'foo' and name = 'bar'. From that set, fetch records where the keywords (x, y, z) match any of the following: (1, 2, 3), (4, 5, 6), or (7, 8, 9).

For example, if I have these items:

Item 1:
   category: foo, name: bar,
   x: 1, y: 2, z: 3

Item 2:
   category: foo, name: baz,
   x: 1, y: 2, z: 3

Item 3:
   category: foo, name: bar,
   x: 4, y: 5, z: 6

Item 4:
   category: foo, name: bar,
   x: 10, y: 11, z: 12

The search should not return Item 2 (because it fails the global condition that name = 'bar'), or Item 4 (because it has (x, y, z) = (10, 11, 12), which was not one of my specified/allowed tuples). It should return the other items, which match both the global conditions and fall within the list of specified/allowed tuples for the values of x, y, z.

I know I could issue one simple query per item to do this; but I assume this would be very inefficient, since I need to specify on the order of 10K tuples or more, each time.

Apologies if this was already answered; one of the existing answers may already be adaptable for this, but I am too new to elasticsearch to recognize how to do it.

Environment: elasticsearch 7.10.1 in Python 3.8.

Upvotes: 4

Views: 8110

Answers (1)

Nishant
Nishant

Reputation: 7874

Here you go,

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "category": "foo"
          }
        },
        {
          "term": {
            "name": "bar"
          }
        },
        {
          "bool": {
            "should": [
              {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "x": 1
                      }
                    },
                    {
                      "term": {
                        "y": 2
                      }
                    },
                    {
                      "term": {
                        "z": 3
                      }
                    }
                  ]
                }
              },
              {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "x": 4
                      }
                    },
                    {
                      "term": {
                        "y": 5
                      }
                    },
                    {
                      "term": {
                        "z": 6
                      }
                    }
                  ]
                }
              },
              {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "x": 7
                      }
                    },
                    {
                      "term": {
                        "y": 8
                      }
                    },
                    {
                      "term": {
                        "z": 9
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

All you need is bool query, with a combination of must and should clauses.

Upvotes: 7

Related Questions