# How to update all docs with query that looks for a regex and update it incase found

**URL:** <https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894>\
**Category:** Elasticsearch\
**Created:** [March 16, 2022, 7:28pm UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894 "2022-03-16T19:28:39Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![ShayWeizman](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@ShayWeizman](https://discuss.elastic.co/u/ShayWeizman)\
**Post date:** [March 16, 2022, 7:28pm UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/1 "2022-03-16T19:28:39Z")

</div>

Hi,

I'm using 7.16.2.

I have a lot of records looks like this

```auto
{
        "_index" : "testcases",
        "_type" : "_doc",
        "_id" : "Cv6z8HoBF-BJEaLQYGtU",
        "_score" : 1.0,
        "_source" : {
          "buildVersion" : "1.16.3-alpha+9950",
          "testcase" : {
            "$" : {
              "name" : "/opt/fireglass/1.16.3-alpha+9950/regression_tests/out/cases/activity_logs/basic_activity_logs_search.js - Basic activity log search Activity log search after browsing Contains a log with events 'Network Request' and 'Forward To Isolation",
              "time" : "14.583",
              "className" : "Contains a log with events 'Network Request' and 'Forward To Isolation"
            }
          },
          "testcaseName" : "Basic activity log search Activity log search after browsing Contains a log with events 'Network Request' and 'Forward To Isolation" (18),
          "testcaseStatus" : "Passed",
          "timestamp" : "2021-07-29T05:18:45.071Z"
        }
      }

```

In the testcaseName field there is an index in brackets, for example "(18)".

I want to run on all documents in the DB on testcaseName field and search for testcaseName that contain this index and remove it.

For example:  
"testcaseName" : "Basic activity log search Activity log search after browsing Contains a log with events 'Network Request' and 'Forward To Isolation" (18)  
Will become:  
"testcaseName" : "Basic activity log search Activity log search after browsing Contains a log with events 'Network Request' and 'Forward To Isolation"

Just remove the "(18)"

I did it in my code to prevent it happen again, but I need to fix the DB as well.  
javascript code:

```auto
let newTestName = testName;
    // Find number in parenthesis with space (if exist) before as example: " (33)"
    const match = testName.match(/\s?\([0-9]+\)/gm);

    if (match !== null) {
        // Get only the last finding
        const matchPattern = match[match.length - 1];
        const endIndex = testName.lastIndexOf(matchPattern);
        newTestName = testName.substring(0, endIndex);
    }

```

How to do the same in Elastic? A query in Dev Tools or should I write code in order to that?  
If there is a way to do it from Dev tools let me know how?

Thanks,  
Shay

---

<div class="post-metadata">

**Author:** ![casterQ](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casterq/32/93257_2.png) [@casterQ](https://discuss.elastic.co/u/casterQ)\
**Post date:** [March 17, 2022, 3:06am UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/2 "2022-03-17T03:06:51Z")

</div>

The doc recognized by ES is JSON，but this is not a json，How do you save it into es？

```auto
"testcaseName" : "Basic activity log search Activity log search after browsing Contains a log with events 'Network Request' and 'Forward To Isolation" (18),

```

---

<div class="post-metadata">

**Author:** ![ShayWeizman](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@ShayWeizman](https://discuss.elastic.co/u/ShayWeizman)\
**Post date:** [March 17, 2022, 6:30am UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/3 "2022-03-17T06:30:59Z")

</div>

> [@casterQ](#):
>
> ES

I build a json in the code and save it to ES

---

<div class="post-metadata">

**Author:** ![casterQ](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casterq/32/93257_2.png) [@casterQ](https://discuss.elastic.co/u/casterQ)\
**Post date:** [March 17, 2022, 6:35am UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/4 "2022-03-17T06:35:22Z")

</div>

So `(18)` should be in front of `"` ？

---

<div class="post-metadata">

**Author:** ![ShayWeizman](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@ShayWeizman](https://discuss.elastic.co/u/ShayWeizman)\
**Post date:** [March 17, 2022, 6:53am UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/5 "2022-03-17T06:53:10Z")

</div>

I've fixed the code part, so this won't be a problem in the future, my question is how to fix the existing data in Elastic

---

<div class="post-metadata">

**Author:** ![casterQ](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casterq/32/93257_2.png) [@casterQ](https://discuss.elastic.co/u/casterQ)\
**Post date:** [March 17, 2022, 6:59am UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/6 "2022-03-17T06:59:56Z")

</div>

so you want to update data in es  
from:

```auto
"testcaseName" : "Basic activity log search Activity log search after browsing Contains a log with events 'Network Request' and 'Forward To Isolation (18)"

```

to:

```auto
"testcaseName" : "Basic activity log search Activity log search after browsing Contains a log with events 'Network Request' and 'Forward To Isolation"

```

do you ? In es, `(18)` must be in front of `"`

---

<div class="post-metadata">

**Author:** ![ShayWeizman](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@ShayWeizman](https://discuss.elastic.co/u/ShayWeizman)\
**Post date:** [March 17, 2022, 7:12am UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/7 "2022-03-17T07:12:50Z")

</div>

Yes, the result you show this is my main goal

---

<div class="post-metadata">

**Author:** ![casterQ](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casterq/32/93257_2.png) [@casterQ](https://discuss.elastic.co/u/casterQ)\
**Post date:** [March 17, 2022, 7:28am UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/8 "2022-03-17T07:28:31Z")

</div>

use `script pipeline` to update your data in es，as below, You can write your own script to suit your specific situation

```auto
POST _ingest/pipeline/_simulate
{
  "pipeline": {
    "processors": [
      {
        "script": {
          "lang": "painless",
          "source": """
            if(ctx.testcaseName.endsWith(" (18)")){
              ctx.testcaseName=ctx.testcaseName.substring(0,ctx.testcaseName.lastIndexOf(" (18)"));
            }
          """
        }
      }
    ]
  },
  "docs": [
    {
      "_source": {
        "testcaseName" : "Basic activity log search Activity log search after browsing Contains a log with events 'Network Request' and 'Forward To Isolation (18)"
      }
    },
    {
      "_source": {
        "testcaseName" : "Basic activity log search Activity log search after browsing Contains a log with events 'Network Request' and 'Forward To Isolation xxxx"
      }
    }
  ]
}

```

---

<div class="post-metadata">

**Author:** ![ShayWeizman](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@ShayWeizman](https://discuss.elastic.co/u/ShayWeizman)\
**Post date:** [March 17, 2022, 8:09am UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/9 "2022-03-17T08:09:55Z")

</div>

First of all, thank you very much for your response.  
I see you wrote \_simulate, so this script just do a simulation? If I really want to change the data I use \_update instead?

---

<div class="post-metadata">

**Author:** ![casterQ](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casterq/32/93257_2.png) [@casterQ](https://discuss.elastic.co/u/casterQ)\
**Post date:** [March 17, 2022, 8:13am UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/10 "2022-03-17T08:13:14Z")

</div>

you can follow this DOC, use update API with pipeline or script

> **[Update By Query API | Elasticsearch Guide \[8.1\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/8.1/docs-update-by-query.html#docs-update-by-query-api-ingest-pipeline)**

---

<div class="post-metadata">

**Author:** ![ShayWeizman](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@ShayWeizman](https://discuss.elastic.co/u/ShayWeizman)\
**Post date:** [March 24, 2022, 11:00am UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/11 "2022-03-24T11:00:26Z")

</div>

How do I run this script on index? for example my index is: testindexshay.. ?

I've tried:

```auto
PUT _ingest/pipeline/testindexshay
{
  "pipeline": {
    "processors": [
      {
        "script": {
          "lang": "painless",
          "source": """
            if(ctx.testcaseName.endsWith(" (22)")){
              ctx.testcaseName=ctx.testcaseName.substring(0,ctx.testcaseName.lastIndexOf(" (22)"));
            }
          """
        }
      }
    ]
  }
}

```

and got this error:

```auto
{
  "error" : {
    "root_cause" : [
      {
        "type" : "parse_exception",
        "reason" : "[processors] required property is missing",
        "property_name" : "processors"
      }
    ],
    "type" : "parse_exception",
    "reason" : "[processors] required property is missing",
    "property_name" : "processors"
  },
  "status" : 400
}

```

How do I run on all docs in this index?

---

<div class="post-metadata">

**Author:** ![casterQ](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casterq/32/93257_2.png) [@casterQ](https://discuss.elastic.co/u/casterQ)\
**Post date:** [March 25, 2022, 3:35am UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/12 "2022-03-25T03:35:57Z")

</div>

use `update_by_query` API with pipeline:

> **[Update By Query API | Elasticsearch Guide \[8.1\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/8.1/docs-update-by-query.html#docs-update-by-query-api-ingest-pipeline)**

---

<div class="post-metadata">

**Author:** ![ShayWeizman](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@ShayWeizman](https://discuss.elastic.co/u/ShayWeizman)\
**Post date:** [March 25, 2022, 1:52pm UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/13 "2022-03-25T13:52:07Z")

</div>

I have used this script:

```auto
POST /testindexshay/_update_by_query
{
         "script": {
          "lang": "painless",
          "source": """
            if(ctx.testcaseName.endsWith(" (22)")){
              ctx.testcaseName=ctx.testcaseName.substring(0,ctx.testcaseName.lastIndexOf(" (22)"));
            }
          """
}

```

and got this error:

```auto
{
  "error" : {
    "root_cause" : [
      {
        "type" : "script_exception",
        "reason" : "runtime error",
        "script_stack" : [
          "if(ctx.testcaseName.endsWith(\" (22)\")){\n ",
          " ^---- HERE"
        ],
        "script" : " ...",
        "lang" : "painless",
        "position" : {
          "offset" : 32,
          "start" : 13,
          "end" : 67
        }
      }
    ],
    "type" : "script_exception",
    "reason" : "runtime error",
    "script_stack" : [
      "if(ctx.testcaseName.endsWith(\" (22)\")){\n ",
      " ^---- HERE"
    ],
    "script" : " ...",
    "lang" : "painless",
    "position" : {
      "offset" : 32,
      "start" : 13,
      "end" : 67
    },
    "caused_by" : {
      "type" : "null_pointer_exception",
      "reason" : "cannot access method/field [endsWith] from a null def reference"
    }
  },
  "status" : 400
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 22, 2022, 1:52pm UTC](https://discuss.elastic.co/t/how-to-update-all-docs-with-query-that-looks-for-a-regex-and-update-it-incase-found/299894/14 "2022-04-22T13:52:56Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.