Grafana Elasticsearch - Query condition that references field value

Grafana Elasticsearch
1

Hi all. I already asked this question on Stackoverflow, but didn’t get any responses. I’m hoping someone here can point me in the right direction, or tell me if it’s not possible.

What I want to achieve is, 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 build a Grafana dashboard that contains all documents that satisfy the following 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!