delete all elements which is less than a value in Elasticsearch

Question

I have the following saved json data in Elasticsearch:

   {
   "id":"1234",
   "expirationDate":"17343234234",
   "paths":"http:localhost:9090",
   "work":"software dev",
   "family":{
      "baba":"jams",
      "mother":"ela"
   }
},
{
   "id":"00021",
   "expirationDate":"0123234",
   "paths":"http:localhost:8080",
   "work":"software engi",
   "family":{
      "baba":"stev",
      "mother":"hela"
   }
}

i want to delete all list of ids which its expirationDate are smaller than today using QueryBuilder in springdata Elasticsearch

my question is how much is all? 10, 100, 1000, 10000, 1M? the solution depends on that answer — Val, Oct 22 '20 at 18:50
Ok, then you're not going to be able to get them all in one shot, is that ok for you? — Val, Oct 22 '20 at 18:52
And I'm just curious about the condition... all items created before today, pretty much means all items, right? since there are no items created in the future... or are there? — Val, Oct 22 '20 at 18:52
The reason is that by default, ES will only return 10K hits. You can lift that limit, but the can be performance implications... It'd help to know your use case in more details. — Val, Oct 22 '20 at 18:54
Okay what is the best solution for that plz, i want to get all old entities, means before today — Catalina, Oct 22 '20 at 18:55
Well not all items, in more details there well be expire date which is 3 days ahead, but i am fucusing on this small example just for demo — Catalina, Oct 22 '20 at 19:00
Because if i check whether expirationDate before today then the item is expired — Catalina, Oct 22 '20 at 19:03
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/223476/discussion-between-catalina-and-val). — Catalina, Oct 22 '20 at 19:03
Here is a solution that would work for you: https://stackoverflow.com/a/37669582/4604579, it might need some adaptation given it was for an older version, but the concept is explained there, i.e. scrolled search. — Val, Oct 22 '20 at 20:00
Could u please adapt the solution with elastcsearch 7.6 which i am using, would very thankfull — Catalina, Oct 22 '20 at 20:26

GPI · Accepted Answer · 2020-11-06T16:17:42.003

9

Well, delete by query is the way to go.

POST /{your_index_name}/_delete_by_query
{
  "query": {
    "range": {
      "expirationDate": {
        "lt": "{your_timestamp}"
      }
    }
  } 
}

The java client documentation indicates you can build a request this way:

BulkByScrollResponse response =
  new DeleteByQueryRequestBuilder(client, DeleteByQueryAction.INSTANCE)
    .filter(QueryBuilders.matchQuery("gender", "male")) 
    .source("persons")                                  
    .get();                                             
long deleted = response.getDeleted();

This is marked as supported by Spring-data-elasticsearch since version 3.2.

You can for example use query derivation :

In addition to query methods, query derivation for both count and delete queries is available.

In the Appendix C, you can see that IsLessThan is a query derivation keyword, which means something along these lines ought to be supported out of the box :

interface YourRepository extends CrudRepository<User, Long> {
  long deleteByExpirationDateIsLessThan(long timestamp);
}

By using query derivation, you are letting spring do the implementation (fingers crossed that it will do "the right thing").

But you can also use a ElasticsearchRestTemplate#delete (if you are using the older ElasticsearchTemplate, this works the same).

This allows you to pass in any spring-data query (Native, String, or criteria).

edited Nov 06 '20 at 16:17

answered Nov 06 '20 at 15:07

GPI

9,088
2
31
38

but how to create a function supporting this query using QueryBuilder or similar API?or DeleteRequestBuilder in springboot – Catalina Nov 06 '20 at 15:16
I've added details, but really, this is just standard Spring data / Spring es stuff. – GPI Nov 06 '20 at 16:19
i have last questoin please, is there any way to update all expirationDate to be currenttime? so is expiratonDAte less than currenttime thaen update to todays time please help me – Catalina Nov 08 '20 at 21:33
There kind of is, but elasticsearch is not optimized for this at all so it is extremely costly (it basically entails rebuilding the whole index). But this is another question altogether so please ask in a separate question. – GPI Nov 09 '20 at 08:43
please help me: https://stackoverflow.com/questions/64771780/updating-elasticsearch-entities-with-bulk-without-id-parameters – Catalina Nov 10 '20 at 15:25

delete all elements which is less than a value in Elasticsearch

1 Answers1