In elastic searach there is a index name personalprofile11 and there is a field named createdDate in epoch format , integer data type. but I want to make it datetime format query Datehistogramaggregation and using that converted datetime field want to count the data on database or daily base, the chatgpt give me the following query but it not working
curl -X POST "http://localhost:9200/myprofile11/_search?scroll=5m&pretty" -H 'Content-Type: application/json' -d '{
"size": 10,
"query": {
"bool": {
"must_not": [
{
"term": {
"createdDate": 0
}
}
],
"filter": [
{
"range": {
"createdDate": {
"gte": 1631320967,
"lte": 1631407367
}
}
}
]
}
},
"aggs": {
"date_terms_agg": {
"scripted_metric": {
"init_script": "state.timestamps = []",
"map_script": "if (doc.containsKey(\"createdDate\") && doc[\"createdDate\"].size() > 0) { state.timestamps.add(doc[\"createdDate\"].value * 1000) }",
"combine_script": "def merged = []; for (state in states) { merged.addAll(state.timestamps) }; return merged",
"reduce_script": "def merged = []; for (state in states) { merged.addAll(state) }; return merged"
},
"aggs": {
"date_histogram_agg": {
"date_histogram": {
"field": "createdDate",
"calendar_interval": "1d",
"format": "yyyy-MM-dd",
"min_doc_count": 1
}
}
}
}
}
}'
and it showing me the following error
{
"error" : {
"root_cause" : [
{
"type" : "script_exception",
"reason" : "compile error",
"script_stack" : [
"... ddAll(state.timestamps) }; return merged",
" ^---- HERE"
],
"script" : "def merged = []; for (state in states) { merged.addAll(state.timestamps) }; return merged",
"lang" : "painless",
"position" : {
"offset" : 74,
"start" : 49,
"end" : 89
}
}
],
"type" : "search_phase_execution_exception",
"reason" : "all shards failed",
"phase" : "query",
"grouped" : true,
"failed_shards" : [
{
"shard" : 0,
"index" : "myprofile11",
"node" : "oNajg00ZSK6sNQzE-8Lp-Q",
"reason" : {
"type" : "script_exception",
"reason" : "compile error",
"script_stack" : [
"... ddAll(state.timestamps) }; return merged",
" ^---- HERE"
],
"script" : "def merged = []; for (state in states) { merged.addAll(state.timestamps) }; return merged",
"lang" : "painless",
"position" : {
"offset" : 74,
"start" : 49,
"end" : 89
},
"caused_by" : {
"type" : "illegal_argument_exception",
"reason" : "unexpected token [';'] was expecting one of [<EOF>]."
}
}
}
]
},
"status" : 400
}
can anyone help me how can i do that please as
Blockquote
