Schema optimization/alternative for nested objects

[cbp698 (chandan)]

Hi,
We are using Elastic search for storing product catalog in e-commerce domain.

We have different prices in different regions for a given product. Every product belongs to certain category.

Our query pattern is we search products against a certain category. Our product index mapping looks like this

    {
    "_id":"234",
    "category":"abc",
    "prices":[
          {
            "district_id":1,
            "price":100,
            "qty":10
          },
          {
            "district_id":2,
            "price":200,
            "qty":20
          },
          {
            "district_id":3,
            "price":200,
            "qty":0
          }
          ...
          ]
    }

Here prices is of "nested" type. This is user facing api.

For every api, for a given category we need to fetch products and select the product only if price and quantity of the product is greater than zero for a particular district.

Our match query looks like this.

    GET /_index/_search
    {
    "query": {
    "bool": {
     "filter": [
        {
          "term": {
            "category": "abc"
          }
        }      
      ],
     must:[
        {
          "bool": {
            "must": [
              {"nested": {
                "path": "prices",
                "query": {
                  "bool": {
                    "must": [
                      {"term": {
                        "prices.district_id": {
                          "value": 1
                        }
                      }},
                      {
                        "range": {
                          "prices.price": {
                            "gt": 0
                          }
                        }
                      },
                      {
                        "range": {
                          "prices.qty": {
                            "gt": 0
                          }
                        }
                      }
                    ]
                  }
                }
              }}
            ]
          }
        }
     ]
     }
    }
    }

We are able to get the product documents matching the condition,
however to pick the price of the product for a district, need to loop the array again and fetch the price.

For a given category we have 4-5k products.
Looping for each product document, once documents are filtered according to the above condition to look up for the price is time consuming, due to which latency is increasing.

Is there any better approach compared to this one or is this the right format to store different prices for product.

Please let me know, thanks in advance

[Christian_Dahlqvist]

Have you looked into using inner hits to only return what you need?

[cbp698 (chandan)]

Hi,
Yes I have checked inner_hits. But the problem is, once the price is matched and fetched from nested object, another dynamic field is emitted using "runtime_mappings" for our use case.

For that price from nested array is the input. So i wont be able to get the nested object value, i can get it only after the query is executed.

[Christian_Dahlqvist]

Then I do not really have any suggestions.

[cbp698 (chandan)]

Would you suggest any better schema than the above?

[Christian_Dahlqvist]

Have you considered flattening the model and storing one document per product and region instead of using nesting?