CODEWITHSUNDEEP
elasticsearchcountelasticsearch-aggregationelasticsearch-dslelasticsearch-query
hatellla
hatellla

Reputation: 5142

Find the number of documents with filter at a particular time in Elasticsearch

I have documents in elasticsearch in which each document looks something like as follows:

{
  "id": "T12890ADSA12",
  "status": "ENDED",
  "type": "SAMPLE",
  "updatedAt": "2020-05-29T18:18:08.483Z",
  "audit": [
    {
      "event": "STARTED",
      "version": 1,
      "timestamp": "2020-04-30T13:41:25.862Z"
    },
    {
      "event": "INPROGRESS",
      "version": 2,
      "timestamp": "2020-05-14T17:03:09.137Z"
    },
    {
      "event": "INPROGRESS",
      "version": 3,
      "timestamp": "2020-05-17T17:03:09.137Z"
    },
    {
      "event": "ENDED",
      "version": 4,
      "timestamp": "2020-05-29T18:18:08.483Z"
    }
  ],
  "createdAt": "2020-04-30T13:41:25.862Z"
}

If I wanted to know the number of documents which are in STARTED state at a particular time given. How can I do that? It should use the timestamp from each event in the events field.

Edit: Mapping of the index is as follows:

{
  "id": "text",
  "status": "text",
  "type": "text",
  "updatedAt": "date",
  "events": [
    {
      "event": "text",
      "version": long,
      "timestamp": "date"
    }
  ],
  "createdAt": "date"
}

Upvotes: 0

Views: 42

Answers (1)

Val
Val

Reputation: 217494

In order to achieve what you want, you need to make sure that the events array is of nested type because you have two conditions that you need to apply on each array element and this is only possible if events is nested:

    "events" : {
      "type": "nested",                     <--- you need to add this
      "properties" : {
        "event" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "timestamp" : {
          "type" : "date"
        },
        "version" : {
          "type" : "long"
        }
      }
    },

Then you'll be able to run the following nested query:

{
  "query": {
    "nested": {
      "path": "events",
      "query": {
        "bool": {
          "must": [
            {
              "range": {
                "events.date": {
                  "gte": "2020-06-08",
                  "lte": "2020-06-08"
                }
              }
            },
            {
              "term": {
                "events.event": "STARTED"
              }
            }
          ]
        }
      }
    }
  }
}

Upvotes: 1

Related Questions