Painless : Loop through all documents of index

Hi!

Is there a way to loop through all documents with scripted fields?

I want to divide two sums for a given day, and since dividing two aggregated values is impossible, I was thinking if there is a workaround with scripted fileds.

I am thinking of doing something like this:

double totalprice=0; 
double totalquantity=0; 
def current_day=doc['day_of_week'].value; 

for (int i=0;i< ALL DOCUMENTS ;++i) 
{ 
   if (current_day==doc['day_of_week'][i]) 
    { totalprice=totalprice+doc['products.price'][i];
      totalquantity=totalquantity+doc['products.quantity'][i];
    } 
    
}

return totalprice/totalquantity; 

Any ideas?

I do not think that is possible as scripted fields run in the context of a single document.

1 Like

Ok! Thank you for your answer!

Hello @elisavet

I think you can use an average aggregation to get the same result. See
https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-avg-aggregation.html

At the same time, it would be possible to use scripted metric to do what you wish, but the iteration will be done by Elasticsearch. See https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-scripted-metric-aggregation.html

Hello @Luca_Belluccini!

Thank you for your answer. I forgot to mention that I want the ratio to be visualised in kibana . Therefore, how could I use the average aggregation and scripted metric to show the ratio in kibana? Should I create a new index?

In Kibana the average will be calculated for the time range you'll choose.
If you want to get the average on all the documents regardless the time picker, you will have to create an index pattern without time field.