Delete by query

[Giovanni_Di_Lembo]

Hi all, and sorry for my poor english.
I need to build a delete by query request.
My porpose is to delete 'activity' elements in 'activities' array where 'creationDate' is less than some date.
My index mapping is

{
 "profile-history" : {
   "mappings" : {
     "properties" : {
       "_class" : {
         "type" : "text",
         "fields" : {
           "keyword" : {
             "type" : "keyword",
             "ignore_above" : 256
           }
         }
       },
       "activities" : {
         "type" : "nested",
         "properties" : {
           "cpeBoxId" : {
             "type" : "keyword"
           },
           "creationDate" : {
             "type" : "keyword"
           },
           "errorCode" : {
             "type" : "keyword"
           },
           "errorMessage" : {
             "type" : "keyword"
           },
           "jobId" : {
             "type" : "keyword"
           },
           "message" : {
             "type" : "keyword"
           },
           "modificationDate" : {
             "type" : "keyword"
           },
           "operationType" : {
             "type" : "keyword"
           }
         }
       },
       "federationId" : {
         "type" : "keyword"
       },
       "profileUuid" : {
         "type" : "keyword"
       }
     }
   }
 }
}

Thnx in advance

[dadoonet (David Pilato)]

This is not a trivial thing to do.

You need to do 2 things:

• First, build the query which will find the documents you are looking for
• Then, build a painless script which removes some part of the json content. (As you want to manipulate the _source).

It looks like to me an update by query but I'm just guessing. See Update By Query API | Elasticsearch Guide [7.15] | Elastic and Script processor | Elasticsearch Guide [7.15] | Elastic

Unless you want to delete the full document if it matches the query?
In which case you have to:

• First, build the query which will find the documents you are looking for
• Then, call the delete by query API with the same request.

[aaron-nimocks]

Also you might have to change your mapping of creationDate to a date vs keyword in order to query by date range.

[Giovanni_Di_Lembo]

Can you suggest samples? I can fetch the activities array using script, I can remove an element but I can't find how to reassign this reduced array to main document

[dadoonet (David Pilato)]

I don't have such scripts.
I believe that you have to create a new array, iterate over the old one, check the date and if needed send the object to the new array.
At the end set the new array as the value for the field activities.

But I'm wondering about the usecase.
If this is a one time operation because you need to fix your dataset, that would be fine.
But if you're doing that every x minutes, I don't think it will be super efficient.

May be explain the use case with sole concrete data?

[Giovanni_Di_Lembo]

I found this solution to manipulate activities array

GET /profile-history/_search
{
  "script_fields": {
    "activities": {
      "script": {
        "lang": "painless",
        "source": """
                if (params['_source']['activities']!=null)    
                {   
                    ArrayList  activities=params['_source']['activities'];
                    for (int i=activities.length-1; i>=0; i--) {
                      if (activities[i].creationDate.compareTo('2021-09-02 T 15:46:15.985+0000')<=0) {
                          activities.remove(i);
                         
                      }
                       
                    }
                    return activities;
                    
                }
        """
      }
    } 
  }
  
}

does nothing

[Giovanni_Di_Lembo]

I found it, for all my fans

POST /profile-history/_update_by_query
{
  
      "script": {
        "lang": "painless",
        "inline": """
                if (ctx._source.activities!=null)    
                {   
                    ArrayList  activities=ctx._source.activities;
                    for (int i=activities.length-1; i>=0; i--) {
                      if (activities[i].creationDate.compareTo('2021-09-02 T 15:59:33.160+0000')==0) {
                          activities.remove(i);
                         
                      }
                       
                    }
                     
                   
                    ctx._source.activities=activities
                    
                }
        """
  
  }
}