CODEWITHSUNDEEP
elasticsearchelasticsearch-query
Tejas Ambalia
Tejas Ambalia

Reputation: 255

Match documents in elasticsearch with AND and OR on different fields

I am new to elastic search and I am working with MySQL and MongoDB. I want to write the query which gives products which are active and it's name or brand_name or model_number or category contains a specific word.

Here is MySQL query. SELECT * FROM products WHERE active=0 AND (name LIKE '%car%' OR brand_name LIKE '%car%' OR model_number LIKE '%car%' OR category LIKE '%car%');

I already tried with the below query in elastic search, but it's just AND operator.

"query": {
        "bool": {
            "must": [
                {"match": {"active": "1"}},
                {"regexp": {"name": "cars.*"}},
                {"regexp": {"brand_data.brand_name": "cars.*"}},
                {"regexp": {"model_number": "cars.*"}},
            ]
        }
    }

Can anyone help me to write this query in elasticsearch.

Upvotes: 0

Views: 76

Answers (2)

Alex Baidan
Alex Baidan

Reputation: 1075

Short answer - for OR you can use should occurrence of boolean query.

Long answer with example: It can be done in several ways, but I want point you to one detail. If you need to search only active=1 or active=0 results, it will be better to use filter occurrence in your query. It will give you better performance and caching.

The clause (query) must appear in matching documents. However unlike must the score of the query will be ignored. Filter clauses are executed in filter context, meaning that scoring is ignored and clauses are considered for caching.

So I can propose you this query: 

{
  "query": {
    "bool": {
      "filter": {
        "term": {
          "active": "1"
        }
      },
      "should": [
        {
          "regexp": {
            "name": "cars.*"
          }
        },
        {
          "regexp": {
            "brand_data.brand_name": "cars.*"
          }
        },
        {
          "regexp": {
            "model_number": "cars.*"
          }
        }
      ]
    }
  }
}

Upvotes: 3

Golden
Golden

Reputation: 417

You are looking for a 'or' nested inside an 'and' clause. this is the correct format:

"query": {
        "bool": {
            "must": [
                {"match": {"active": "1"}},
                {"bool":{"should":[
                {"regexp": {"name": "cars.*"}},
                {"regexp": {"brand_data.brand_name": "cars.*"}},
                {"regexp": {"model_number": "cars.*"}}]}}
            ]
        }
    }

Upvotes: 0

Related Questions