# Update 2 million record with a correct format of country prefix in phone number field

**URL:** <https://discuss.elastic.co/t/update-2-million-record-with-a-correct-format-of-country-prefix-in-phone-number-field/239152>\
**Category:** Elasticsearch\
**Created:** [June 29, 2020, 3:56pm UTC](https://discuss.elastic.co/t/update-2-million-record-with-a-correct-format-of-country-prefix-in-phone-number-field/239152 "2020-06-29T15:56:22Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![meena](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/meena/32/71304_2.png) [@meena](https://discuss.elastic.co/u/meena)\
**Post date:** [June 29, 2020, 3:56pm UTC](https://discuss.elastic.co/t/update-2-million-record-with-a-correct-format-of-country-prefix-in-phone-number-field/239152/1 "2020-06-29T15:56:22Z")

</div>

Hi,

Am not an Expert on elastic search but would like to update around 2 million records in my database with a country prefix to be added to the phone number field in correct format.  
My phone number field lies under nested fields. The data is store in below structure

```
"contact" : [
            {
              "contactIdentifier" : {
                "id" : "xxxxx"
              },
              "contactMedium" : [
                {
                  "PhoneNumber" : {
                    "countryPrefix" : "21",
                    "phoneNumber" : "2147000000",
                    "nationalNumber" : "47000000"
                  }
                },
                {
                  "emailAddress" : "hilxxxrs@tvvvv.bggg",
                  "cmIdentifier" : "266k7382873115286"
                }
              ]
            }
          ]

```

i would like to update data for "phoneNumber" : "2147000000" to "phoneNumber" : "002147000000"

I tried with the below query to update the data but it did not work

```
POST /search_party_reindex_v2/_update_by_query
{
    "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "contact.contactMedium",
            "query": {
              "exists": {
                "field": "contact.contactMedium.PhoneNumber.phoneNumber"
              }
            }
          }
        }
      ], 
      "must_not": [
        {
          "nested": {
            "path": "contact.contactMedium",
            "query": {
              "wildcard": {
                "contact.contactMedium.PhoneNumber.phoneNumber": {
                  "value": "00*"
                }
              }
            }
          }
        }
      ]
    }
  },
    "script": {
        "script": "ctx._source.contact?.each { obj -> if (obj != null && obj.contactMedium != null) { obj.contactMedium?.each{cm -> if(cm.PhoneNumber.phoneNumber != null) ctx._source.contact.contactMedium.PhoneNumber.phoneNumber -= params.prefix + ctx._source.contact.contactMedium.PhoneNumber.phoneNumber}}};",
        "lang": "painless",
        "params": {
            "prefix": "00"
        }
    }
}

```

Can any one help me on this

Thanks  
Meena

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [June 29, 2020, 5:48pm UTC](https://discuss.elastic.co/t/update-2-million-record-with-a-correct-format-of-country-prefix-in-phone-number-field/239152/2 "2020-06-29T17:48:11Z")

</div>

Try this may help, it's not the best solution, but that will help

```
POST search_party_reindex_v2/_update_by_query
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "contact.contactMedium",
            "query": {
              "exists": {
                "field": "contact.contactMedium.PhoneNumber.phoneNumber"
              }
            }
          }
        }
      ],
      "must_not": [
        {
          "nested": {
            "path": "contact.contactMedium",
            "query": {
              "wildcard": {
                "contact.contactMedium.PhoneNumber.phoneNumber": {
                  "value": "00*"
                }
              }
            }
          }
        }
      ]
    }
  },
  "script": {
    "source": """
    if(ctx._source['contact'] != null) {
      for(int i = 0; i <= ctx._source['contact'].size()-1; i++) {
        if(ctx._source.contact[i]['contactMedium'] != null){
          for(int j = 0; j <= ctx._source.contact[i]['contactMedium'].size()-1; j++) {
            if (ctx._source.contact[i].contactMedium[j]['PhoneNumber'] != null) {
              ctx._source.contact[i].contactMedium[j].PhoneNumber['phoneNumber'] = params.prefix + ctx._source.contact[i].contactMedium[j].PhoneNumber['phoneNumber'];
            }
          }
        }
        }
    }
    """,
    "lang": "painless",
    "params": {
      "prefix": "00"
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![meena](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/meena/32/71304_2.png) [@meena](https://discuss.elastic.co/u/meena)\
**Post date:** [June 29, 2020, 9:19pm UTC](https://discuss.elastic.co/t/update-2-million-record-with-a-correct-format-of-country-prefix-in-phone-number-field/239152/3 "2020-06-29T21:19:12Z")

</div>

> [@ylasri](#):
>
> ```auto
> POST search_party_reindex_v2/_update_by_query
> {
> "query": {
> "bool": {
> "must": [
> {
> "nested": {
> "path": "contact.contactMedium",
> "query": {
> "exists": {
> "field": "contact.contactMedium.PhoneNumber.phoneNumber"
> }
> }
> }
> }
> ],
> "must_not": [
> {
> "nested": {
> "path": "contact.contactMedium",
> "query": {
> "wildcard": {
> "contact.contactMedium.PhoneNumber.phoneNumber": {
> "value": "00*"
> }
> }
> }
> }
> }
> ]
> }
> },
> "script": {
> "source": """
> if(ctx._source['contact'] != null) {
> for(int i = 0; i <= ctx._source['contact'].size()-1; i++) {
> if(ctx._source.contact[i]['contactMedium'] != null){
> for(int j = 0; j <= ctx._source.contact[i]['contactMedium'].size()-1; j++) {
> if (ctx._source.contact[i].contactMedium[j]['PhoneNumber'] != null) {
> ctx._source.contact[i].contactMedium[j].PhoneNumber['phoneNumber'] = params.prefix + ctx._source.contact[i].contactMedium[j].PhoneNumber['phoneNumber'];
> }
> }
> }
> }
> }
> """,
> "lang": "painless",
> "params": {
> "prefix": "00"
> }
> }
> }
> 
> ```

Thanks. It works 🙂

My index is having around 16 million record and i have to correct 2 million records using the above update query. Is it the only way to achieve this or do we have any other alternative to perform this activity faster than update by query.

---

<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:** [July 27, 2020, 9:19pm UTC](https://discuss.elastic.co/t/update-2-million-record-with-a-correct-format-of-country-prefix-in-phone-number-field/239152/4 "2020-07-27T21:19:13Z")

</div>

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