Search code examples
elasticsearchelasticsearch-aggregation

In Elastic search how to filter result set on multiple fields

can someone help with my search query on how I can filter the results based on 2 fields? I have built an Index with 1000's of documents init and from the UI we will be calling this Index and it consists of 2 search fields

  1. Search by Zipcode
  2. and search by city/state

Based on these combinations we need to show results only within that zip code.

Mapping

{
  "mappings": {
    "properties": {
      "address": {
        "properties": {
          "city": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "state": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "zipcode": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      },
      "startdate": {
        "type": "date"
      },
      "enddate": {
        "type": "date"
      },
      "customerstatus": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "customerid": {
        "type": "long"
      }
    }
  },
  "settings": {
    "index": {
      "number_of_shards": "1",
      "number_of_replicas": "1"
    }
  }
}

Query

 {
  "from": 0,
  "size": 100,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "32081",
            "fields": ["address.zipcode" ]
          }
        },
        {
          "query_string": {
            "query": "FL",
            "fields": ["address.cityname","address.state" ]
          }
        }
      ]
    }
  }
}

Result set

{
    "customerid":1,
    "customerstatus": Active,
    "address": {
                        "city": "PONTE VEDRA",
                        "state": "FL",
                        "zipcode": "32081"
                    },
    "startdate": "2020-07-15",
    "enddate": "2021-07-15" 
},
{
    "customerid":2,
    "customerstatus": Pending,
    "address": {
                        "city": "PONTE VEDRA",
                        "state": "FL",
                        "zipcode": "32081"
                    },
    "startdate": "2018-01-01",
    "enddate": "2019-01-01" 
},
{
    "customerid":3,
    "customerstatus": Pending,
    "address": {
                        "city": "PONTE VEDRA",
                        "state": "FL",
                        "zipcode": "32081"
                    },
    "startdate": "2020-06-01",
    "enddate": "2021-06-01" 
},
{
    "customerid":4,
    "customerstatus": Pending,
    "address": {
                        "city": "PONTE VEDRA",
                        "state": "FL",
                        "zipcode": "32081"
                    },
    "startdate": "2021-01-01",
    "enddate": "2022-01-01" 
},
{
    "customerid":5,
    "customerstatus": Inactive,
    "address": {
                        "city": "PONTE VEDRA",
                        "state": "FL",
                        "zipcode": "32081"
                    },
    "startdate": "2020-07-15",
    "enddate": "2021-07-15" 
},
{
    "customerid":6,
    "customerstatus": cancelled,
    "address": {
                        "city": "PONTE VEDRA",
                        "state": "FL",
                        "zipcode": "32081"
                    },
    "startdate": "2020-07-15",
    "enddate": "2021-07-15" 
}

Now the requirement is in a way that,

  1. Exclude the results where customerstatus is Inactive and Cancelled (Customer 5 and 6 shouldn't be displayed)
  2. Display only Active and Pending
  3. If Status is Pending then display the customers where the enddate is <500 days old and enddate not greater than 91 days

So, how can I get only customerid 1 and 3 in my result set.

Solution

  • You can use a combination of bool query along with range query to find documents on the basis of a range of days. Try out this below query

    {
  "from": 0,
  "size": 100,
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "match": {
                  "customerstatus": "pending"
                }
              },
              {
                "range": {
                  "enddate": {
                    "gt": "now-500d/d",
                    "lte": "now+91d/d"
                  }
                }
              }
            ]
          }
        },
        {
          "match": {
            "customerstatus": "active"
          }
        }
      ],
      "must_not": {
        "terms": {
          "customerstatus.keyword": [
            "Inactive",
            "cancelled"
          ]
        }
      }
    }
  }
}

    Search Result will be

    "hits": [
      {
        "_index": "67260491",
        "_type": "_doc",
        "_id": "3",
        "_score": 1.6931472,
        "_source": {
          "customerid": 3,
          "customerstatus": "Pending",
          "address": {
            "city": "PONTE VEDRA",
            "state": "FL",
            "zipcode": "32081"
          },
          "startdate": "2020-06-01",
          "enddate": "2021-06-01"
        }
      },
      {
        "_index": "67260491",
        "_type": "_doc",
        "_id": "1",
        "_score": 1.5404451,
        "_source": {
          "customerid": 1,
          "customerstatus": "Active",
          "address": {
            "city": "PONTE VEDRA",
            "state": "FL",
            "zipcode": "32081"
          },
          "startdate": "2020-07-15",
          "enddate": "2021-07-15"
        }
      }
    ]