Use painless script in transform aggregation

Elastic Stack Elasticsearch
1

Hi i am running 7.6.2 Elastic search and kibana ,
and i was trying simple painless script in transform aggregation .

POST _transform/_preview
{
  "source": {
    "index": "testindex*"
  },
 
  "pivot": {
      "group_by": {
      "customer.keyword": {
        "terms": {
          "field": "customer.keyword"
        }
      },
      "locationcode.keyword": {
        "terms": {
          "field": "locationcode.keyword"
        }
      }
    },
    "aggregations": {

 "script_fields": {
      "hour_of_day": { 
        "script": {
          "lang": "painless",
          "source": """
            ZonedDateTime date =  doc['journeydate'].value; 
            return date.getHour(); 
          """
        }
      },
      "month_of_year": { 
        "script": {
          "lang": "painless",
          "source": """
            ZonedDateTime date =  doc['journeydate'].value; 
            return date.getMonthValue(); 
          """
        }
      }
    }

    }
    
    
  }}

but its throw the error

{
  "error" : {
    "root_cause" : [
      {
        "type" : "named_object_not_found_exception",
        "reason" : "[1:35] unable to parse BaseAggregationBuilder with name [month_of_year]: parser not found"
      }
    ],
    "type" : "x_content_parse_exception",
    "reason" : "[1:525] [data_frame_transform_config] failed to parse field [pivot]",
    "caused_by" : {
      "type" : "x_content_parse_exception",
      "reason" : "[1:525] [data_frame_transform_pivot] failed to parse field [aggregations]",
      "caused_by" : {
        "type" : "named_object_not_found_exception",
        "reason" : "[1:35] unable to parse BaseAggregationBuilder with name [month_of_year]: parser not found"
      }
    }
  },
  "status" : 400
}

any suggestion on this ?
thanks

2

Hi,

there is no script_fields aggregation, maybe you mean scripted_metric or bucket_script. Examples can be found in the docs.

Hendrik

3

Hi ,
i am talking about script_fields same available in doc

4

Thanks, this looks like an error in the documentation, I will check with the docs team.

What's your usecase? What feature do you want to extract? script_fields can not be used together with aggregations, because they are only applied to the result set, which is a limited (top n) set of results.

However scripts can be used in aggregations:

  "aggregations": {
    "avg_hour": {
      "avg": {
        "script": {
          "source": "ZonedDateTime date =  doc['order_date'].value; return date.getHour();"
        }
      }
    }
  }
5

Thanks ,
my use case is trying to create transform index on my sales index .
which can have field like -
1 last visit ( which i am getting with max ( visitdate) )
2 week of last visit ( here i was trying to script , as i need to use last visit field to achieved this .)
i will try as per ur suggestion and will get back to you .

6

We fixed the documentation, I think with a max aggregation and a script, you should be able to implement 2. Once you have it, please share your solution.

7

Hi @Hendrik_Muhs ,
Thanks for your help ,yes i able to do that .
there some more condition will be , which team will put it .
but this is my basic input and output

POST _transform/_preview
{
  "source": {
    "index": "bdf_salesinvoicec1_3_2019"
  },
 
  "pivot": {
      "group_by": {
      "customer.keyword": {
        "terms": {
          "field": "customer.keyword"
        }
      },
      "locationcode.keyword": {
        "terms": {
          "field": "locationcode.keyword"
        }
      }
    },
   "aggregations": {
       "maxDoc_Date": { "max": { "field": "documentdate" }},
    "MonthofDate": {
      "max": {
        "script": {
          "source": "ZonedDateTime date =  doc['documentdate'].value;          return date.getMonthValue();"
                    
        }
        }
        },
        "dayofmonth": {
        "max": {
          "script": {
            "source": "ZonedDateTime date =  doc['documentdate'].value;          return date.getDayOfMonth();"
                    
        }
		}
		},
       "weekday": {
        "max": {
          "script": {
            "source": "return doc['documentdate'].value.getDayOfWeek() ;"
                    
        }
        
      }
    }
        }}}

output --

{
  "preview" : [
    {
      "MonthofDate" : 9.0,
      "weekday" : 5.0,
      "locationcode" : {
        "keyword" : "location1"
      },
      "customer" : {
        "keyword" : "customer 1 "
      },
      "maxDoc_Date" : "2019-09-20T01:35:50.283Z",
      "dayofmonth" : 20.0
    },
    {
      "MonthofDate" : 8.0,
      "weekday" : 4.0,
      "locationcode" : {
        "keyword" : "location2"
      },
      "customer" : {
        "keyword" : "customer 2"
      },
      "maxDoc_Date" : "2019-08-19T07:43:25.000Z",
      "dayofmonth" : 19.0
    }...

thanks