Elasticsearch aggregation that compares document field to bucket key for multiple buckets
<p>I have an index of orders. Each document in the index contains a date that the order was completed. I am trying to build an aggregation that gives me the historical work in progress (wip) for a date histogram aggregation. The wip is calculated by comparing the completed date with each date in the date histogram. If the completed date is > current bucket date then the order is considered in progress and should be included in the bucket. </p>

<p>From my research the best I can determine is that a date_histogram using a value script would give me the results I need. However I can't figure out how to structure my script.</p>

<p>Currently my query looks like this:</p>

<pre><code>{
    "query": {
        "match_all": {}
    },
    "aggs": {
        "wip": {
            "date_histogram": {
                "field": "com_ord_created_ddate",
                "script": "doc['com_ord_completed_ddate'] &gt; _value",
                "interval": "day",
                "format": "yyyy-MM-dd"
            }
        }
    }
}
</code></pre>

<p>This query returns the following exception</p>

<pre><code>{
  "error": {
    "root_cause": [
      {
        "type": "script_exception",
        "reason": "failed to run inline script [doc['com_ord_completed_ddate'] &gt; _value] using lang [groovy]"
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "orders",
        "node": "eYYqpuNSQ0KOt04JEztTDg",
        "reason": {
          "type": "script_exception",
          "reason": "failed to run inline script [doc['com_ord_completed_ddate'] &gt; _value] using lang [groovy]",
          "caused_by": {
            "type": "groovy_runtime_exception",
            "reason": "Cannot compare org.elasticsearch.index.fielddata.ScriptDocValues$Longs with value '[1445864743000]' and java.lang.Long with value '1,445,618,646,000'"
          }
        }
      }
    ]
  },
  "status": 500
}
</code></pre>

<p>I know script is written poorly. I have not been able to find documentation that clearly outlines available variables inside the script scope. I got _value from a qbox tutorial here <a href="https://qbox.io/blog/elasticsearch-scripting-aggregations" rel="nofollow">https://qbox.io/blog/elasticsearch-scripting-aggregations</a>
But not enough is said about what _value is and what other variables are available for operating on.</p>

<p>Can anyone point me to clear documentation on aggregation inline value scripting or can they help me by providing a script that would get the results that I need?</p>

<p><strong>UPDATE</strong>
I was able to get the first part of my question using this:</p>

<pre><code>{
    "query": {
        "match_all": {}
    },
    "aggs": {
        "wip": {
            "date_histogram": {
                "field": "com_ord_created_ddate",
                "script": "if(_value &lt; doc['com_ord_completed_ddate'].value) {_value} else {0}",
                "interval": "day",
                "format": "yyyy-MM-dd"
            }
        }
    }
}
</code></pre>

<p>However the script is limited to comparing documents already aggregated to the bucket. I need to compare all documents in the result for every bucket. Any thoughts?</p>