Create script to update an attribute datatype from string to array for all the documents in a bucket

Couchbase Server
1

I want to create a script to update ‘email’ attribute datatype for all the documents inside a bucket(Client) from a string to an array. Array would contain the value for the email attribute inside it. I want to store more than 1 email domain for any given client, so I have to retrospectively make the change to all client data which is currently in the system.
Please help me with the update script format.
Is there any equivalent of ‘Alter Table Statement’ in Couchbase?

2

Can you post an example of what the array should look like?

3

@clinton1ql
For example, I have a document like below. I want to add a new attribute “ClientEmailArray” which will be of a Array datatype where we want to store more than 1 email domain for any given client.
{
“taxIdNumber”: “”,
“clientEmail”: “gmail.com”,
“secIdentifier”: “”,
“clientAddress”: “”,
“clientLicenseTree”: [
{
“licenseType”: “C_T”,
“licenseId”: “12345667”,
“licenseStartDate”: “2018-03-01 00:00:00.000”,
“licenseStatus”: “Active”,
“licenseExpirationDate”: “2019-03-01 00:00:00.000”
}
],
“clientPhone”: “”,
“clientAdministrator”: “”,
“clientPartner”: “”,
“primaryContact”: “”,
“name”: “Mckee, Abner S.”,
“_class”: “com.abc.adc.assurance.client.domain.Client”,
“clientTPRID”: “22233”
}.

The Array should look like :
“ClientEmailArray”:[
“gmail.com”,
“yahoo.com”
]

4

Do all the documents currently just have a single value for clientEmail? That you want to convert into clientEmailArray? And the clientEmail just goes “as is” into clientEmailArray, no changing of any data?

How many documents do you need to update?

5

@clinton1ql
Ya all the documents now have single value for clientEmail. As of now, the clientEmail will just go “as is” into clientEmailArray, bt once business will be adding more values the data will be changed.
122 documents I need to update.

6

Try this? Just replace nameofyourbucket with your bucket name.

UPDATE nameofyourbucket SET clientEmailArray = (SELECT RAW ( nameofyourbucket.clientEmail ));

7

Thanks @clinton1ql
I just tried this for a sample,its working fine. Will execute the same in original bucket and let you know in case of any issues.

8

TO_ARRAY() - If it is array returns as it is, if not converts array of single value

UPDATE nameofyourbucket SET clientEmailArray = TO_ARRAY(clientEmail);

9

@vsr1
This also works fine. Can u tell me the difference between using RAW and TO_ARRAY?
As for now, both the functions are giving same output.

10

(SELECT RAW ( nameofyourbucket.clientEmail ))
This correlated sub query it is overhead. and no need