ElasticSearch - Complicated Order on 2 fields?

Question

I have this sample data

Name: A
Price: 200
PromotionPrice: 100
PromotionStart: 2020/01/01
PromotionEnd:   2020/01/15

PromotionPrice would be 0 if there is no promotion on them

I would like to sort those have PromotionPrice comming first (asc or desc) and those dont have PromotionPrice coming later. (filter out those have PromotionPrice but out of promotion time range)

I tried working on _script but it's too complicated for me.

Currently working on version 7.

My mapping:

"Price" : {"type": "long"}
"PromotionPrice": {"type": "long"},
"PromotionStart": {"type": "date"},  
"PromotionEnd": {"type": "date"},

My code: Sort by Price or PromotionPrice but did not prioritize those items with promotion first.

"sort": [
            {
                "_script": {
                    "type": "number",
                    "script": {
                        "lang": "painless",
                        "params": {
                            "now": "120000000"
                        },
                        "source": "if (doc['PromotionPrice'].value != 0 && params.now <= doc['PromotionEnd'].value.getMillis() && params.now >= doc['PromotionStart'].value.getMillis()) {doc['selling_price'].value} else {doc['normal_price'].value}"
                    },
                    "order": "asc"
                }
            }
        ]

Expected Result:

Name: A
Price: 200
PromotionPrice: 100
PromotionStart: 2020/11/01
PromotionEnd:   2020/12/01

Name: B
Price: 500
PromotionPrice: 200
PromotionStart: 2020/11/01
PromotionEnd:   2020/12/01


Name: C
Price: 500
PromotionPrice: 300
PromotionStart: 2020/11/01
PromotionEnd:   2020/12/01


Name: E
Price: 100
PromotionPrice: 0
PromotionStart: 2020/11/01
PromotionEnd:   2020/12/01


Name: D                              
Price: 500               #have PromotionPrice but treated as not having 
PromotionPrice: 300      #cuz out of promotion time range
PromotionStart: 2020/11/01
PromotionEnd:   2020/11/05

Further topic:

Based on the logic of @Val:

If I would like to sort asc or desc those with promotion above:

with now and type would be included in params

def isPromo = (doc.new_price.value > 0 && doc.start_date.value.getMillis() <  Long.parseLong(params.now) && doc.end_date.value.getMillis() >  Long.parseLong(params.now)); 
if (isPromo? && if (params.type == 'asc') {return true;} return false;) 
(return doc.new_price.value: (doc.price.value+100000000) return doc.new_price.value: (doc.price.value-100000000

Sad that this one not working

Would be easy for us to provide working example, if you can share you mapping, sample data and expected output — Amit
– Amit, Commented Nov 9, 2020 at 10:33
@ElasticsearchNinja , sorry for providing a question with lack of information, please check it again as I updated — Chau Loi
– Chau Loi, Commented Nov 9, 2020 at 10:54

Val · Accepted Answer · 2020-11-09 10:53:52Z

Great start, your script almost contains the right logic, you just need to add a constant to the regular price as you'll see in my response below.

The following script can help you sort your results the way you expect. The way it works is inlined in comments into the script:

POST promotions/_search
{
  "sort": {
    "_script": {
      "type": "number",
      "script": {
        "lang": "painless",
        "source": """
        // current time
        def now = new Date().getTime();

        // check if the current product satisfies to the promotion constraints
        def isPromo = (doc.PromotionPrice.value > 0 && doc.PromotionStart.value.getMillis() < now && doc.PromotionEnd.value.getMillis() > now);

        // if the product qualifies for promotion return its promotion price, otherwise return its normal price + a constant
        return isPromo ? doc.PromotionPrice.value : (doc.Price.value + 100000000);
        """
      },
      "order": "asc"
    }
  }
}

Results:

    "hits" : [
      {
        "_index" : "promotions",
        "_type" : "_doc",
        "_id" : "ilucrHUB7CBHpnCGfnBn",
        "_score" : null,
        "_source" : {
          "Name" : "A",
          "Price" : 200,
          "PromotionPrice" : 100,
          "PromotionStart" : "2020-11-01",
          "PromotionEnd" : "2020-12-01"
        },
        "sort" : [
          100.0
        ]
      },
      {
        "_index" : "promotions",
        "_type" : "_doc",
        "_id" : "i1ucrHUB7CBHpnCGfnBn",
        "_score" : null,
        "_source" : {
          "Name" : "B",
          "Price" : 500,
          "PromotionPrice" : 200,
          "PromotionStart" : "2020-11-01",
          "PromotionEnd" : "2020-12-01"
        },
        "sort" : [
          200.0
        ]
      },
      {
        "_index" : "promotions",
        "_type" : "_doc",
        "_id" : "jFucrHUB7CBHpnCGfnBn",
        "_score" : null,
        "_source" : {
          "Name" : "C",
          "Price" : 500,
          "PromotionPrice" : 300,
          "PromotionStart" : "2020-11-01",
          "PromotionEnd" : "2020-12-01"
        },
        "sort" : [
          300.0
        ]
      },
      {
        "_index" : "promotions",
        "_type" : "_doc",
        "_id" : "jVucrHUB7CBHpnCGfnBn",
        "_score" : null,
        "_source" : {
          "Name" : "E",
          "Price" : 100,
          "PromotionPrice" : 0,
          "PromotionStart" : "2020-11-01",
          "PromotionEnd" : "2020-12-01"
        },
        "sort" : [
          1.000001E8
        ]
      },
      {
        "_index" : "promotions",
        "_type" : "_doc",
        "_id" : "jlucrHUB7CBHpnCGfnBn",
        "_score" : null,
        "_source" : {
          "Name" : "D",
          "Price" : 500,
          "PromotionPrice" : 300,
          "PromotionStart" : "2020-11-01",
          "PromotionEnd" : "2020-11-05"
        },
        "sort" : [
          1.000005E8
        ]
      }
    ]

It works but I just cant make it sorts ascor descfor products with promotion above, mind you having a look on my latest JSON above, thanks
I don't understand what your code is doing... You cannot return a boolean AND a value at the same time.

Collectives™ on Stack Overflow

ElasticSearch - Complicated Order on 2 fields?

Currently working on version 7.

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Currently working on version 7.

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related