elasticsearchelasticsearch-dslelasticsearch-painlesselasticsearch-scripting

Extract date (yyyyMMdd) from date field in ElasticSearch query


My index has a date field formatted as 2020-01-04T05:00:06.870000Z. In the ES query response, I need the date in the form yyyyMMdd, so 20200104. I tried using scripted query and extracted the day, month and year individually. How can i concatenate them in _source to get a number of the form yyyyMMdd ?

Sample data :

 "_source": {
    "updated": "2020-01-04T05:00:06.870000Z"
  }
  "_source": {
    "updated": "2020-01-04T09:00:08.870000Z"
  }
  "_source": {
    "updated": "2019-12-04T01:00:06.870000Z"
  }
}

Query:

"sort" : [
        { 
            "_script": {
                "type": "number",
                "script": {
                    "lang": "painless",
                    "source": "doc['updated'].value.getYear()"  
//similarly use getMonthOfYear() and getDayOfMonth(). How to concatenate and convert to number ?
                },
                "order": "desc"
            }
        }
    ]

Solution

  • You could use String.format to correctly fill in the digits and then Integer.parseInt on the result.

    Alternatively you could go with the following:

    GET dates/_search
    {
      "sort": [
        {
          "_script": {
            "type": "number",
            "script": {
              "lang": "painless",
              "source": """
                Integer.parseInt(
                  DateTimeFormatter
                    .ofPattern("yyyyMMdd")
                    .withZone(ZoneOffset.UTC)
                    .format(Instant.ofEpochMilli(
                      doc['updated'].value.millis)
                    ));
              """
            },
            "order": "desc"
          }
        }
      ]
    }