How to add IF expression to Aggregation query?

Elastic Stack Elasticsearch
1

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.

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

Tag structure:

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

Not yet clear what you want to accomplish.

First:

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

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?

3

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.

4

Add a range query.

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

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.

1 Like
5

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.

6

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.

7

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.