# Complex nested query
**URL:** https://discuss.elastic.co/t/complex-nested-query/19729
**Category:** Elasticsearch
**Created:** [September 11, 2014, 3:23am UTC](https://discuss.elastic.co/t/complex-nested-query/19729 "2014-09-11T03:23:44Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@11157](https://discuss.elastic.co/u/11157)
#### Post date: [September 11, 2014, 3:23am UTC](https://discuss.elastic.co/t/complex-nested-query/19729/1 "2014-09-11T03:23:44Z")
Dear All!
I have a problem with a complex nested query
the docs like this:
\_id:1
{
"detail":[
{
"date":"2014-09-01",
"price":50
},
{
"date":"2014-09-02",
"price":100
},
{
"date":"2014-09-03",
"price":100
},
{
"date":"2014-09-04",
"price":200
}
]
}
\_id:2
{
"detail":[
{
"date":"2014-09-01",
"price":100
},
{
"date":"2014-09-02",
"price":200
},
{
"date":"2014-09-03",
"price":300
},
{
"date":"2014-09-04",
"price":200
}
]
}
I will filter the docs with "date in [2014-09-01, 2014-09-03] and sum(price) \> 300".
I only find some way with "aggregation", but it can only stat the sum of all docs.
How Can I solve the problem??
Thanks && Best Regard!
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@vineeth\_mohan\_2](https://discuss.elastic.co/u/vineeth_mohan_2)
#### Post date: [September 11, 2014, 4:04am UTC](https://discuss.elastic.co/t/complex-nested-query/19729/2 "2014-09-11T04:04:02Z")
Hello ,
First you need to declare field details as nested. -
> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.
Then do a bool query with the date range constrain and range constrain
Thanks
Vineeth
On Thu, Sep 11, 2014 at 8:53 AM, 闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) wrote:
> Dear All!
>
> I have a problem with a complex nested query
> the docs like this:
> \_id:1
> {
> "detail":[
> {
> "date":"2014-09-01",
> "price”:50
> },
> {
> "date":"2014-09-02",
> "price”:100
> },
> {
> "date":"2014-09-03",
> "price":100
> },
> {
> "date":"2014-09-04",
> "price":200
> }
> ]
>
> }
> \_id:2
> {
> "detail":[
> {
> "date":"2014-09-01",
> "price":100
> },
> {
> "date":"2014-09-02",
> "price":200
> },
> {
> "date":"2014-09-03",
> "price":300
> },
> {
> "date":"2014-09-04",
> "price":200
> }
> ]
>
> }
> I will filter the docs with “date in [2014-09-01, 2014-09-03] and
> sum(price) \> 300”.
> I only find some way with “aggregation”, but it can only stat the sum of
> all docs.
>
> How Can I solve the problem??
>
> Thanks && Best Regard!
>
> --
> 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> To view this discussion on the web visit
> [https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com)
> [https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com?utm_medium=email&utm_source=footer)
> .
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i\_2ZxVQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i_2ZxVQ%40mail.gmail.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@11157](https://discuss.elastic.co/u/11157)
#### Post date: [September 11, 2014, 5:24am UTC](https://discuss.elastic.co/t/complex-nested-query/19729/3 "2014-09-11T05:24:02Z")
Thank you ! But nested bool query can not plus all price with the data range. how can i do this??
Thx again.
Thanks && Best Regard!
在 2014年9月11日，12:04，vineeth mohan [vm.vineethmohan@gmail.com](mailto:vm.vineethmohan@gmail.com) 写道：
> Hello ,
>
> First you need to declare field details as nested. - [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html#mapping-nested-type)
>
> Then do a bool query with the date range constrain and range constrain
>
> Thanks
> Vineeth
>
> On Thu, Sep 11, 2014 at 8:53 AM, 闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) wrote:
> Dear All!
>
> I have a problem with a complex nested query
> the docs like this:
> \_id:1
> {
> "detail":[
> {
> "date":"2014-09-01",
> "price”:50
> },
> {
> "date":"2014-09-02",
> "price”:100
> },
> {
> "date":"2014-09-03",
> "price":100
> },
> {
> "date":"2014-09-04",
> "price":200
> }
> ]
>
> }
> \_id:2
> {
> "detail":[
> {
> "date":"2014-09-01",
> "price":100
> },
> {
> "date":"2014-09-02",
> "price":200
> },
> {
> "date":"2014-09-03",
> "price":300
> },
> {
> "date":"2014-09-04",
> "price":200
> }
> ]
>
> }
> I will filter the docs with “date in [2014-09-01, 2014-09-03] and sum(price) \> 300”.
> I only find some way with “aggregation”, but it can only stat the sum of all docs.
>
> How Can I solve the problem??
>
> Thanks && Best Regard!
>
> --
> 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com).
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
>
> --
> 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i\_2ZxVQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i_2ZxVQ%40mail.gmail.com).
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/BAA9C6D6-8060-4F4E-B2F0-7764EAD2BCDB%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/BAA9C6D6-8060-4F4E-B2F0-7764EAD2BCDB%40gmail.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@11157](https://discuss.elastic.co/u/11157)
#### Post date: [September 11, 2014, 11:34am UTC](https://discuss.elastic.co/t/complex-nested-query/19729/4 "2014-09-11T11:34:34Z")
Anyone can help this?
Thanks && Best Regard!
在 2014年9月11日，13:24，闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) 写道：
> Thank you ! But nested bool query can not plus all price with the data range. how can i do this??
>
> Thx again.
>
> Thanks && Best Regard!
>
> 在 2014年9月11日，12:04，vineeth mohan [vm.vineethmohan@gmail.com](mailto:vm.vineethmohan@gmail.com) 写道：
>
> > Hello ,
> >
> > First you need to declare field details as nested. - [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html#mapping-nested-type)
> >
> > Then do a bool query with the date range constrain and range constrain
> >
> > Thanks
> > Vineeth
> >
> > On Thu, Sep 11, 2014 at 8:53 AM, 闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) wrote:
> > Dear All!
> >
> > I have a problem with a complex nested query
> > the docs like this:
> > \_id:1
> > {
> > "detail":[
> > {
> > "date":"2014-09-01",
> > "price”:50
> > },
> > {
> > "date":"2014-09-02",
> > "price”:100
> > },
> > {
> > "date":"2014-09-03",
> > "price":100
> > },
> > {
> > "date":"2014-09-04",
> > "price":200
> > }
> > ]
> >
> > }
> > \_id:2
> > {
> > "detail":[
> > {
> > "date":"2014-09-01",
> > "price":100
> > },
> > {
> > "date":"2014-09-02",
> > "price":200
> > },
> > {
> > "date":"2014-09-03",
> > "price":300
> > },
> > {
> > "date":"2014-09-04",
> > "price":200
> > }
> > ]
> >
> > }
> > I will filter the docs with “date in [2014-09-01, 2014-09-03] and sum(price) \> 300”.
> > I only find some way with “aggregation”, but it can only stat the sum of all docs.
> >
> > How Can I solve the problem??
> >
> > Thanks && Best Regard!
> >
> > --
> > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com).
> > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
> >
> > --
> > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i\_2ZxVQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i_2ZxVQ%40mail.gmail.com).
> > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@vineeth\_mohan\_2](https://discuss.elastic.co/u/vineeth_mohan_2)
#### Post date: [September 11, 2014, 4:12pm UTC](https://discuss.elastic.co/t/complex-nested-query/19729/5 "2014-09-11T16:12:03Z")
Hello ,
I don't feel you can do this in a single call.
What i have in mind would be
1. Run a two level aggregation query with date histogram aggregation on
first level with date and term aggregation on second with sum aggregation
on prize field on second level. You might need to use nested aggregation
also here.
2. Once you get the results , choose the dates based on the criteria ,
i.e. sum of prize more than 300. With the dates you are interested in ,
fire the next query which has all interesting date ranges as range query.
Thanks
Vineeth
On Thu, Sep 11, 2014 at 5:04 PM, 闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) wrote:
> Anyone can help this?
>
> Thanks && Best Regard!
>
> 在 2014年9月11日，13:24，闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) 写道：
>
> Thank you ! But nested bool query can not plus all price with the data
> range. how can i do this??
>
> Thx again.
>
> Thanks && Best Regard!
>
> 在 2014年9月11日，12:04，vineeth mohan [vm.vineethmohan@gmail.com](mailto:vm.vineethmohan@gmail.com) 写道：
>
> Hello ,
>
> First you need to declare field details as nested. -
> [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html#mapping-nested-type)
>
> Then do a bool query with the date range constrain and range constrain
>
> Thanks
> Vineeth
>
> On Thu, Sep 11, 2014 at 8:53 AM, 闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) wrote:
>
> > Dear All!
> >
> > I have a problem with a complex nested query
> > the docs like this:
> > \_id:1
> > {
> > "detail":[
> > {
> > "date":"2014-09-01",
> > "price”:50
> > },
> > {
> > "date":"2014-09-02",
> > "price”:100
> > },
> > {
> > "date":"2014-09-03",
> > "price":100
> > },
> > {
> > "date":"2014-09-04",
> > "price":200
> > }
> > ]
> >
> > }
> > \_id:2
> > {
> > "detail":[
> > {
> > "date":"2014-09-01",
> > "price":100
> > },
> > {
> > "date":"2014-09-02",
> > "price":200
> > },
> > {
> > "date":"2014-09-03",
> > "price":300
> > },
> > {
> > "date":"2014-09-04",
> > "price":200
> > }
> > ]
> >
> > }
> > I will filter the docs with “date in [2014-09-01, 2014-09-03] and
> > sum(price) \> 300”.
> > I only find some way with “aggregation”, but it can only stat the sum of
> > all docs.
> >
> > How Can I solve the problem??
> >
> > Thanks && Best Regard!
> >
> > --
> > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > To view this discussion on the web visit
> > [https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com)
> > [https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com?utm_medium=email&utm_source=footer)
> > .
> > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
>
> --
> 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> To view this discussion on the web visit
> [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i\_2ZxVQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i_2ZxVQ%40mail.gmail.com)
> [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i\_2ZxVQ%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i_2ZxVQ%40mail.gmail.com?utm_medium=email&utm_source=footer)
> .
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
>
> --
> 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> To view this discussion on the web visit
> [https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com)
> [https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com?utm_medium=email&utm_source=footer)
> .
>
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5mH%2BSrgrkomvNh9-a-A5gDTjeXCO6DE8uWp32ruNbGPFA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5mH%2BSrgrkomvNh9-a-A5gDTjeXCO6DE8uWp32ruNbGPFA%40mail.gmail.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@11157](https://discuss.elastic.co/u/11157)
#### Post date: [September 11, 2014, 5:18pm UTC](https://discuss.elastic.co/t/complex-nested-query/19729/6 "2014-09-11T17:18:43Z")
Vineeth！
Thanks for your reply！ I’ll try your solution,hope this can solve my problem.
Thanks && Best Regard!
在 2014年9月12日，0:12，vineeth mohan [vm.vineethmohan@gmail.com](mailto:vm.vineethmohan@gmail.com) 写道：
> Hello ,
>
> I don't feel you can do this in a single call.
> What i have in mind would be
>
> Run a two level aggregation query with date histogram aggregation on first level with date and term aggregation on second with sum aggregation on prize field on second level. You might need to use nested aggregation also here.
> Once you get the results , choose the dates based on the criteria , i.e. sum of prize more than 300. With the dates you are interested in , fire the next query which has all interesting date ranges as range query.
> Thanks
> Vineeth
>
> On Thu, Sep 11, 2014 at 5:04 PM, 闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) wrote:
> Anyone can help this?
>
> Thanks && Best Regard!
>
> 在 2014年9月11日，13:24，闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) 写道：
>
> > Thank you ! But nested bool query can not plus all price with the data range. how can i do this??
> >
> > Thx again.
> >
> > Thanks && Best Regard!
> >
> > 在 2014年9月11日，12:04，vineeth mohan [vm.vineethmohan@gmail.com](mailto:vm.vineethmohan@gmail.com) 写道：
> >
> > > Hello ,
> > >
> > > First you need to declare field details as nested. - [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html#mapping-nested-type)
> > >
> > > Then do a bool query with the date range constrain and range constrain
> > >
> > > Thanks
> > > Vineeth
> > >
> > > On Thu, Sep 11, 2014 at 8:53 AM, 闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) wrote:
> > > Dear All!
> > >
> > > I have a problem with a complex nested query
> > > the docs like this:
> > > \_id:1
> > > {
> > > "detail":[
> > > {
> > > "date":"2014-09-01",
> > > "price”:50
> > > },
> > > {
> > > "date":"2014-09-02",
> > > "price”:100
> > > },
> > > {
> > > "date":"2014-09-03",
> > > "price":100
> > > },
> > > {
> > > "date":"2014-09-04",
> > > "price":200
> > > }
> > > ]
> > >
> > > }
> > > \_id:2
> > > {
> > > "detail":[
> > > {
> > > "date":"2014-09-01",
> > > "price":100
> > > },
> > > {
> > > "date":"2014-09-02",
> > > "price":200
> > > },
> > > {
> > > "date":"2014-09-03",
> > > "price":300
> > > },
> > > {
> > > "date":"2014-09-04",
> > > "price":200
> > > }
> > > ]
> > >
> > > }
> > > I will filter the docs with “date in [2014-09-01, 2014-09-03] and sum(price) \> 300”.
> > > I only find some way with “aggregation”, but it can only stat the sum of all docs.
> > >
> > > How Can I solve the problem??
> > >
> > > Thanks && Best Regard!
> > >
> > > --
> > > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com).
> > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
> > >
> > > --
> > > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i\_2ZxVQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i_2ZxVQ%40mail.gmail.com).
> > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
>
> --
> 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com).
>
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
>
> --
> 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5mH%2BSrgrkomvNh9-a-A5gDTjeXCO6DE8uWp32ruNbGPFA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5mH%2BSrgrkomvNh9-a-A5gDTjeXCO6DE8uWp32ruNbGPFA%40mail.gmail.com).
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CA49FB1A-E87E-4CB0-A5CF-355EF9C0147F%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/CA49FB1A-E87E-4CB0-A5CF-355EF9C0147F%40gmail.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@11157](https://discuss.elastic.co/u/11157)
#### Post date: [September 12, 2014, 2:47am UTC](https://discuss.elastic.co/t/complex-nested-query/19729/7 "2014-09-12T02:47:28Z")
Hi!
I can not find how to filter date based on aggregation result in documents. btw, can i use function do this？
Thanks && Best Regard!
在 2014年9月12日，1:18，yancey [yancey1989@gmail.com](mailto:yancey1989@gmail.com) 写道：
> Vineeth！
>
> Thanks for your reply！ I’ll try your solution,hope this can solve my problem.
>
> Thanks && Best Regard!
>
> 在 2014年9月12日，0:12，vineeth mohan [vm.vineethmohan@gmail.com](mailto:vm.vineethmohan@gmail.com) 写道：
>
> > Hello ,
> >
> > I don't feel you can do this in a single call.
> > What i have in mind would be
> >
> > Run a two level aggregation query with date histogram aggregation on first level with date and term aggregation on second with sum aggregation on prize field on second level. You might need to use nested aggregation also here.
> > Once you get the results , choose the dates based on the criteria , i.e. sum of prize more than 300. With the dates you are interested in , fire the next query which has all interesting date ranges as range query.
> > Thanks
> > Vineeth
> >
> > On Thu, Sep 11, 2014 at 5:04 PM, 闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) wrote:
> > Anyone can help this?
> >
> > Thanks && Best Regard!
> >
> > 在 2014年9月11日，13:24，闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) 写道：
> >
> > > Thank you ! But nested bool query can not plus all price with the data range. how can i do this??
> > >
> > > Thx again.
> > >
> > > Thanks && Best Regard!
> > >
> > > 在 2014年9月11日，12:04，vineeth mohan [vm.vineethmohan@gmail.com](mailto:vm.vineethmohan@gmail.com) 写道：
> > >
> > > > Hello ,
> > > >
> > > > First you need to declare field details as nested. - [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html#mapping-nested-type)
> > > >
> > > > Then do a bool query with the date range constrain and range constrain
> > > >
> > > > Thanks
> > > > Vineeth
> > > >
> > > > On Thu, Sep 11, 2014 at 8:53 AM, 闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) wrote:
> > > > Dear All!
> > > >
> > > > I have a problem with a complex nested query
> > > > the docs like this:
> > > > \_id:1
> > > > {
> > > > "detail":[
> > > > {
> > > > "date":"2014-09-01",
> > > > "price”:50
> > > > },
> > > > {
> > > > "date":"2014-09-02",
> > > > "price”:100
> > > > },
> > > > {
> > > > "date":"2014-09-03",
> > > > "price":100
> > > > },
> > > > {
> > > > "date":"2014-09-04",
> > > > "price":200
> > > > }
> > > > ]
> > > >
> > > > }
> > > > \_id:2
> > > > {
> > > > "detail":[
> > > > {
> > > > "date":"2014-09-01",
> > > > "price":100
> > > > },
> > > > {
> > > > "date":"2014-09-02",
> > > > "price":200
> > > > },
> > > > {
> > > > "date":"2014-09-03",
> > > > "price":300
> > > > },
> > > > {
> > > > "date":"2014-09-04",
> > > > "price":200
> > > > }
> > > > ]
> > > >
> > > > }
> > > > I will filter the docs with “date in [2014-09-01, 2014-09-03] and sum(price) \> 300”.
> > > > I only find some way with “aggregation”, but it can only stat the sum of all docs.
> > > >
> > > > How Can I solve the problem??
> > > >
> > > > Thanks && Best Regard!
> > > >
> > > > --
> > > > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > > > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com).
> > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
> > > >
> > > > --
> > > > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > > > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i\_2ZxVQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i_2ZxVQ%40mail.gmail.com).
> > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
> >
> > --
> > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com).
> >
> > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
> >
> > --
> > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5mH%2BSrgrkomvNh9-a-A5gDTjeXCO6DE8uWp32ruNbGPFA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5mH%2BSrgrkomvNh9-a-A5gDTjeXCO6DE8uWp32ruNbGPFA%40mail.gmail.com).
> > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/98C8607A-47D3-4DCA-A74B-901E5F6152E1%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/98C8607A-47D3-4DCA-A74B-901E5F6152E1%40gmail.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@vineeth\_mohan\_2](https://discuss.elastic.co/u/vineeth_mohan_2)
#### Post date: [September 12, 2014, 2:56am UTC](https://discuss.elastic.co/t/complex-nested-query/19729/8 "2014-09-12T02:56:58Z")
Hello Yancey ,
1. Date aggregation -
[Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html#search-aggregations-bucket-datehistogram-aggregation)
2. Sum aggregation -
[Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-metrics-sum-aggregation.html)
3. Nested aggregation -
[Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-bucket-nested-aggregation.html)
Thanks
Vineeth
On Fri, Sep 12, 2014 at 8:17 AM, yancey [yancey1989@gmail.com](mailto:yancey1989@gmail.com) wrote:
> Hi!
> I can not find how to filter date based on aggregation result in
> documents. btw, can i use function do this？
>
> Thanks && Best Regard!
>
> 在 2014年9月12日，1:18，yancey [yancey1989@gmail.com](mailto:yancey1989@gmail.com) 写道：
>
> Vineeth！
>
> Thanks for your reply！ I’ll try your solution,hope this can solve my
> problem.
>
> Thanks && Best Regard!
>
> 在 2014年9月12日，0:12，vineeth mohan [vm.vineethmohan@gmail.com](mailto:vm.vineethmohan@gmail.com) 写道：
>
> Hello ,
>
> I don't feel you can do this in a single call.
> What i have in mind would be
>
> 1. Run a two level aggregation query with date histogram aggregation
> on first level with date and term aggregation on second with sum
> aggregation on prize field on second level. You might need to use nested
> aggregation also here.
> 2. Once you get the results , choose the dates based on the criteria ,
> i.e. sum of prize more than 300. With the dates you are interested in ,
> fire the next query which has all interesting date ranges as range query.
>
> Thanks
> Vineeth
>
> On Thu, Sep 11, 2014 at 5:04 PM, 闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) wrote:
>
> > Anyone can help this?
> >
> > Thanks && Best Regard!
> >
> > 在 2014年9月11日，13:24，闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) 写道：
> >
> > Thank you ! But nested bool query can not plus all price with the data
> > range. how can i do this??
> >
> > Thx again.
> >
> > Thanks && Best Regard!
> >
> > 在 2014年9月11日，12:04，vineeth mohan [vm.vineethmohan@gmail.com](mailto:vm.vineethmohan@gmail.com) 写道：
> >
> > Hello ,
> >
> > First you need to declare field details as nested. -
> > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-nested-type.html#mapping-nested-type)
> >
> > Then do a bool query with the date range constrain and range constrain
> >
> > Thanks
> > Vineeth
> >
> > On Thu, Sep 11, 2014 at 8:53 AM, 闫旭 [yancey1989@gmail.com](mailto:yancey1989@gmail.com) wrote:
> >
> > > Dear All!
> > >
> > > I have a problem with a complex nested query
> > > the docs like this:
> > > \_id:1
> > > {
> > > "detail":[
> > > {
> > > "date":"2014-09-01",
> > > "price”:50
> > > },
> > > {
> > > "date":"2014-09-02",
> > > "price”:100
> > > },
> > > {
> > > "date":"2014-09-03",
> > > "price":100
> > > },
> > > {
> > > "date":"2014-09-04",
> > > "price":200
> > > }
> > > ]
> > >
> > > }
> > > \_id:2
> > > {
> > > "detail":[
> > > {
> > > "date":"2014-09-01",
> > > "price":100
> > > },
> > > {
> > > "date":"2014-09-02",
> > > "price":200
> > > },
> > > {
> > > "date":"2014-09-03",
> > > "price":300
> > > },
> > > {
> > > "date":"2014-09-04",
> > > "price":200
> > > }
> > > ]
> > >
> > > }
> > > I will filter the docs with “date in [2014-09-01, 2014-09-03] and
> > > sum(price) \> 300”.
> > > I only find some way with “aggregation”, but it can only stat the sum of
> > > all docs.
> > >
> > > How Can I solve the problem??
> > >
> > > Thanks && Best Regard!
> > >
> > > --
> > > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > > To view this discussion on the web visit
> > > [https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com)
> > > [https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/A98354E4-9C9F-43B2-9310-6355DE3D6F85%40gmail.com?utm_medium=email&utm_source=footer)
> > > .
> > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
> >
> > --
> > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > To view this discussion on the web visit
> > [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i\_2ZxVQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i_2ZxVQ%40mail.gmail.com)
> > [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i\_2ZxVQ%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfRarPNNBctvYfHsk52tjD2rxv18aQGqq3Hz0i_2ZxVQ%40mail.gmail.com?utm_medium=email&utm_source=footer)
> > .
> > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
> >
> > --
> > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > To view this discussion on the web visit
> > [https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com)
> > [https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/F5848899-E506-470B-AA05-E6A2B1965986%40gmail.com?utm_medium=email&utm_source=footer)
> > .
> >
> > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
>
> --
> 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> To view this discussion on the web visit
> [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5mH%2BSrgrkomvNh9-a-A5gDTjeXCO6DE8uWp32ruNbGPFA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5mH%2BSrgrkomvNh9-a-A5gDTjeXCO6DE8uWp32ruNbGPFA%40mail.gmail.com)
> [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5mH%2BSrgrkomvNh9-a-A5gDTjeXCO6DE8uWp32ruNbGPFA%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5mH%2BSrgrkomvNh9-a-A5gDTjeXCO6DE8uWp32ruNbGPFA%40mail.gmail.com?utm_medium=email&utm_source=footer)
> .
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
>
> --
> 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> To view this discussion on the web visit
> [https://groups.google.com/d/msgid/elasticsearch/98C8607A-47D3-4DCA-A74B-901E5F6152E1%40gmail.com](https://groups.google.com/d/msgid/elasticsearch/98C8607A-47D3-4DCA-A74B-901E5F6152E1%40gmail.com)
> [https://groups.google.com/d/msgid/elasticsearch/98C8607A-47D3-4DCA-A74B-901E5F6152E1%40gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/98C8607A-47D3-4DCA-A74B-901E5F6152E1%40gmail.com?utm_medium=email&utm_source=footer)
> .
>
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5maa-ubX%3DgOuM\_ghOe\_AG2oywJo5kfYiM6rekXDY6UVhA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5maa-ubX%3DgOuM_ghOe_AG2oywJo5kfYiM6rekXDY6UVhA%40mail.gmail.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 1:02am UTC](https://discuss.elastic.co/t/complex-nested-query/19729/9 "2017-07-06T01:02:56Z")