# How to add IF expression to Aggregation query?

**URL:** <https://discuss.elastic.co/t/how-to-add-if-expression-to-aggregation-query/56258>\
**Category:** Elasticsearch\
**Created:** [July 25, 2016, 4:57am UTC](https://discuss.elastic.co/t/how-to-add-if-expression-to-aggregation-query/56258 "2016-07-25T04:57:26Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![NativAt](https://avatars.discourse-cdn.com/v4/letter/n/fbc32d/32.png) [@NativAt](https://discuss.elastic.co/u/NativAt)\
**Post date:** [July 25, 2016, 4:57am UTC](https://discuss.elastic.co/t/how-to-add-if-expression-to-aggregation-query/56258/1 "2016-07-25T04:57:26Z")

</div>

Hello,

I want to add IF expression to my aggregation query. so if impression field is greater than 1, than count this tagId for **every** tagId. The final result is show for every tagId his total count of impressions.

```auto
GET tags-*/_search
{
  "size": 0,
  "aggs": {
    "childTag": {
      "terms": {
        "field": "childTagId"
      }
    }
  }
}
```

Tag structure:

```auto
"_index": "tags-2016",
        "_type": "Tag",
        "_id": "AVWvR4TQuCQdSNQ5FSaE",
        "_score": 1,
        "_source": {
          "tagId": 2,
          "impression": 5,
          "@timestamp": "2016.07.02T00:35:01"
```

---

<div class="post-metadata">

**Author:** ![DiscussBuster](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/discussbuster/32/11042_2.png) [@DiscussBuster](https://discuss.elastic.co/u/DiscussBuster)\
**Post date:** [July 25, 2016, 1:40pm UTC](https://discuss.elastic.co/t/how-to-add-if-expression-to-aggregation-query/56258/2 "2016-07-25T13:40:17Z")

</div>

Not yet clear what you want to accomplish.

First:

> [@NativAt](#):
>
> so if impression field is greater than 1

If _which_ impression field is greater than 1?

Suppose before attempting anything further, your simple terms agg returns three buckets:

- a bucket that says there are 47,812 documents with "tagId" value of `2`
- a bucket that says there are 2 documents with "tagId" value of `red`
- a bucket that says there are 3 documents with "tagId" value of `json`

Suppose that, of those documents with "tagId" value 2, 40,000 of them have impression \> 1.

I'm I correct in understanding, then, that you want the buckets for `red` and `json` to report 40,002 and 40,003, respectively?

Or, based on

> [@NativAt](#):
>
> The final result is show for every tagId his total count of impressions.

are you trying to sum up all of the values in impressions within each bucket?

Or are you seeking some combination of these two things?

---

<div class="post-metadata">

**Author:** ![NativAt](https://avatars.discourse-cdn.com/v4/letter/n/fbc32d/32.png) [@NativAt](https://discuss.elastic.co/u/NativAt)\
**Post date:** [July 25, 2016, 5:04pm UTC](https://discuss.elastic.co/t/how-to-add-if-expression-to-aggregation-query/56258/3 "2016-07-25T17:04:42Z")

</div>

I want the tagId 2 will report 40,000 impression total. I want to count for every tagId his total impression. If the impression is greater than 1, it will be count (not sum) as only one. The final result is the total "count" of impressions per tagId.

---

<div class="post-metadata">

**Author:** ![Glen\_Smith](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/glen_smith/32/111656_2.png) [@Glen\_Smith](https://discuss.elastic.co/u/Glen_Smith)\
**Post date:** [July 25, 2016, 6:28pm UTC](https://discuss.elastic.co/t/how-to-add-if-expression-to-aggregation-query/56258/4 "2016-07-25T18:28:25Z")

</div>

Add a range query.

[https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html)

```auto
GET tags-*/_search
{
  "size": 0,
  "query": {
    "range": {
      "impression": {
        "gt": 1
      }
    }
  },
  "aggs": {
    "childTag": {
      "terms": {
        "field": "childTagId"
      }
    }
  }
}

```

Now only documents matching your rule will be included in the agg.

---

<div class="post-metadata">

**Author:** ![NativAt](https://avatars.discourse-cdn.com/v4/letter/n/fbc32d/32.png) [@NativAt](https://discuss.elastic.co/u/NativAt)\
**Post date:** [July 25, 2016, 7:13pm UTC](https://discuss.elastic.co/t/how-to-add-if-expression-to-aggregation-query/56258/5 "2016-07-25T19:13:31Z")

</div>

No. It's not good solution because I also want the documents with 0 impressions. The solution I'm looking for is scripting I think.

---

<div class="post-metadata">

**Author:** ![Glen\_Smith](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/glen_smith/32/111656_2.png) [@Glen\_Smith](https://discuss.elastic.co/u/Glen_Smith)\
**Post date:** [July 25, 2016, 8:28pm UTC](https://discuss.elastic.co/t/how-to-add-if-expression-to-aggregation-query/56258/6 "2016-07-25T20:28:14Z")

</div>

You would probably be better off just making two calls. One for the document population you want, another with the filter and agg as shown above.

---

<div class="post-metadata">

**Author:** ![NativAt](https://avatars.discourse-cdn.com/v4/letter/n/fbc32d/32.png) [@NativAt](https://discuss.elastic.co/u/NativAt)\
**Post date:** [July 25, 2016, 9:32pm UTC](https://discuss.elastic.co/t/how-to-add-if-expression-to-aggregation-query/56258/7 "2016-07-25T21:32:00Z")

</div>

This will make me to move the problem from the DB to the server. Is there any elegant solution out there? This is the simple task ever in MySQL.

---

<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:** [July 5, 2017, 10:33pm UTC](https://discuss.elastic.co/t/how-to-add-if-expression-to-aggregation-query/56258/8 "2017-07-05T22:33:05Z")

</div>