Hello -
I have a an index with 30M documents, spread across 4 nodes, 30 shards,
with 2 replicas each. Each document has a nested object, representing a
transaction log entry. Each transaction log entry has a positive or
negative dollar value and a timestamp. When I attempt to bucket the nested
objects using the date range and sum aggregations, the sum aggregation
appears to break on transactions with both positive and negative numbers.
For example, I use the follow query. I am including a reverse nested agg to
show that the nested objects are different than the output of the sum agg.
Enter code here
"size": 0,
"query": {
"filtered": {
"query": {
"match_all": {}
},
"filter": {
"bool": {
"must": [
{
"term": {
"foo._id": "12345"
}
},
{
"range": {
"timestamp": {
"gte": "2011-10-01"
}
}
},
{
"term": {
"bar.name": "A Special Name"
}
}
]
}
}
}
},
"aggs": {
"BAR": {
"terms": {
"field": "bar.name",
"size": 2,
"order": {
"NESTED>TOTAL": "asc"
}
},
"aggs": {
"NESTED": {
"nested": {
"path": "a"
},
"aggs": {
"TOTAL": {
"sum": {
"field": "a.obligatedamount"
}
},
"DATES": {
"date_range": {
"field": "a.signeddate",
"keyed": true,
"ranges": [
{
"key": "FY2012",
"from": "2011-10-01",
"to": "2012-09-30"
},
{
"key": "FY2013",
"from": "2012-10-01",
"to": "2013-09-30"
},
{
"key": "FY2014",
"from": "2013-10-01",
"to": "2014-09-30"
},
{
"key": "FY2015",
"from": "2014-10-01",
"to": "2015-09-30"
}
]
},
"aggs": {
"DATEBUCKET_SUBTOTAL": {
"sum": {
"field": "a.obligatedamount"
}
},
"HITS_REVERSE" : {
"reverse_nested": {},
"aggs": {
"HITS": {
"top_hits": {
"_source": {
"include": [
"a.obligatedamount",
"a.signeddate"
]
}
}
}
}
}
}
}
}
}
}
}
}
}...
The following result is returned. Note the odd number in the
DATEBUCKET_SUBTOTAL in FY2012 for "A Special Place". This appears to only
be an issue when the transaction log contains both negative and positive
numbers.
{
"took": 11,
"timed_out": false,
"_shards": {
"total": 30,
"successful": 30,
"failed": 0
},
"hits": {
"total": 2,
"max_score": 0,
"hits": []
},
"aggregations": {
"BAR": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "A Special Place",
"doc_count": 2,
"NESTED": {
"doc_count": 3,
"DATES": {
"buckets": {
"FY2012": {
"from": 1317427200000,
"from_as_string": "2011-10-01T00:00:00.000Z",
"to": 1348963200000,
"to_as_string": "2012-09-30T00:00:00.000Z",
"doc_count": 1,
"HITS_REVERSE": {
"doc_count": 1,
"HITS": {
"hits": {
"total": 1,
"max_score": 1,
"hits": [
{
"_index": "myIndex",
"_type": "myType",
"_id": "AG9J61P110025",
"_score": 1,
"_source": {
"a": [
{
"signeddate":
"2011-02-01T00:00:00+0000",
"obligatedamount": 4000
},
{
"signeddate":
"2012-07-11T00:00:00+0000",
"obligatedamount": -1694
}
]
}
}
]
}
}
},
"DATEBUCKET_SUBTOTAL": {
"value": -8.365e-321 <<-- This appears to be
incorrect. Shouldn't it be 2306?
}
},
"FY2013": {
"from": 1349049600000,
"from_as_string": "2012-10-01T00:00:00.000Z",
"to": 1380499200000,
"to_as_string": "2013-09-30T00:00:00.000Z",
"doc_count": 0,
"HITS_REVERSE": {
"doc_count": 0,
"HITS": {
"hits": {
"total": 0,
"max_score": null,
"hits": []
}
}
},
"DATEBUCKET_SUBTOTAL": {
"value": 0
}
},
"FY2014": {
"from": 1380585600000,
"from_as_string": "2013-10-01T00:00:00.000Z",
"to": 1412035200000,
"to_as_string": "2014-09-30T00:00:00.000Z",
"doc_count": 1,
"HITS_REVERSE": {
"doc_count": 1,
"HITS": {
"hits": {
"total": 1,
"max_score": 1,
"hits": [
{
"_index": "myIndex",
"_type": "myType",
"_id": "AG04GGP140011",
"_score": 1,
"_source": {
"a": [
{
"signeddate":
"2013-12-19T00:00:00+0000",
"obligatedamount": 3449
}
]
}
}
]
}
}
},
"DATEBUCKET_SUBTOTAL": {
"value": 3449
}
},
"FY2015": {
"from": 1412121600000,
"from_as_string": "2014-10-01T00:00:00.000Z",
"to": 1443571200000,
"to_as_string": "2015-09-30T00:00:00.000Z",
"doc_count": 0,
"HITS_REVERSE": {
"doc_count": 0,
"HITS": {
"hits": {
"total": 0,
"max_score": null,
"hits": []
}
}
},
"DATEBUCKET_SUBTOTAL": {
"value": 0
}
}
}
},
"TOTAL": {
"value": 3449
}
}
}
]
}
}
}..
My mapping looks like so:
{
"myIndex": {
"mappings": {
"myType": {
"dynamic_templates": [
{
"basestring": {
"mapping": {
"index": "not_analyzed"
},
"match": "*",
"match_mapping_type": "string"
}
}
],
"properties": {
"a": {
"type": "nested",
"properties": {
"obligatedamount": {
"type": "double"
},
"signeddate": {
"type": "date",
"format": "dateOptionalTime"
}
}
},
"bar": {
"properties": {
"_id": {
"type": "string",
"index": "not_analyzed"
},
"name": {
"type": "string",
"index": "not_analyzed"
},
"type": {
"type": "string",
"index": "not_analyzed"
}
}
},
"timestamp": {
"type": "date",
"format": "dateOptionalTime"
},
"foo": {
"properties": {
"_id": {
"type": "string",
"index": "not_analyzed"
},
"name": {
"type": "string",
"index": "not_analyzed"
},
"type": {
"type": "string",
"index": "not_analyzed"
}
}
}
}
}
}
}
}
Many thanks in advance for any help you can provide.
--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/437b01fb-8a9e-4bcd-b047-6ed568cea0ab%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.