What aggrigation should I use to achieve this

Elastic Stack Elasticsearch
1

Here my index mapping:-

{
  "settings": {
    "number_of_shards": 1
  },
  "mappings": {
    "properties": {
      "timestamp": {
        "type": "date"
      },
      "leadId": {
        "type": "keyword"
      },
      "Status": {
        "type": "keyword"
      },
      "Dates": {
        "type": "date"
      },
      "Type": {
        "type": "keyword"
      },
      "clientId": {
        "type": "keyword"
      },
      "errorReason": {
          "type": "text"
      },
      "errorCode": {
          "type": "keyword"
      }
    }
  }
}

I want to query on a range between two "Dates" and then pick the latest "leadIds" based on timestamp field from the search results and then apply group by aggregation on "Status" and "Type".

I have tried with the following query, but I am getting null pointer in _source part in the script.

{
    "query": {
        "bool": {
            "filter": [
                {
                    "range": {
                        "Dates": {
                            "lt": "now-1d/d",
                            "gte": "now-30d/d"
                        }
                    }
                }
            ]
        }
    },
    "track_total_hits": 1,
    "aggregations": {
        "latest_hits_filter": {
            "filter": {
                "script": {
                    "script": {
                        "lang": "painless",
                        "source": "boolean isLatest = true; long latestTimestamp = 0; def uniqueLeadIds = [:]; for (int i = 0; i < params._source.hits.hits.length; i++) { long timestamp = params._source.hits.hits[i]._source.timestamp.value; String leadId = params._source.hits.hits[i]._source.leadId; if (uniqueLeadIds.containsKey(leadId)) { isLatest = false; break; } else { uniqueLeadIds[leadId] = true; } if (timestamp > latestTimestamp) { latestTimestamp = timestamp; } else { isLatest = false; break; } } return isLatest;"
                    }
                }
            }
        },
        "groupby": {
            "composite": {
                "size": 1000,
                "sources": [
                    {
                        "9913489f": {
                            "terms": {
                                "field": "Status.keyword"
                            }
                        }
                    },
                    {
                        "d681f4dd": {
                            "terms": {
                                "field": "Type",
                                "missing_bucket": true,
                                "order": "asc"
                            }
                        }
                    }
                ]
            }
        }
    }
}

Is there any way to do this. Simple filter and terms based aggregation don't work because leadId can be in tens of thousands and there is no way to merge those buckets again to apply further aggregation. So I tried to use script but that is also not working. More over I don't know how to use this query to display result in a table in kibana also.

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.