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

[meena (Meenakshi)]

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

[ylasri (Yassine LASRI)]

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"
    }
  }
}

[meena (Meenakshi)]

@ylasri

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.