Elasticsearch painless script compilation error on date field

Question

In my e-commerce project, I was trying to sort the products based on the sale start field and also by the orderingData from the PIM. eg: I have salestart key in the ES document and also we have orderingDatalike(1,2,3,4)

In FED(react) we need to order the products by orderingData and need to push the not available for sale data to last

    from: fromIndex,
    index: 'products',
    body: {
      size: 30,
      query: {
         bool: {
           must: 
             term: ['category.categoryId'] = 1
         }
      },
      sort: [{_script: {
                 type: 'number',
                 script: {
                   "lang": "painless",
                   "source" : "(doc['saleStart'].value.toInstant().toEpochMilli() > params.current_time.toInstant().toEpochMilli()) ? 1 : 0",
                   "params": {
                      "current_time" : moment(new Date())
                    }
             },
             order: 'asc',
            }},
           'orderingData': 'asc']
    }

but i got compilation erro like this

    "error": "[script_exception] runtime error, with { script_stack={ 0=\"(doc['saleStart'].value.getMillis() > params.current_time.getMillis()) ? 1 : 0\" & 1=\"                                                             ^---- HERE\" } & script=\"(doc['saleStart'].value.getMillis() > params.current_time.getMillis()) ? 1 : 0\" & lang=\"painless\" & position={ offset=61 & start=0 & end=82 } }"
}

Tried multiple ways and still got the same kind of error. am new to elasticsearch please give me with some solutions to make it work

Thanks in advance

Answer 1

push the test data

POST _bulk
{ "index": { "_index": "test_products", "_id": "1" } }
{ "category": { "categoryId": 1 }, "saleStart": "2023-08-09T23:00:00Z", "orderingData": 1 }
{ "index": { "_index": "test_products", "_id": "2" } }
{ "category": { "categoryId": 1 }, "saleStart": "2023-08-10T08:00:00Z", "orderingData": 2 }
{ "index": { "_index": "test_products", "_id": "3" } }
{ "category": { "categoryId": 1 }, "saleStart": "2023-08-10T08:00:00Z", "orderingData": 3 }
{ "index": { "_index": "test_products", "_id": "4" } }
{ "category": { "categoryId": 1 }, "saleStart": "2023-08-11T15:00:00Z", "orderingData": 4 }

test to see the current timestamp, please note that it can show the UTC timestamp.

GET /test_products/_search
{
  "query": {
    "match_all": {}
  },
  "script_fields": {
    "current_timestamp": {
      "script": {
        "lang": "painless",
        "source": "Instant.ofEpochMilli(System.currentTimeMillis()).toString()"
      }
    }
  }
}

the query will sort by saleStart date if the date bigger than current timestamp. If not it will sort by orderingData. Also, If the timestamps are the same it will sort by orderingData after sort by saleStart.

GET /test_products/_search
{
  "query": {
    "match_all": {}
  },
  "sort": [
    {
      "_script": {
        "type": "number",
        "script": {
          "lang": "painless",
          "source": """
            def saleStart;
            if (doc['saleStart'].size() != 0) {
              saleStart = ZonedDateTime.parse(doc['saleStart'].value.toString()).toInstant();
            }
            def now = Instant.ofEpochMilli(System.currentTimeMillis());
            if (saleStart != null && saleStart.isAfter(now)) {
              return saleStart.toEpochMilli();
            } else {
              return now.toEpochMilli();
            }
          """
        },
        "order": "desc"
      }
    },
    {
      "orderingData": {
        "order": "desc"
      }
    }
  ]
}