# Delete by query **URL:** \ **Category:** Elasticsearch\ **Tags:** painless\ **Created:** [October 21, 2021, 1:48pm UTC](https://discuss.elastic.co/t/delete-by-query/287322 "2021-10-21T13:48:55Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Giovanni\_Di\_Lembo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/giovanni_di_lembo/32/45714_2.png) [@Giovanni\_Di\_Lembo](https://discuss.elastic.co/u/Giovanni_Di_Lembo)\ **Post date:** [October 21, 2021, 1:48pm UTC](https://discuss.elastic.co/t/delete-by-query/287322/1 "2021-10-21T13:48:55Z")
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 ```auto { "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 ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 21, 2021, 2:14pm UTC](https://discuss.elastic.co/t/delete-by-query/287322/2 "2021-10-21T14:14:55Z")
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](https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-update-by-query.html#docs-update-by-query-api-ingest-pipeline) and [Script processor | Elasticsearch Guide [7.15] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/script-processor.html) 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. ---
**Author:** ![aaron-nimocks](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aaron-nimocks/32/73965_2.png) [@aaron-nimocks](https://discuss.elastic.co/u/aaron-nimocks)\ **Post date:** [October 21, 2021, 2:23pm UTC](https://discuss.elastic.co/t/delete-by-query/287322/3 "2021-10-21T14:23:09Z")
Also you might have to change your mapping of `creationDate` to a `date` vs `keyword` in order to query by date range. ---
**Author:** ![Giovanni\_Di\_Lembo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/giovanni_di_lembo/32/45714_2.png) [@Giovanni\_Di\_Lembo](https://discuss.elastic.co/u/Giovanni_Di_Lembo)\ **Post date:** [October 22, 2021, 5:27am UTC](https://discuss.elastic.co/t/delete-by-query/287322/4 "2021-10-22T05:27:26Z")
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 ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 22, 2021, 5:48am UTC](https://discuss.elastic.co/t/delete-by-query/287322/5 "2021-10-22T05:48:40Z")
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? ---
**Author:** ![Giovanni\_Di\_Lembo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/giovanni_di_lembo/32/45714_2.png) [@Giovanni\_Di\_Lembo](https://discuss.elastic.co/u/Giovanni_Di_Lembo)\ **Post date:** [October 22, 2021, 2:31pm UTC](https://discuss.elastic.co/t/delete-by-query/287322/6 "2021-10-22T14:31:54Z")
I found this solution to manipulate activities array ```auto 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 ---
**Author:** ![Giovanni\_Di\_Lembo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/giovanni_di_lembo/32/45714_2.png) [@Giovanni\_Di\_Lembo](https://discuss.elastic.co/u/Giovanni_Di_Lembo)\ **Post date:** [October 25, 2021, 7:39am UTC](https://discuss.elastic.co/t/delete-by-query/287322/7 "2021-10-25T07:39:54Z")
I found it, for all my fans ```auto 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 } """ } } ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 22, 2021, 7:40am UTC](https://discuss.elastic.co/t/delete-by-query/287322/8 "2021-11-22T07:40:38Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.