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!

Answer 2

Let me take show you how to do it, using the nested fields and query and filter context. I will take your example to show, you how to define index mapping, index sample documents, and search query.

It's important to note the include_in_parent param in Elasticsearch mapping, which allows us to use these nested fields without using the nested fields.

Please refer to Elasticsearch documentation about it.

If true, all fields in the nested object are also added to the parent document as standard (flat) fields. Defaults to false.

Index Def

{
    "mappings": {
        "properties": {
            "product": {
                "type": "nested",
                "include_in_parent": true
            }
        }
    }
}

Index sample docs

{
    "product": {
        "price" : 5,
        "membershipLevel" : "Gold"
    }
}
{
    "product": {
        "price" : 50,
        "membershipLevel" : "Silver"
    }
}

{
    "product": {
        "price" : 100,
        "membershipLevel" : "Bronze"
    }
}

Search query to show Gold with price range 0-10

{
    "query": {
        "bool": {
            "must": [
                {
                    "match": {
                        "product.membershipLevel": "Gold"
                    }
                }
            ],
            "filter": [
                {
                    "range": {
                        "product.price": {
                            "gte": 0,
                            "lte" : 10
                        }
                    }
                }
            ]
        }
    }
}

Result

"hits": [
            {
                "_index": "so-60620921-nested",
                "_type": "_doc",
                "_id": "1",
                "_score": 1.0296195,
                "_source": {
                    "product": {
                        "price": 5,
                        "membershipLevel": "Gold"
                    }
                }
            }
        ]

Search query to exclude Silver, with same price range

{
    "query": {
        "bool": {
            "must": [
                {
                    "match": {
                        "product.membershipLevel": "Silver"
                    }
                }
            ],
            "filter": [
                {
                    "range": {
                        "product.price": {
                            "gte": 0,
                            "lte" : 10
                        }
                    }
                }
            ]
        }
    }
}

Above query doesn't return any result as there isn't any matching result.

P.S :- this SO answer might help you to understand nested fields and query on them in detail.

Answer 3

You have to use Nested fields and nested query to archive this: https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-nested-query.html

Define you Price property with type "Nested" and then you will be able to filter by every property of nested object