Examples

Here are a couple of useful examples that may help you create your own expressions.

Getting Full Name from First and Last Names and Vice Versa

Suppose, you load data between two data sources, and one of them has the ContactName column, storing full names, and another source has the separate First Name and Last Name columns. In this case you can use expression mapping to create full name from the first and last names and vice versa.

Concatenating first and last names into full name is easy, just don’t forget to add a space between them. The expression looks like the following:

"First Name" + ' ' + "Last Name"

Note that column names, containing spaces, like here, should be delimited with double quotation marks.

The reverse transformation is a bit more complex. We assume that the full name consists of the first name and the last name, separated with a space. In this case, we can find the position of space with the find_string function, and take the left and right parts of the full name. Here is an example for the first name:

left(ContactName,find_string(ContactName,' ',1)-1)

And for the last name:

right(ContactName,len(ContactName)-find_string(ContactName,' ',1))

However, if the ContactName column may also include middle name or more names, this expression for the last name would return all the names except the first one, because we are looking for the first occurrence of the space character. In this case, since we don’t know how many spaces the ContactName contains, we may reverse the string, so that the last part becomes the first, take this part, and reverse it again:

reverse(left(reverse(ContactName),find_string(reverse(ContactName),' ',1)-1))

These expressions with the left function, however, have one con. They cannot process cases when the ContactName column does not contain spaces at all. In this case, the find_string function returns 0, and the left function gets -1 as the second argument. This causes an error, and records with such ContactName values will fail. If it’s OK for you to fail such records, you may use these expressions. Otherwise, we may add an additional check for spaces in the source string using the conditional operator ?:. If there are no spaces, we may either use the whole source string or return a NULL value.

Let’s return a NULL value for the first name:

find_string(ContactName,' ',1) > 0 ? left(ContactName,find_string(ContactName,' ',1)-1) : null()

For the last name let’s return the full ContactName value, because in many cases last name is required, and cannot be null:

find_string(ContactName,' ',1) > 0 ? reverse(left(reverse(ContactName),find_string(reverse(ContactName),' ',1)-1)) : ContactName

Using Conditional Operator

The conditional operator is useful, for example, when you need to map a target column, having a fixed set of possible values, but source values are not exactly the same as target values.

For example, let’s consider loading Zendesk tickets to Jira issues. In Zendesk, tickets can have the following Priority values: Low, Normal, High, or Urgent. Tickets can also have no priority assigned. In Jira, available issue priorities are stored in their own table, and issues have references to the corresponding priorities in the PriorityId column. For example, in our Jira there are the following priorities: Highest, High, Medium, Low, and Lowest. They have ids from 1 to 5 respectively.

If we want to load Zendesk tickets to Jira issues, we can use expression mapping for the PriorityId column and select the corresponding value using the conditional operator. We can also use the replace_null function to use Normal priority for Zendesk tickets without priority set.

The expression will look like the following:

replace_null(Priority,'Normal')=='Normal' ? 3 : (Priority=='Urgent' ? 1 : (Priority=='High' ? 2 : 4))

The outer operator assigns the Id of Jira’s Medium priority (3) for Zendesk tickets with a Normal priority or without the priority assigned. Otherwise, we check if the ticket has Urgent priority in Zendesk, and assign the Highest priority (id is 1) in this case. Then the innermost operator checks if the ticket has the High priority, and assigns the corresponding id 2, and if fails, it assign the Low priority (id is 4).

Replacing NULL Values

In some cases, a column in a source can be null, but the corresponding column in target cannot. If you simply map the columns to each other, you will get errors for every source row, where the corresponding column has a NULL value. An easy way to avoid it is using the replace_null function.

This function checks whether the first argument is NULL. If not, it returns the first argument. Otherwise, it returns the second argument. So, for example, if the source has a boolean column Unsubscribed, which can have NULL values, and the target column does not accept NULL (is required), you can use the following expression in your mapping to replace NULL values with the false constant:

replace_null(Unsubscribed,false)

If the target field is not only required, but should also be unique, you cannot use constant value to replace null values. In this way, you need to use another column that is guaranteed to be non-null and unique as a replacement.

For example, suppose you have the Name column in target, and want to map the source column Full Name to it. However, the source may contain records without the Full Name specified, however, they always have the Display Name column filled. In this case, you can use the following expression.

replace_null(`Full Name`,`Display Name`)

In the worst case, when there are no suitable column for replacement, you may use the source record id.

Working with JSON Values

Some data sources, like G Suite (Google Contacts) or SendPulse, have columns, storing emails and phones in the JSON format. There are also other data sources with columns, storing data in JSON. When loading data between such data sources and other data sources, when values are stored as usual, without JSON, you can use expressions to construct the necessary JSON strings or get scalar values from JSON.

For example, Google Contact Emails values look like the following: [{“Address”:”[email protected]”,”Type”:”Work”,”IsPrimary”:true}]

This means that if our source stores emails as is in an E-mail column, we can use the following expression to import them to the Google Contacts Emails column:

'[{"Address":"'+"E-mail"+'","Type":"Work","IsPrimary":true}]'

A similar expression can be used for the Phones column:

'[{"PhoneNumber":"'+ Phone+ '","Type":"Mobile"}]'

As for extracting values from such JSON, usually it is not necessary, because Skyvia provides virtual objects, from which you can get these values as a simple column values. For example, you can get emails and phone numbers of your Google Contacts via the ContactEmails and ContactPhoneNumbers.

However, in this case we can also use expressions to extract values. For example, we can take a substring between the first occurrences of the ’:”‘ and ’”,’ character sequences. This would correspond to the first email and first phone in the Emails and PhoneNumbers column of the Contacts object:

substring(Emails,find_string(Emails,':"',1)+2,find_string(Emails,'",',1)-find_string(Emails,':"',1)-2)

And for the PhoneNumbers:

substring(PhoneNumbers,find_string(PhoneNumbers,':"',1)+2,find_string(PhoneNumbers,'",',1)-find_string(PhoneNumbers,':"',1)-2)

In this way you may design your own expressions for working with JSON string. To create a JSON string, you may obtain an example value from your data source with Skyvia query or export. Then, you may use this JSON string as a template and substitute source columns into places of the corresponding values, concatenating strings using the + operator.

To extract a value from the source JSON string, you may use the substring function, and use the find_string functions to determine the position and length of the value to extract.

Convert_tz Examples

convert_tz function is used to convert values from one timezone to another. For example, the expression

convert_tz(datetime('2021/7/7 19:00'),'Eastern Standard Time','Aleutian Standard Time')

returns 7/7/2021 2:00:00 PM.

For example, let’s consider a case when we need to synchronize two databases in different timezones, having datetime columns without time zones. Suppose one uses Western Europe Standard Time, and another — US Eastern Standard Time. The first one is used as a source, and the second — as a target. Let’s consider mapping for an example column LastContactedDate. For source to target mapping it will be:

convert_tz(LastContactedDate,'W. Europe Standard Time','Eastern Standard Time')

and for target to source mapping:

convert_tz(LastContactedDate,'Eastern Standard Time','W. Europe Standard Time')

Anonymizing Data via Hashing

Hashing field values is useful to conceal sensitive information when loading data to other systems. It enables you to load anonymized values that still can be used for analytics purposes.

Skyvia uses sha256_encrypt and sha512_encrypt functions to hash data. These functions hash binary or string values using SHA-256 and SHA 512 algorithms respectively, adding a string or binary salt to the value as a suffix. The key difference between SHA-256 and SHA-512 is their output size, with SHA-256 producing a 256-bit hash value and SHA-512 generating a 512-bit hash value. Note that if you specify the salt as a string value, you need to use a base64 encoded value.

For example, if you want to conceal names, you can use the following expression:

sha256_encrypt(Name,encode_base64('the_secret_salt'))

Replace the_secret_salt with your own secret string.