Elasticsearch filter by multiple fields in an object which is in an array field

Question

The goal is to filter products with multiple prices.

The data looks like this:

{
  "name":"a",
  "price":[
    {
      "membershipLevel":"Gold",
      "price":"5"
    },
    {
      "membershipLevel":"Silver",
      "price":"50"
    },
    {
      "membershipLevel":"Bronze",
      "price":"100"
    }
    ]
}

I would like to filter by membershipLevel and price. For example, if I am a silver member and query price range 0-10, the product should not appear, but if I am a gold member, the product "a" should appear. Is this kind of query supported by Elasticsearch?

Answer 1

You need to make use of nested datatype for price and make use of nested query for your use case.

Please see the below mapping, sample document, query and response:

Mapping:

PUT my_price_index
{
  "mappings": {
    "properties": {
      "name":{
        "type":"text"
      },
      "price":{
        "type":"nested",
        "properties": {
          "membershipLevel":{
            "type":"keyword"
          },
          "price":{
            "type":"double"
          }
        }
      }
    }
  }
}

Sample Document:

POST my_price_index/_doc/1
{
  "name":"a",
  "price":[
    {
      "membershipLevel":"Gold",
      "price":"5"
    },
    {
      "membershipLevel":"Silver",
      "price":"50"
    },
    {
      "membershipLevel":"Bronze",
      "price":"100"
    }
    ]
}

Query:

POST my_price_index/_search
{
  "query": {
    "nested": {
      "path": "price",
      "query": {
        "bool": {
          "must": [
            {
              "term": {
                "price.membershipLevel": "Gold"
              }
            },
            {
              "range": {
                "price.price": {
                  "gte": 0,
                  "lte": 10
                }
              }
            }
          ]
        }
      },
      "inner_hits": {}           <---- Do note this. 
    }
  }
}

The above query means, I want to return all the documents having price.price range from 0 to 10 and price.membershipLevel as Gold.

Notice that I've made use of inner_hits. The reason is despite being a nested document, ES as response would return the entire set of document instead of only the document specific to where the query clause is applicable.

In order to find the exact nested doc that has been matched, you would need to make use of inner_hits.

Below is how the response would return.

Response:

{
  "took" : 128,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 1.9808291,
    "hits" : [
      {
        "_index" : "my_price_index",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.9808291,
        "_source" : {
          "name" : "a",
          "price" : [
            {
              "membershipLevel" : "Gold",
              "price" : "5"
            },
            {
              "membershipLevel" : "Silver",
              "price" : "50"
            },
            {
              "membershipLevel" : "Bronze",
              "price" : "100"
            }
          ]
        },
        "inner_hits" : {
          "price" : {
            "hits" : {
              "total" : {
                "value" : 1,
                "relation" : "eq"
              },
              "max_score" : 1.9808291,
              "hits" : [
                {
                  "_index" : "my_price_index",
                  "_type" : "_doc",
                  "_id" : "1",
                  "_nested" : {
                    "field" : "price",
                    "offset" : 0
                  },
                  "_score" : 1.9808291,
                  "_source" : {
                    "membershipLevel" : "Gold",
                    "price" : "5"
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}

Hope this helps!