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