# Sum nested values while applying match conditions

**URL:** https://discuss.elastic.co/t/sum-nested-values-while-applying-match-conditions/108032
**Category:** Elasticsearch
**Created:** [November 16, 2017, 10:29pm UTC](https://discuss.elastic.co/t/sum-nested-values-while-applying-match-conditions/108032 "2017-11-16T22:29:35Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![Rory](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@Rory](https://discuss.elastic.co/u/Rory)
#### Post date: [November 16, 2017, 10:29pm UTC](https://discuss.elastic.co/t/sum-nested-values-while-applying-match-conditions/108032/1 "2017-11-16T22:29:35Z")

</div>

Hi,

I have a document which looks like:

```
{
  "comments_by_day" : [
  {
    "dateofcomment" : "2017-09-20",
    "numberofcomments" : 10 
  },
  {
    "dateofcomment" : "2017-09-24",
    "numberofcomments" : 5
  },
  {
    "dateofcomment" : "2017-09-29",
    "numberofcomments" : 9 
  },
  {
    "dateofcomment" : "2017-10-03",
    "numberofcomments" : 25 
  },
  {
    "dateofcomment" : "2017-10-07",
    "numberofcomments" : 10
  }
]
}

```

I want to write a query which will apply a date filter while summing the value in the numberofcomments field. So find users who have commented more than 10 times between 2017-09-23 and 2017-10-01.

Is it possible to do this using ElasticSearch?

Any help is highly appreciated.

Thanks.

---

<div class="post-metadata">

### Author: ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)
#### Post date: [November 17, 2017, 9:25am UTC](https://discuss.elastic.co/t/sum-nested-values-while-applying-match-conditions/108032/2 "2017-11-17T09:25:34Z")

</div>

you should take a look at [nested documents](https://www.elastic.co/guide/en/elasticsearch/reference/5.6/nested.html)

see this example

```auto
DELETE test

PUT test
{
  "mappings": {
    "doc" : {
      "properties": {
        "comments_by_day" : {
          "type": "nested"
        }
      }
    }
  }
}

PUT test/doc/1
{
  "comments_by_day": [
    {
      "dateofcomment": "2017-09-20",
      "numberofcomments": 10
    },
    {
      "dateofcomment": "2017-09-24",
      "numberofcomments": 5
    },
    {
      "dateofcomment": "2017-09-29",
      "numberofcomments": 9
    },
    {
      "dateofcomment": "2017-10-03",
      "numberofcomments": 25
    },
    {
      "dateofcomment": "2017-10-07",
      "numberofcomments": 10
    }
  ]
}

GET test/_search
{
  "size": 0,
  "aggs": {
    "foo": {
      "nested": {
        "path": "comments_by_day"
      },
      "aggs": {
        "my_filter": {
          "filter": {
            "bool": {
              "filter": {
                "range": {
                  "comments_by_day.dateofcomment": {
                    "gte": "2017-10-01"
                  }
                }
              }
            }
          },
          "aggs": {
            "sum": {
              "sum": {
                "field": "comments_by_day.numberofcomments"
              }
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

### Author: ![Rory](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@Rory](https://discuss.elastic.co/u/Rory)
#### Post date: [November 17, 2017, 1:24pm UTC](https://discuss.elastic.co/t/sum-nested-values-while-applying-match-conditions/108032/3 "2017-11-17T13:24:59Z")

</div>

Thanks for the response.

I have already indexed the comments\_by\_day field as a nested datatype. The problem I have is that there are other fields in the document as well like:

```
{
  "city" : "LONDON",
  "age" : 29,
  "comments_by_day" : [
  {
    "dateofcomment" : "2017-09-20",
    "numberofcomments" : 10 
  },
  {
    "dateofcomment" : "2017-09-24",
    "numberofcomments" : 5
  },
  {
    "dateofcomment" : "2017-09-29",
    "numberofcomments" : 9 
  },
  {
    "dateofcomment" : "2017-10-03",
    "numberofcomments" : 25 
  },
  {
    "dateofcomment" : "2017-10-07",
    "numberofcomments" : 10
  }
]
}

```

How should I modify the query that you provided, to return something like all users who live in "LONDON" and are between 25 to 35 years old and have commented more than 10 times in total between 2017-09-23 and 2017-10-01?

> [@spinscale](#):
>
> ```
> GET test/_search
> {
> "size": 0,
> "aggs": {
> "foo": {
> "nested": {
> "path": "comments_by_day"
> },
> "aggs": {
> "my_filter": {
> "filter": {
> "bool": {
> "filter": {
> "range": {
> "comments_by_day.dateofcomment": {
> "gte": "2017-10-01"
> }
> }
> }
> }
> },
> "aggs": {
> "sum": {
> "sum": {
> "field": "comments_by_day.numberofcomments"
> }
> }
> }
> }
> }
> }
> }
> }
> 
> ```

In the comments\_by\_day field there are 2 nested documents which are between 2017-09-23 and 2017-10-01 and the sum of the numberofcomments field in those 2 nested documents is 5 + 9 = 14, so this document should be returned by the query.

Thanks.

---

<div class="post-metadata">

### Author: ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)
#### Post date: [December 15, 2017, 1:25pm UTC](https://discuss.elastic.co/t/sum-nested-values-while-applying-match-conditions/108032/4 "2017-12-15T13:25:18Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.