Calculating sum of nested fields with date_histogram aggregation

Elastic Stack Elasticsearch
1

Hello,

I have a mapping that looks like this:

"client" : {
// various irrelevant stuff here...

"associated_transactions" : {
"type" : "nested",
"include_in_parent" : true,
"properties" : {
"amount" : {
"type" : "double"
},
"effective_at" : {
"type" : "date",
"format" : "dateOptionalTime"
}
}
}
}

I'm trying to get a date_histogram that shows total revenue across all
clients--i.e. a time series showing the sum associated_transactions.amount
in a histogram determined by associated_transactions.effective_date. I
tried running this query:

{
"query": {
// ...
},
"aggregations": {
"revenue": {
"date_histogram": {
"interval": "month",
"min_doc_count": 0,
"field": "associated_transactions.effective_at"
},
"aggs": {
"monthly_revenue": {
"sum": {
"field": "associated_transactions.amount"
}
}
}
}
}
}

But the sum it's giving me isn't right. It seems that what ES is doing is
finding all clients who have any transaction in a given month, then summing
all of the transactions (from any time) for any client who made a purchase
in a given month. That is, it's a *sum of the amount spent in the lifetime
*of a client who made a purchase in a given month, not the sum of
purchases in a give month.

Is there any way to get the data I'm looking for, or is this a limitation
in how ES handles nested fields?

Thanks very much in advance for your help!

David

--
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/b954b9b5-49dd-4ccb-9e38-7321acfd2623%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

2

Indeed, your aggregation runs in the context of the root document. You need
to use a nested aggregation to tell Elasticsearch to use your nested field
as a context:

"aggs": {
"transactions": {
"nested": {
"path": "associated_transactions"
},
"aggs": {
"revenue": {
"date_histogram": {
"interval": "month",
"min_doc_count": 0,
"field": "associated_transactions.effective_at"
},
"aggs": {
"monthly_revenue": {
"sum": {
"field": "associated_transactions.amount"
}
}
}
}
}
}
}

See

for more information.

On Wed, May 28, 2014 at 6:34 PM, David david@carom.io wrote:

Hello,

I have a mapping that looks like this:

"client" : {
// various irrelevant stuff here...

"associated_transactions" : {
"type" : "nested",
"include_in_parent" : true,
"properties" : {
"amount" : {
"type" : "double"
},
"effective_at" : {
"type" : "date",
"format" : "dateOptionalTime"
}
}
}
}

I'm trying to get a date_histogram that shows total revenue across all
clients--i.e. a time series showing the sum associated_transactions.amount
in a histogram determined by associated_transactions.effective_date. I
tried running this query:

{
"query": {
// ...
},
"aggregations": {
"revenue": {
"date_histogram": {
"interval": "month",
"min_doc_count": 0,
"field": "associated_transactions.effective_at"
},
"aggs": {
"monthly_revenue": {
"sum": {
"field": "associated_transactions.amount"
}
}
}
}
}
}

But the sum it's giving me isn't right. It seems that what ES is doing is
finding all clients who have any transaction in a given month, then summing
all of the transactions (from any time) for any client who made a purchase
in a given month. That is, it's a *sum of the amount spent in the
lifetime *of a client who made a purchase in a given month, not the sum
of purchases in a give month.

Is there any way to get the data I'm looking for, or is this a limitation
in how ES handles nested fields?

Thanks very much in advance for your help!

David

--
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/b954b9b5-49dd-4ccb-9e38-7321acfd2623%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/b954b9b5-49dd-4ccb-9e38-7321acfd2623%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
Adrien Grand

--
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/CAL6Z4j610B1G%3D2ouZcYOGuEeyBUXaXrZKd%2BLpKWRNQyYQq%3D3Vg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.