Grafana Elasticsearch - Query condition that references field value

user_1861001 0
Viewed 498

Given the following Elasticsearch document structure

{
  "mappings": {
    "doc": {
      "properties": {
        "projectKey": {
          "type": "keyword"
        },
        "documentDate": {
          "type": "date"
        },
        "lastAnalysisDate": {
          "type": "date"
        },
        "qualityScore": {
          "type": "float"
        }
      }
    }
  }
}

I would like to get all documents that satisfy these conditions (pseudocode): (currentDate - 1 year < documentDate < currentDate) AND (documentDate - 1 year < lastAnalysisDate < documentDate)

The second condition (in italics) is what I'm having trouble with. I do not know how to make the query reference the value of the documentDate field.

Here is what I tried so far:

  • documentDate:[now-365d TO now] AND lastAnalysisDate:[documentDate-365d TO documentDate] => 0 results returned (it should be thousands)
  • documentDate:[now-365d TO now] AND lastAnalysisDate:[doc['documentDate'].value-365d TO doc['documentDate'].value] => invalid query
  • documentDate:[now-365d TO now] AND lastAnalysisDate:[doc['documentDate'].date-365d TO doc['documentDate'].date] => invalid query

Grafana only supports Lucene syntax for Elasticsearch, so I cannot use the query DSL.

Is there any way I can do this or it's not possible?

Thank you in advance!

grafana elasticsearch lucene
1 Answers

I was able to get it working by creating an alias in Elasticsearch, like below:

POST _aliases
{
  "actions": [
    {
      "add": {
        "index": "myIndex",
        "alias": "myAlias",
        "filter": {
          "bool": {
            "must": [
              {
                "query_string": {
                  "query": "documentDate:[now-365d TO now]"
                }
              },
              {
                "bool": {
                  "should": [
                    {
                      "script": {
                        "script": {
                          "source": "doc['lastAnalysisDate'].value.toInstant().toEpochMilli() >= doc['documentDate'].value.minusYears(1).toInstant().toEpochMilli() && doc['lastAnalysisDate'].value.toInstant().toEpochMilli() <= doc['documentDate'].value.toInstant().toEpochMilli()"
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      }
    }
  ]
}

Then in Grafana I created an Elasticsearch datasource pointing to the alias. In my dashboard I use this datasource with an empty query since all the filtering is done by the alias.

user_18610010
Related