Elasticsearch bucket_script to subtract two timestamps

Unable to subtract two time stamps using bucket_script aggregation:

Below is my query:

POST xxxxxxxxxxxxxxxxxxxxxxxxxx/_search?size=0
{
"query": {
"bool": {
"must": [
{
"match_phrase": {
"projectId.keyword": "xxxxxxxxxxxxxxxxxxxxxx"
}
}
],
"filter": {
"range": {
"page.time": {
"gte": "now-w",
"lte": "now"
}
}
}
}
},
"aggs": {
"sessionLengthData": {
"terms": {
"field": "sId.keyword",
"size": 10
},
"aggs": {
"firstUrl":{
"terms":{
"field":"page.time",
"size":1,
"order":{
"_term":"asc"
}
}
},
"lastUrl":{
"terms":{
"field":"page.time",
"size":1,
"order":{
"_term":"desc"
}
}
},
"sessionLength": {
"bucket_script": {
"buckets_path": {
"startTime": "firstUrl._bucket_key",
"endTime": "lastUrl._bucket_count"
},
"script": "params.endTime - params.startTime"

                        }
                    }
        }
      }
    }
    }

In bucket_script query i am taking time stamp values from upper aggregation query.

Below are my sample records:

"_source": {
          "projectId": "*************************",
          "sId": "6b7xisp971521692338655",
          "pId": "6b7xisp971521692338655.1",
          "startTime": 1521692338655,
          "page": {
            "sid": "6b7xisp971521692338655",
            "time": 1521700149947,
            "url": "********************************",
            "pageMinUrl": "************************",
            "host": "**************************************",
            "start": 0,
            "pageLoad": 1523,
            "network": 668,
            "dom": 669,
          }
}

 "_source": {
          "projectId": "*****************************",
          "sId": "6b7xisp971521692338655",
          "pId": "6b7xisp971521692338655.2",
          "startTime": 1521692338655,
          "page": {
            "sid": "6b7xisp971521692338655",
            "time": 1521700349782,
            "url": "********************************",
            "pageMinUrl": "******************************",
            "host": "****************************************",
            "start": 0,
            "pageLoad": 1537,
            "network": 212,
            "dom": 1109,
            
          }
} 

Give me some suggestions how to use bucket_script to subtract to time stamps values

Do you need those firstUrl and lastUrl aggregations to be terms aggregations? Because if you change those to min and max aggregations the solution is simple:

{
  "query": {
    "bool": {
      "filter": {
        "range": {
          "page.time": {
            "gte": "now-w",
            "lte": "now"
          }
        }
      }
    }
  },
  "aggs": {
    "sessionLengthData": {
      "terms": {
        "field": "sId.keyword",
        "size": 10
      },
      "aggs": {
        "firstUrl": {
          "min": {
            "field": "page.time"
          }
        },
        "lastUrl": {
          "max": {
            "field": "page.time"
          }
        },
        "sessionLength": {
          "bucket_script": {
            "buckets_path": {
              "startTime": "firstUrl",
              "endTime": "lastUrl"
            },
            "script": "params.endTime - params.startTime"
          }
        }
      }
    }
  }
}

@abdon, Thanks for the reply. I need firstUrl and lastUrl aggregation to be terms aggregations only.

Alright. Well, you can still retrieve those terms aggs in addition to the min and max metric aggregations:

{
  "query": {
    "bool": {
      "filter": {
        "range": {
          "page.time": {
            "gte": "now-w",
            "lte": "now"
          }
        }
      }
    }
  },
  "aggs": {
    "sessionLengthData": {
      "terms": {
        "field": "sId.keyword",
        "size": 10
      },
      "aggs": {
        "firstUrl": {
          "terms": {
            "field": "page.time",
            "size": 1,
            "order": {
              "_term": "asc"
            }
          }
        },
        "lastUrl": {
          "terms": {
            "field": "page.time",
            "size": 1,
            "order": {
              "_term": "desc"
            }
          }
        },
        "min": {
          "min": {
            "field": "page.time"
          }
        },
        "max": {
          "max": {
            "field": "page.time"
          }
        },
        "sessionLength": {
          "bucket_script": {
            "buckets_path": {
              "startTime": "min",
              "endTime": "max"
            },
            "script": "params.endTime - params.startTime"
          }
        }
      }
    }
  }
}

@abdon, Thank you . It worked :grinning:

1 Like

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