Elasticsearch - Search and filter by price

Elastic Stack Elasticsearch
1

I have some array with promocodes (comes from request):

$promocodes = ['promo1', 'promo2', 'promo4'];

And I have products data in Elasticsearch (as example, I will give data for three products):

// Product data example 

"price": 275,
"promocodes" : [
        {
          "code" : "promo1",
          "price" : 265.5
        },
        {
          "code" : "promo2",
          "price" : 270
        },
        {
          "code" : "promo3",
          "price" : 250
        }
]

I need to filter products data by price based on promocodes. If you set a range for the price and have promocodes, then you need to filter the products. If the product has the same promocode, then you need to take the price for this promotional code, not the main price. If 2 promocodes match for one product, then you need to take a lower price. In my example, the same product has 2 promotional codes for one product, I need to take the lower price out of 2 prices for the promocode and filter for that particular price.

This request does not filter prices as I need and has such problem (see the search query below and Product data example, next I will describe the essence of the problem) - if the range for price and promocodes.price is equal to "gte":270,"lte":271 and in terms of promocodes.code is equal to ["promo1","promo2","promo4"] the request does not work - in fact, he should not choose this product, since the price according to the lowest promotional code is 265.5 and does not fall into the diapason of range, but he still selects this product and does not add the desired Promocode for inner_hits (for some reason he chooses "promo2" for inner_hits with price 270).

GET dev_products/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "range": {
                  "price": {
                    "gte": 270,
                    "lte": 271
                  }
                }
              }
            ],
            "must_not": [
              {
                "nested": {
                  "path": "promocodes",
                  "query": {
                    "bool": {
                      "must": [
                        {
                          "terms": {
                            "promocodes.code": [
                              "promo1",
                              "promo2",
                              "promo4"
                            ]
                          }
                        }

                      ]
                    }
                  },
                  "inner_hits": {
                    "sort": {
                      "promocodes.price": "asc"
                    },
                    "size": 1
                  }
                }
              }
            ]
          }
        },
        {
          "nested": {
            "path": "promocodes",
            "query": {
              "bool": {
                "must": [
                  {
                    "terms": {
                      "promocodes.code": [
                        "promo1",
                        "promo2",
                        "promo4"
                      ]
                    }
                  },
                  {
                    "range": {
                      "promocodes.price": {
                        "gte": 270,
                        "lte": 271
                      }
                    }
                  }
                ]
              }
            },
            "inner_hits": {
              "sort": {
                "promocodes.price": "asc"
              },
              "size": 1
            }
          }
        }
      ]
    }
  }
}

I don't know how to make a request correctly, I ask you for help.

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.