Not possible to count documents in search query


#1

Hi,

I am trying to alternate sort documents in Elasticsearch on date and a certain field value, if found then the document is pushed to the top of the results. So a result set needs to be like this: first the most recent document then the pushed document, then the second recent document and then the second pushed document, then the third recent document and then the third pushed document and so on. I wanted to achieve this by scripting a counter in painless. So I sort the documents on date and give them a counter value incremented by 2. So the first document gets 1, the second document gets 3, the third gets 5. The moment a pushed document is found, I overwrite the counter, I start the counter by 2 and next one by 4 and then 6 and so on. In the end I sort the whole lot by the counter (this can be done in SQL) and is reasonable simple. I searched and I searched but in an Elsaticsearch search query there is no way to count documents or increment counters over multiple records. Or is there?

Regards,


(Christian Dahlqvist) #2

Why not simply issue two separate queries using a msearch request and interleave the results client side?


#3

Is possible, but if a million records are found, you have to load in the whole lot client side.


(Christian Dahlqvist) #4

Why would that be a problem when using 2 queries and not when merging them into one? Why would the client want to see 1 million documents?


#5

Because the sorting have to be done on the overall result set not just a limited one. It is for a property website with more than 400.000 properties. Elasticsearch is by far the fastest way.


#6

By the way, I am curious, why can't you use a counter or increment a value per document in Elasticsearch, clearly it uses it itself when you set size and from.