Visual Mapping functions

In map expressions, you can use some functions to do slightly more complex tasks.

For instance, consider a scenario where the input property maritalStatus is null, indicating an unknown marital status. You must map this property to the non-nullable output property marital_status. To address this situation, you could use the coalesce function, which returns the first non-null argument:

┌─────────────────────────────────────┐
│ coalesce(maritalStatus, "Unknown")  │ ->  marital_status
└─────────────────────────────────────┘

With this mapping, the input:

maritalStatus: null

will become:

marital_status: "Unknown"

Below is a list of available functions:

and

The and function returns true only when all of its arguments are true; otherwise, it returns false. For example:

┌─────────────────────────────────┐
│ and(active, newsletterConsent)  │ ->  marketing_consent
└─────────────────────────────────┘

It returns null if an argument is null and there are no false arguments. For example:

and(true, true)   -> true
and(true, false)  -> false
and(false, false) -> false
and(null, true)   -> null
and(null, false)  -> false

The arguments of the and function should have type boolean, and the result has type boolean.

array

The array function returns an array with the passed arguments as elements. For example:

┌─────────────────────────────────┐
│ array(email, company.email)     │ ->  emails
└─────────────────────────────────┘

The result of the array function has type array(json).

coalesce

The coalesce function returns the first non-null argument, or null if all arguments are null. For example:

┌───────────────────────────────────────────┐
│ coalesce(shippingAddress, billingAddress) │ ->  ship_address
└───────────────────────────────────────────┘

For example:

coalesce(null, 0)    -> 0
coalesce(null, null) -> null

The result of the coalesce function has type json.

eq

The eq function takes two values and returns true if they are equal; otherwise, it returns false. For example:

┌─────────────────────────────────┐
│ eq(level, "VIP")                │ ->  vip_customer
└─────────────────────────────────┘

If an argument is null, the function returns null. For example:

eq(5, 5)      -> true
eq('a', 'b')  -> false
eq('a', 5)    -> false
eq('a', null) -> null

The result of the eq function has type boolean.

if

The if function evaluates the first boolean argument. If it is true, the function returns the second argument. If the first argument is false or null, the function returns the third argument. For example, if hasCode is true, the following code evaluates to the value of code; otherwise, it evaluates to an empty string value.

┌─────────────────────────────────┐
│ if(hasCode, code, '')           │ ->  code
└─────────────────────────────────┘

For example:

if(true, 1, 2)      -> 1
if(false, 'a', 'b') -> 'b'
if(null, 7.5, 9.8)  -> 9.8

The if function can also be called with only two arguments. In this case, if the first argument is not true, the result is null.

┌─────────────────────────────────┐
│ if(hasCode, code)               │ ->  code
└─────────────────────────────────┘

For example:

if(true, 5)  -> 5
if(false, 5) -> null
if(null, 5)  -> null

The result of the if function has type json.

initcap

The initcap function returns its argument with the first letter of each word in uppercase, all other letters in lowercase. For example:

┌─────────────────────────────────┐
│ initcap(firstName ' ' lastName) │ ->  full_name
└─────────────────────────────────┘

If its argument is null, the function returns null. For example:

initcap('emily johnson')  -> 'Emily Johnson'
initcap('john o\'connor') -> 'John O\'Connor'
initcap('NEW YORK')       -> 'New York'
initcap(null)             -> null

The argument of the initcap function should have type string, and the result has type string.

json_parse

The json_parse function parses its argument as JSON and returns the corresponding json value. For example:

┌──────────────────────────────────────────────┐
│ json_parse('{"city":"Milan","zip":"20100"}') │ ->  address
└──────────────────────────────────────────────┘

If its argument is null, the function returns null. For example:

json_parse('"Android"') -> "Android" as JSON
json_parse('true')      -> true      as JSON
json_parse('[1, 2, 3]') -> [1,2,3]   as JSON
json_parse('null')      -> null      as JSON
json_parse(null)        -> null

If the input is not valid JSON, json_parse will produce an error, causing the entire mapping to fail.

The argument of the json_parse function should have type string or json. If the argument has type json, the value must be a JSON string. The result has type json.

len

The len function returns the length of the given argument based on its type.

┌─────────────────────────────────┐
│ len(values)                     │ ->  num_values
└─────────────────────────────────┘

The length is determined as follows:

• string: Returns the length in characters.
• array: Returns the number of elements.
• map: Returns the number of key-value pairs.
• object: Returns the number of present properties.
• boolean, int, float, decimal, datetime, date, time, year, uuid, ip: Returns the length of its string representation.

For json values:

• JSON string: Returns the length in characters.
• JSON object: Returns the number of key-value pairs.
• JSON array: Returns the number of elements.
• JSON boolean: Returns the length of its string representation.
• JSON number: Returns the length of its string representation.
• JSON null: Returns 0.

If the argument is null, the function also returns 0. For example:

len(null) -> 0
len(true) -> 4
len(false) -> 5
len('Seattle') -> 7
len('東京') -> 2
len(array(1.5, 3.9, 0.15)) -> 3
len(520945) -> 6
len(-722) -> 4
len(88.0300) -> 5

The result of the len function has type int(32).

lower

The lower function returns its argument with all letters in lower case. For example:

┌─────────────────────────────────┐
│ lower(address.city)             │ ->  city
└─────────────────────────────────┘

If its argument is null, the function returns null. For example:

lower("aBc") -> "abc"
lower(null)  -> null

The argument of the lower function should have type string, and the result has type string.

ltrim

The ltrim function returns its argument with all leading Unicode whitespace removed. For example:

┌─────────────────────────────────┐
│ ltrim(email)                    │ ->  email
└─────────────────────────────────┘

If its argument is null, the function returns null. For example:

ltrim("  info@example.com") -> "info@example.com"
ltrim("\t   hello world\n") -> "hello world\n"
ltrim(null)  -> null

Both the argument and the result of the trim function have type string.

map

The map function builds a map from the provided key-value pairs. It must be called with an even number of arguments where string keys and values alternate. Keys are case-insensitive, must be constant string, and must be unique (though they can be empty); duplicate keys cause a compile error. For example:

┌────────────────────────────────────────────┐
│ map('foo', traits.name, 'active', true)    │ ->  customer_attributes
└────────────────────────────────────────────┘

Calling map() with no arguments returns an empty map.

If a value is just a single property that has no value, the corresponding key is omitted from the resulting map.

The result of the map function has type map(json).

ne

The ne function takes two values and returns true if they are not equal; otherwise, it returns false. For example:

┌─────────────────────────────────┐
│ ne(status, "inactive")          │ ->  is_active
└─────────────────────────────────┘

If an argument is null, the function returns null. For example:

ne(5, 5)      -> false
ne('a', 'b')  -> true
ne('a', 5)    -> true
ne('a', null) -> null

The result of the ne function has type boolean.

not

The not function returns false if its argument is true, and true if its argument is false. For example:

┌─────────────────────────────────┐
│ not(traits.active)              │ ->  inactive
└─────────────────────────────────┘

If its argument is null, the function returns null. For example:

not(true)  -> false
not(false) -> true
not(null)  -> null

The argument of the not function should have type boolean, and the result has type boolean.

or

The or function returns true if at least one of its arguments is true; otherwise, it returns false. For example:

┌─────────────────────────────────┐
| or(hasDriverLicense, age18)     │ ->  eligibility
└─────────────────────────────────┘

It returns null if an argument is null and there are no true arguments. For example:

or(true, true)   -> true
or(true, false)  -> true
or(false, false) -> false
or(null, true)   -> true
or(null, false)  -> null

The arguments of the or function should have type boolean, and the result has type boolean.

rtrim

The rtrim function returns its argument with all trailing Unicode whitespace removed. For example:

┌─────────────────────────────────┐
│ rtrim(email)                    │ ->  email
└─────────────────────────────────┘

If its argument is null, the function returns null. For example:

rtrim("info@example.com\n") -> "info@example.com"
rtrim("\t   hello world\n") -> "\t   hello world"
rtrim(null)  -> null

Both the argument and the result of the rtrim function have type string.

substring

The substring function extracts a portion of a string based on a specified starting position and length. The indices are 1-based, meaning the first character of the string has an index of 1.

┌─────────────────────────────────┐
│ substring(traits.phone, 6, 12)  │ ->  phone
└─────────────────────────────────┘

The syntax is substring(s, start, length), where:

• s is the input string from which you want to extract a substring.
• start is the position in the string where extraction begins, with the first character at position 1.
• length is the number of characters to extract from the starting position. If omitted, the function will extract the substring from start to the end of the string.

For example:

substring('Alice Johnson', 0, 5)  -> 'Alice'
substring('Alice Johnson', 1, 5)  -> 'Alice'
substring('Alice Johnson', 7)     -> 'Johnson'
substring('Alice Johnson', 7, 6)  -> 'Johnson'
substring('Alice Johnson', 7, 20) -> 'Johnson'
substring('Alice Johnson', 20, 5) -> '' 
substring(null, 1, 5)             -> null

Note:

• If any argument is null, the function returns null.
• If the starting position start exceeds the length of the string s, the result is an empty string.
• The length length can be zero but cannot be negative.
• When length is greater than the number of characters remaining from start to the end of the string, the function returns the substring from start to the end of the string.

The start and length arguments should be of type integer, and the s argument and the result are of type string.

trim

The trim function returns its argument with all leading and trailing Unicode whitespace removed. For example:

┌─────────────────────────────────┐
│ trim(email)                     │ ->  email
└─────────────────────────────────┘

If its argument is null, the function returns null. For example:

trim("  info@example.com ") -> "info@example.com"
trim("\t   hello world\n") -> "hello world"
trim(null)  -> null

Both the argument and the result of the trim function have type string.

upper

The upper function returns its argument with all letters in upper case. For example:

┌─────────────────────────────────┐
│ upper(address.countryCode)      │ ->  country
└─────────────────────────────────┘

If its argument is null, the function returns null. For example:

upper("usa") -> "USA"
upper(null)  -> null

The argument of the upper function should have type string, and the result has type string.