Get number of days in a month to aggregate data

Elastic Stack Elasticsearch
1

I am trying to get the number of days of a month from a timestamp field, and then, divide the one numeric field by this number of days.
Right now, my implementation is like this:

{
    "query" : {...},
    "script_fields": {
        "days_of_month": {
            "script": {
                "source": "Calendar cal = Calendar.getInstance(); cal.setTimeInMillis(doc['timestamp'].value); return cal.getActualMaximum(Calendar.DAY_OF_MONTH);"
            }
        }
    },
    "aggregations": {...}
}

My intention is to create a new field named days_of_month and then later put it in bucket_script params to use in aggregation part. But the result does not returns any days_of_month.
If I put the script_fields under aggregation then it the server can't parse the json.

Any idea to implement this kind of aggregation would be very much appreciated.

Thank you!

UPDATE:
Since I set the size as 0 because I only care about the aggregation result so the days_of_month is missing. Set the size as 1 and this will return the number of days in the month.

2

is there any chance you can use a script in aggregation (like terms) to figure out the right logic to bucket.. maybe you can explain what you are after in more detail.

3

@spinscale Thanks Alex, I figure out what I am missing. Since I set the size as 0 (I use result in aggregation only) so that scripted field is missing. After set the size as 1, it comes back as expected.

In aggregation part, I group data daily in a month. I already have the _source.month_price so scripted field above is for division to get daily price. After that, I will multiply the field extra_usage with the daily_price to have daily daily charge. That is what I am after.

However, scripted field is not able to be reused in aggregation. Hence, there will be duplicate calculation for the value of the scrpited field if I put the whole script (get days of a month) in aggregation.

Maybe I have to do it in the client side. :slight_smile:

Cheers!

4

it seems to me, that you could do that scripting calculation as part of the aggregation without any need of a scripted field

5

yes Alex, but if the script as a part of aggregation then the result will be calculated for many times, depending on the hit bucket size, leading to duplication.
That is why I want it to be calculated once only and then use it in aggregation later but it seems to be a challenge.

6

I am not sure I understand the last post. A script field will also be executed for every hit.

7

Really? I notice in the result json, there is only one section for field script if I put it outside aggregation. If I put it inside aggregation, it will return the same number of output as aggregation hits.
So I assume that putting field script outside of the aggregation section, it will be executed once only.

Is that so?

        "days_of_month": {
            "script": {
                "source": "Calendar cal = Calendar.getInstance(); cal.setTimeInMillis(doc['timestamp'].value); return cal.getActualMaximum(Calendar.DAY_OF_MONTH);"
            }
        }
    },
    "aggregations": {...}

//Inside aggregation

    "aggregations": {
        "script_fields": {
                "days_of_month": {
                     "script": {
                        "source": "Calendar cal = Calendar.getInstance();cal.setTimeInMillis(doc['timestamp'].value); return cal.getActualMaximum(Calendar.DAY_OF_MONTH);"
                    }
                }
          },...
}
8

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