JSON XML CSV Data Field Functions

The function takes 2 arguments:
json_path: Set the path and name to the JSON array from the record being processed, or from a parent record found using the parent_count argument.
parent_count: Set the number of steps to navigate up the JSON tree from the position of the record being processed to navigate from using the JSON path. This allows traversal up the JSON tree to find arrays and its length.

Below is an example of JSON data obtained in entirety from a data source for a given data export.

If the JSON RecordSet Path is set to $ then the following will be outputted:

If the JSON RecordSet Path is set to sizes[*] then the following will be outputted:

For each record found with the sizes[*] path in the function below it first traverses up the JSON tree (3 steps) then obtains length in a parent object's "sizes" array.

The function takes 3 arguments:
json_path: Set the path and name to the JSON array from the record being processed, or from a parent record found using the parent_count argument.
parent_count: Set the number of steps to navigate up the JSON tree from the position of the record being processed to navigate from using the JSON path. This allows traversal up the JSON tree to find arrays in parent objects.
delimiter: Set the text that will be placed in between each value obtained from the array.

Below is an example of JSON data obtained in entirety from a data source for a given data export.

If the JSON RecordSet Path is set to $ then the following will be outputted:

This function will ignore outputting the array value 'small' with the other values.

If the JSON RecordSet Path is set to sizes[*] then the following will be outputted:

For each record found with the sizes[*] path in the function below it first traverses up the JSON tree (3 steps) then joins values in a parent object's "sizes" array.

The function can take 1 or more arguments with each argument either the name of a JSON field in the record, or literal text enclosed with either single quote or double quote characters. The output of each argument is joined together to make one final mathematical equation which is evaluated out. The mathematical operators supported are + - / % ( )

The function takes 1 argument:
input_arg: the field name or literal text to read in and replace control characters with.

The function takes the following 3 arguments:
input_arg: the field name or literal text to read in that contains the date value.
date_format: the field name or literal text that contains text on how the input text has its date format parsed. This date format should conform to the Dot.NET language's date format conventions or otherwise have the value 'LONG' set to denote that the value in the input_arg is a long integer (64bit integer) that stores the amount of milliseconds past since the 1970-01-01 12am UTC date time epoch.
output_type: Set to 'LONG' or otherwise place a date format string that conforms to the Dot.NET language's date format conventions. If Set to LONG then the datetime value read in will be converted to a long integer number that represents the amount of milliseconds past since the 1970-01-01 12am UTC date time epoch. If set to a date format string then the datetime value will be formatted to text with the date in the local time zone that is set for the computer that the Connector is installed on.

First example converts date time text (in the local time zone) into a long integer that displays the amount of seconds since the 1970-01-01 12am UTC epoch (also known as unix time).

Second example converts a long integer number storing the amount of milliseconds since the 1970-01-01 12am UTC epoch into formatted date time text in the local time zone.

Third example converts date time formatted text into date only formatted text.

Fourth example converts date time formatted text into time only formatted text.

Fifth example converts a date time formatted text into a date time formatted text with day and month names, with time shown as a 12 hour clock

The function takes no arguments

In this first example if the current date time was Thursday July 26 2018 08:43:04 in the UTC time zone then it would return the date time as the shown number.

The function takes 2 arguments:
input_arg: the field name or literal text to read in and decode.
encoding_type: set to one of the following:

• XML
  Replaces encoded XML or HTML characters to the original text form, such as changing &gt characters to >; and & to &
• JSON
  Removes characters that are used for escaping within JSON data, such as replacing " to "
• BASE64
  Decodes text encoded in base64 to its original text form.
• URL
  Replaces encoded chracters within URL text, such as replacing the %2F characters with a / slash character
• SQL_SINGLE_QUOTE
  Removes characters that are used for escaping within SQL data with text wrapped in single quotes, such as replacing ' to '
• SQL_DOUBLE_QUOTE
  Removes characters that are used for escaping within SQL data with text wrapped in double quotes, such as replacing " to '

The function takes 2 arguments:
input_arg: the field name or literal text to read in and encode.
encoding_type: set to one of the following:

• XML
  Replaces characters that would cause XML or HTML markup to break, such as changing > character to > and & to &
• JSON
  Replaces characters that would cause JSON data to break, such as escaping " characters with "
• BASE64
  Encodes the text into a Base 64 text string.
• URL
  Replaces characters to allow literal text to be placed wihtin a URL, such as replacing forward slash characters with %2f. Note that any encoded characters will contain letters in lowercase
• URL_UPPERCASE
  Replaces characters to allow literal text to be placed wihtin a URL, such as replacing forward slash characters with %2F. Note that any encoded characters will contain letters in uppercase.
• SQL_SINGLE_QUOTE
  Replaces characters that allow text to be set within single quotes of an SQL query, esaping slash characters and single quote characters
• SQL_DOUBLE_QUOTE
  Replaces characters that allow text to be set within double quotes of an SQL query, esaping slash characters and double quote characters
• SHA1
  Converts characters into scrambled text using the Secure Hash Algorithm 1 (SHA-1) cryptographic hash function. This scrambled text cannot be decrypted (i.e. one way encryption).
• SHA256
  Converts characters into scrambled text using the Secure Hash Algorithm 2 (SHA-2) cryptographic hash function 256bits in size. This scrambled text cannot be decrypted (i.e. one way encryption).

The function takes 4 arguments:
input_arg: the field name or literal text to read in and encrypt.
encryption_method: set to one of the following:

• RSA-SHA1-SIGN
  Converts characters into scrambled text using the Secure Hash Algorithm 1 (SHA-1) cryptographic hash function. Then encrypts the hash'd text with the 2048 bit RSA private key provided to a generate signed encrypted text. Note that this signed encrypted cannot be decrypted, instead it can only verified by another party that contains the RSA public key. You can use tools such as OpenSSL to generate private and public RSA keys. Only private key needs to be set in function for this method and the public key argument can be left empty.
• RSA-SHA256-SIGN
  Converts characters into scrambled text using the Secure Hash Algorithm 2 (SHA-2) cryptographic hash function 256 bits in size. Then encrypts the hash'd text with the 2048 bit RSA private key provided to generate a signed encrypted text. Note that this signed encrypted cannot be decrypted, instead it can only verified by another party that contains the RSA public key. You can use tools such as OpenSSL to generate private and public RSA keys. Only private key needs to be set in function for this method and public key argument can be left empty.
• RSA-SHA384-SIGN
  Converts characters into scrambled text using the Secure Hash Algorithm 2 (SHA-2) cryptographic hash function 384 bits in size. Then encrypts the hash'd text with the 2048 bit RSA private key provided to generate a signed encrypted text. Note that this signed encrypted cannot be decrypted, instead it can only verified by another party that contains the RSA public key. You can use tools such as OpenSSL to generate private and public RSA keys. Only private key needs to be set in function for this method and the public key argument can be left empty.
• RSA-SHA512-SIGN
  Converts characters into scrambled text using the Secure Hash Algorithm 2 (SHA-2) cryptographic hash function 512 bits in size. Then encrypts the hash'd text with the 2048 bit RSA private key provided to generate a signed encrypted text. Note that this signed encrypted cannot be decrypted, instead it can only verified by another party that contains the RSA public key. You can use tools such as OpenSSL to generate private and public RSA keys. Only private key needs to be set in function for this method and the public key argument can be left empty.
• RSA-MD5-SIGN
  Converts characters into scrambled text using the MD5 cryptographic hash function 128 bits in size. Then encrypts the hash'd text with the 2048 bit RSA private key provided to generate a signed encrypted text. Note that this signed encrypted cannot be decrypted, instead it can only verified by another party that contains the RSA public key. You can use tools such as OpenSSL to generate private and public RSA keys. Only the private key needs to be set in function for this method and the public key argument can be left empty.

private_key: Text that contains the private key that may be used to encrypt the plain text data specified in the input arg. The private key may need to conform to certain structure based on the encryption_method set to encrypt the plain text.
public_key: Text that contains the public key that may be used to encrypt the plain text data specified in the input arg. The public key may need to conform to certain structure based on the encryption_method set to encrypt the plain text.

Example returns text encrypted and signed using the RSA SHA1 cipher. Note that the private key and output text have been cut off for brevity.

Example returns text encrypted and signed using the RSA SHA256 cipher. Note that the private key and output text have been cut off for brevity.

Example returns text encrypted and signed using the RSA SHA384 cipher. Note that the private key and output text have been cut off for brevity.

Example returns text encrypted and signed using the RSA SHA512 cipher. Note that the private key and output text have been cut off for brevity.

Example returns text encrypted and signed using the RSA MD5 cipher. Note that the private key and output text have been cut off for brevity.

The function takes 3 arguments:
input_arg: The field name or literal text to read in and hash/scramble
secret_key: The field name or literal text that contains the secret key that is scrambled with the input_arg.
hash_cipher: The hashing function/cipher that is used to do the hashing/scrambling of the text. Set it to either SHA1, SHA256, SHA512, MD5

Example returns text hashed using the SHA1 cipher

Example returns text hashed using the SHA2 (256bit) cipher

Example returns text hashed using the SHA2 (512bit) cipher

Example returns text hashed using the MD5 cipher

The function takes 5 arguments:
compare_arg1: The first field name or literal text to read in and compare against the second
compare_arg2: The second field name or literal text to read in and compare against the first
then_arg: Name of the JSON field in the record or literal text to be outputted when the comparing arguments match
else_arg: Name of the JSON field in the record or literal text to be outputted when the comparing arguments fail to match
operator: Operator used to compare the two arguments. must be set to one of the following

• ==S
  Checks if both values match each other, character for character in each value's string
• ==N
  Checks if both values match each other numerically. Each value must evaluate out to a number first.
• !=S
  Checks if both values do not match each other, character for character in each value's string
• !=N
  Checks if both values do not match each other numerically after each value has been converted to a number.
• >
  Checks if the first value is greater than the second value after both values have been converted to numbers.
• >=
  Checks if the first value is greater than or equal to the second value after both values have been converted to numbers.
• <
  Checks if the first value is less than the second value after both values have been converted to numbers.
• <=
  Checks if the first value is less than or equal to the second value after both values have been converted to numbers.
• .*
  Checks if the first value starts with the text given in the second value.
• *.
  Checks if the first value ends with the text given in the second value.
• *.*
  Checks if the first value contains the text given in the second value.
• REGEX
  Checks if the first value matches a regular expression set within the second value.

The function takes 2 arguments:
input_arg: the field name or literal text to read in and search on
matching_arg: the text to match on

The function takes 1 argument:
input_arg: the field name or literal text to read in and obtain the length for

The function takes 4 arguments:
input_arg: The field name or literal text to read in and pad text with.
max_length: The number of characters that the text given must be padded to.
(optional)padding_text: The text characters to pad before (left) or after (right) of the text provided in the input arg. By default set to a space character. Note that if multiple characters are set in the argument then the sequence of text characters will appear each time a pad position needs to be set within the text being padded. See example below.
(optional)padding_direction: The position where the padding should be placed. Either set to LEFT (before input_arg text) or RIGHT (after input_arg text). Default value is LEFT.

Example returns a text left padded with spaces up to 10 characters in total length

Example returns a number left padded with zeros to 8 characters in total length

Example returns a number text right padded with zeros to 9 characters in total length

Example returns a text that has no padding since the text given is greater than the length that needs to be padded

Example returns a text that has has been left padding with each of the 4 pad characters being placed with the text AB. Note the final text is longer than the 10 characters due to the pad text being multiple characters in length

The function takes no arguments

The function takes the 1 argument:
parent_count: Set the number of steps to navigate up the JSON tree from the position of the record being processed to find the record index of within the array. Set the value to 0 to get the index/position of the immediate array that a record exists within.

Below is an example of JSON data obtained in entirety from a data source for a given data export.

If the JSON RecordSet path is set to sizes[*] then the following will be outputted:

If the JSON RecordSet Path is set to sizes[*].colours[*] the following would be output for across all records. This shows that for each colour record the function steps up to the parent "sizes" array to find the position of the size record that the colours array records are indirectly children of.

If no parent array can be found at the level specified then the function will return -1. This can also be used to determine if a record is within an array.

The function takes the following 2 arguments:
input_arg: the field name or literal text to read in that contains the name of the JSON property to read from the parent node.
parent_count: Set literal text containing a number to denote how many levels of parents to traverse up to obtain the value with the given name. If the value is set to -1 then the function will traverse up the JSON tree until it finds an object with the name set in the input_arg

Below is an example of JSON data obtained in entirety from a data source for a given data export.

If a data export is configured to get all address records from within any customer accounts. then a rule such as customers.[*].addresses[*] would return an array of address records.

If for each address record you wanted to get associated customer data then you would use the PARENT_NODE function like so:

If for a field in the address record you wanted to get the country at the top most level of the JSON structure, then you would use the following function:

The function takes the 1 argument:
parent_count: Set literal text containing a number to denote how many levels of parents to traverse up to obtain the key name of the parent node.

Below is an example of JSON data obtained in entirety from a data source for a given data export.

If a data export is configured to get all address records from within any customer accounts. then a rule such as customers.[*].addresses[*] would return an array of address records.

If for each address record you wanted to get the name of parent key that holds the array of customer records you would use the PARENT_NODE_NAME function like so:

The function takes 3 arguments:
(optional) min_number_arg: Optional argument that specifies the the lowest number than can be generated. By default is set to 0.
(optional) max_number_arg: Optional argument that specifies the the highest number than can be generated. By default is set to 1.
(optional) decimal_place_rounding_arg: Optional argument that specifies how many decimal places the random number should be rounded to. By default set to 10.

The function takes no arguments

The function takes the 1 argument:
(optional) offset_amount: Set literal text containing a number that is added to the index number being returned. For example set the number 1 so that the 1st record returned by the function is the number 1 instead of 0.

Below is an example of JSON data obtained in entirety from a data source for a given data export.

If the JSON RecordSet path was set to customers[*] Then the function would return the following values for the found records:

The function takes 4 arguments:
input_arg: the field name or literal text to read in
matching_arg: the text to match on
replace_arg: the text to replace the matched text with.
replace_method_arg: (optional) the method to use to match the text on. Set to either LITERAL, or REGEX. If set to LITERAL then the function will match on the character sequence exactly set within the matching_arg argument. This is the default behaviour. If set to REGEX then the value set within the matching_arg needs to be a regular expression pattern in the .NET supported format. Using regular expression matching allows many different rules to be specified to determine which characters within the input text are matched on and replaced. Read more on .NET regular expressions.

The function takes 3 arguments:
input_arg: (optional) the field name or literal text to read in and convert to a number
decimal_places_arg: (optional) the number of decimal placees to round the number by. By default it is set to 4.
rounding_method_arg: (optional) the method to round the number when the number is at a mid point. Set to either 'UP' or 'DOWN'. If set to UP (the default option) then a number such as 5.5 would be rounded up to 6. If set to DOWN then a number such as 5.5 would be rounded down to 5.

The function takes 3 arguments:
input_arg: the field name or literal text to read in and cut text from
start_index_arg: an integer number that specifies the position of the text in the input_arg to cut all text before it. If the number is less than 0 then it will be set to 0 (meaning no text will be cut).
(optional)end_index_arg: an integer number that specifies the position of the text in the input_arg to cut all text after it. If the number is greater than the length of the input_arg text value then it will be set to the length of the string (meaning no text at its end will be cut).

To cut the first 5 characters of text you would set the function as so

To cut the first 5 characters of text and the last 5 characters you would set the function to:

If the starting index is negative then no characters will be cut from the start of the string:

If the end index is smaller than the start index then a blank value will be returned:

If the end index is larger than the length of the text then no text at the end will be cut:

The function takes 2 arguments:
input_arg: the field name or literal text to read in
(optional)text_case_arg: either 'UPPER', 'LOWER', or 'TITLE'. Set to UPPER to change input text to all upper case letters. Set to LOWER to change the input text to allow lower case letters. Set to TITLE, to make the first letter in each of the words in the input text to upper case, and all other letters in the word lower case.

Example returns text converted to all upper case.

Example returns text converted to all lower case.

Example returns text converted to all lower case.

The function takes 2 arguments:
input_arg: the field name or literal text to read in
(optional) trim_direction: the side on which to remove the white space. Set to either LEFT, RIGHT or BOTH. If this argument is not given then it will default to BOTH.