Articles in this section

Data Transformation Supported Functions

Updated
Follow

About this article

This article outlines the JMESPath functions supported by Templafy's External Data Source Transformation.

Functions in this article:

Prerequisites

 
  • Admin access in Templafy tenant
  • Data Connector and Data Transformation modules enabled
  • External Data Source configured

 

Supported Functions

These functions are part of the JMESPath community library in addition to some custom Templafy functions.

 

abs

Returns the absolute value of the provided number argument

Syntax: 

abs(number)

Examples:

Input Expression Output
{"NumberValue": 10} {AbsValue: abs(NumberValue)} {"AbsValue": 10}
{"NumberValue": -5} {AbsValue: abs(NumberValue)} {"AbsValue": 5}

 

avg

Returns the average of the elements in a provided array. Values in the array must be numbers.

Syntax:

avg(array[number])

Examples:

Input Expression Output
{"NumberArray": [2,4,30]} {AverageA: avg(NumberArray)} {"AverageA": 12}

{"Num_A": 2,

  "Num_B": 4,

  "Num_C": 30}

 {AverageB: avg([Num_A,Num_B,Num_C])} {"AverageB": 12}

 

ceil

Returns the next highest integer value by rounding up a decimal number.

Syntax:

ceil(number)

Examples:

Input Expression Output
{"Number": 6.7} {CeilValue: ceil(Number)}

{CeilValue: 7} 

{"Number": 3.24}

 {CeilValue: ceil(Number)} {CeilValue: 4}

 

contains

Returns true if the subject (array or string) contains the provided search value. Returns false if not.

If searching an array, the function returns true if the search is equal to one of the elements.

Syntax:

contains(arrayOrString, search_value)

Examples:

Input Expression Output

{"EmployeeNotes": "Steven was promoted to sales manager in March.  Mr. Buchanan has completed the courses Successful Telemarketing and International Sales Management."}

 {Contains_A: contains(EmployeeNotes, 'International Sales Management')}

{"Contains_A": true} 
{"OrderDetails": [

   {

    "OrderID": 1234,

    "ProductID": "11"

   },

  {

    "OrderID": 1248,

    "ProductID": "42"

   }

 ]}

{Contains_B: contains(OrderDetails[*].ProductID, '11')}

 

 

{Contains_C: contains(OrderDetails[*].ProductID, '4')

{"Contains_B":true}

 

 

 

{"Contains_C": false}

 

ends_with

Returns true if the subject ends with the specified string, otherwise returns false

Syntax:

ends_with(Subject_string, Search_string)

Examples:

Input Expression Output
{"Title": "Sales Manager"} {IsManager: ends_with(Title, 'Manager')}

{"IsManager": true} 

{"Title": "Manager Assistant"}

 {IsManager: ends_with(Title, 'Manager')} {"IsManager": false}

 

find_first

Returns the zero-based index of the first occurrence where the searched substring appears in the subject (or null if it does not appear). Start and End parameters are optional and allow for restricting the index range in which the substring must be found. If omitted, start will default to 0, and end will default to length(subject)-1, which is the end of the subject string.

Syntax:

find_first(Subject_string, Substring, Start, End)

Examples:

Input Expression Output
{"Location": "201-Office-New York City"} {StartIndex: find_first(Location, 'Office-')}

{"StartIndex": 4} 

{"ProdList": "AB|CD|EF|GH"}

 {StartIndex: find_first(ProdList, '|')} {"StartIndex":2}

{"ProdList": "AB|CD|EF|GH"}

 {StartIndex: find_first(ProdList, '|', 3)} {"StartIndex":5}

{"ProdList": "AB|CD|EF|GH"}

 {StartIndex: find_first(ProdList, 'GH', 0,10)} {"StartIndex":9}

{"ProdList": "AB|CD|EF|GH"}

 {StartIndex: find_first(ProdList, 'GH', 0,8)} {"StartIndex": null}

 

find_last

Returns the zero-based index of the last occurrence where the searched substring appears in the subject (or null if the it does not appear). Start and End parameters are optional and allow for restricting the index range in which the substring must be found. If omitted, start will default to 0, and end will default to length(subject)-1, which is the end of the subject string.

Syntax:

find_last(Subject_string, Substring, Start, End)

Examples:

Input Expression Output
{"Location": "201-Office-New York City"} {Index: find_last(Location, '-')}

{"Index": 10}

{"ProdList": "AB|CD|EF|GH"}

 {Index: find_last(ProdList, '|')} {"Index": 8}

{"ProdList": "AB|CD|EF|GH"}

 {Index: find_last(ProdList, '|',0,7)} {"Index": 5}

{"ProdList": "AB|CD|EF|GH"}

 {Index: find_last(ProdList, 'GH',0,9)} {"Index": null}

 

floor

Returns the next lowest integer value by rounding down a decimal number.

Syntax:

floor(number)

Examples:

Input Expression Output
{"Number": 6.7} {FloorValue: floor(Number)}

{"FloorValue: 6}

{"Number": 3.24}

 {FloorValue: floor(Number)} {"FloorValue: 3}

 

format

Custom Templafy function.

Formats the provided number or datetime value into a string following the defined format and optional culture parameters. 

For numbers, format strings are defined here

For datetime values, format strings are defined here

Culture is a language culture name defined by ISO-63901. If the culture parameter is omitted, then invariant culture is used.

Syntax:

format(number, format, culture) or format(datetime, format, culture)

Examples:

Input Expression Output
{"UnitPrice": 18.2000} {Price: format(UnitPrice, 'n2', 'en-US')}

{"Price": "18.20"}

{"UnitPrice": 18.2000}

 {Price: format(UnitPrice, 'n2', 'es-ES')} {"Price": "18,20"}

{"UnitPrice": 18.2000}

 {Price: format(UnitPrice, 'n')} {"Price": "18.2000"}

{"Date": "02-19-2021"}

 {FormatDate: format(to_datetime(Date, 'MM-dd-yyyy', 'en-US'), 'g', 'en-US')} {"FormatDate":"'2/19/2021 12:00 AM"}

 

from_items

Returns an object from a provided array of key value pairs.

Syntax:

from_items(array[])

Examples:

Input Expression Output
{"Orders": [["ProdA",5],["ProdB",2],["ProdC",10]]} {OrderAmt: from_items(Orders)}

{"OrderAmt": {"ProdA": 5, "ProdB": 2, "ProdC": 10}}

 

group_by

Groups an array of objects using the provided expression parameter as the the grouping key. The result is an object whose keys are the unique set of strings from the grouping parameter, and the values are an array of objects matching the specified group criteria. 

Any object from the input that does not match the group criteria will not be included in the output.

Syntax:

group_by(array[object], expression)

Examples:

Input Expression Output

{"orders": [

  {

    "OrderID": 1234,

    "ProductID": "11"

   },

  {

    "OrderID": 1248,

    "ProductID": "42"

   },

  {

    "OrderID": 1253,

    "ProductID": "11"

   },

 ]}

 {GroupedOrders: group_by(orders, &ProductID)} 

{"GroupedOrders": {

  "11": [

   {

    "OrderID": 1234,

    "ProductID": "11"

    },

   {

    "OrderID": 1253,

    "ProductID": "11"

    }],

  "42": [

   {

    "OrderID": 1248,

    "ProductID": "42"

    }]

  }

}

 

items

Returns an array of key-value pairs for a provided input object. Each pair is returned as a two item array where the first item is the key and the second item is the value. 

Syntax:

items(object{})

Examples:

Input Expression Output
{"OrderAmt": {"ProdA": 5, "ProdB": 2, "ProdC": 10}} {Orders: items(OrderAmt)}

{"Orders": [["ProdA",5],["ProdB",2],["ProdC",10]]}

 

join

Returns all elements of a the specified array joined together using the provided argument as a separator between each

Syntax:

join(Separator, array[string])

Examples:

Input Expression Output
{"Products":["AB", "CD", "EF", "GH"]} {ProdList: join(', ', Products)}

{"ProdList": "AB, CD, EF, GH"}

{"FirstName": "Lisa",

  "LastName": "Crosby"} 

 {FullName: join(' ',[FirstName, LastName])} {"FullName": "Lisa Crosby"}

 

keys

Returns an array containing the keys of a provided object. 

Note: the keys associated with a provided object are inherently unordered. This means that keys will not be returned in any specific order.

Syntax:

keys(object{})

Examples:

Input Expression Output
{"EmptyObject": {}} {Keys: keys(EmptyObject)}

{"Keys":[]} 

{"Products": {"AB": 2, "CD":5, "EF": 10}}

 {ProdNames: keys(Products)} {ProdNames: ["AB","EF","CD"]}

 

length

Returns the length of the given argument using the following types rules:

  1. string: returns the number of code points in the string (this essentially means number of Unicode characters)
  2. array: returns the number of elements in the array
  3. object: returns the number of key-value pairs in the object

Syntax:

length(subject)

Examples:

Input Expression Output
{"String": "This is a string!"} {Length: length(String)}

{"Length": 17}

{"Products":["AB", "CD", "EF", "GH"]}

 {NumProducts: length(Products)} {"NumProducts": 4}

{"OrderDetails": [

   {

    "OrderID": 1234,

    "ProductID": "11"

   },

  {

    "OrderID": 1248,

    "ProductID": "42"

   }

 ]}

 {NumOrders: length(OrderDetails)} {"NumOrders": 2}

{"OrderAmt": {"ProdA": 5, "ProdB": 2, "ProdC": 10}}

 {NumOrders: length(OrderAmt)} {"NumOrders":3}

 

lower

Returns the lowercase provided string

Syntax:

lower(string)

Examples:

Input Expression Output
{"String": "VALUE"} {NewString: lower(String)}

{"NewString": "value"}

{"String": "User TYped string"}

 {NewString: lower(String)} {"NewString": "user typed string"}

 

map

Applies the specified expression to each element in the provided array. 

Syntax:

map(expression, array[])

Examples:

Input Expression Output

{"OrderDetails": [

   {

    "OrderID": 1234,

    "Price": 1400

   },

  {

    "OrderID": 1248,

    "Price": 980

   },

  {

    "OrderID": 1249,

    "OtherField": "value"

   }

 ]}

 {DiscountedPrices: map(&Price*`0.85`, Order_Details)}

{"DiscountedPrices": [1190,833,null]}

 

max

Returns the highest found number in the provided array argument. An empty array will return a value of null.

Syntax:

max(array[number]) or max(array[string])

Examples:

Input Expression Output
{"Numbers": [-2, 3.2, 1.01]} {MaxNumber: max(Numbers)}

{"MaxNumber": 3.2}

{"Strings": ["a","b","c"]}

 {MaxString: max(Strings)} {"MaxString": "c"}

 

max_by

Returns the maximum element in a provided array using the defined expression as the comparison key. 

Syntax:

max_by(array[elements], expression)

Examples:

Input Expression Output

{"OrderDetails": [

   {

    "OrderID": 1234,

    "Quantity": 5

   },

  {

    "OrderID": 1248,

    "Quantity": 41

   },

  {

    "OrderID": 1248,

    "Quantity": 27

   }

 ]}

 {MaxOrder: max_by(OrderDetails, &Quantity)}

{"MaxOrder": {

  "OrderID": 1248,

  "Quantity": 41

  }

}

 

merge

Returns one object with the defined objects merged. This function combines multiple objects into one where the first object is the base and the subsequent objects override or add to the base object.

Syntax:

merge(object1, object2,...)

Examples:

Input Expression Output

{

"Object_one": {

"one": "a",

"two": "b"

  },

"Object_two":{
"three": "c",

"four": "d"

  }

}

 {FullObject: merge(Object_one, Object_two)}

{"FullObject": {

"one": "a",

"two": "b",

"three": "c",

"four": "d"

  }

} 

{

"Object_one": {

"one": "a",

"two": "b"

  },

"Object_two":{
"three": "c",

"four": "d"

  },

"Object_three":{

 "three": "override",

  "five": "e"

 }

}

 {FullObject: merge(Object_one, Object_two, Object_three)}

{"FullObject": {

"one": "a",

"two": "b",

"three": "override",

"four": "d",

"five": "e"

 }

} 

 

min

Returns the lowest found number in the provided array.

Syntax:

min(array[number]) or min(array[string])

Examples:

Input Expression Output
{"Numbers": [-2, 3.2, 1.01]} {MinNumber: min(Numbers)}

{"MinNumber": -2}

{"Strings": ["a","b","c"]}

 {MinString: min(Strings)} {"MinString":"a"}

 

min_by

Returns the minimum element in a provided array using the defined expression as the comparison key.

Syntax:

min_by(array[elements], expression)

Examples:

Input Expression Output

{"OrderDetails": [

   {

    "OrderID": 1234,

    "Quantity": 5

   },

  {

    "OrderID": 1248,

    "Quantity": 41

   },

  {

    "OrderID": 1248,

    "Quantity": 27

   }

 ]}

 {MinOrder: min_by(OrderDetails, &Quantity)}

{"MinOrder": {

  "OrderID": 1234,

  "Quantity": 5

  }

}

 

not_null

Returns the first argument that does not resolve to null. This function accepts one or more arguments, and will evaluate them in order until a non null argument is encountered. If all arguments values resolve to null, then a value of null is returned

Syntax:

not_null(argument1, argument2, ...)

Examples:

Input Expression Output

{"a": null,

"b": null,

"c": "string",

"d": []}

 {first_value: not_null(a, e, b, d, c)}

{"first_value": []}

{"a": null,

"b": null,

"c": "string",

"d": []}

 {first_value: not_null(a, c, b, d) {"first_value": "string"}

 

pad_left

Adds characters to the beginning of a specified string to return a string of at least the specified width. The optional pad string parameter specifies the character that will be added, but if not specified, a space will be used. 

Syntax:

pad_left(subject_string, width, pad_character)

Examples:

Input Expression Output
{"a": "string"} {padded_string: pad_left(a, `7`)}

{"padded_string": " string"}

{"a": "string"}

 {padded_string: pad_left(a, `10`, -)} {"padded_string": "----string"}

{"a": "string"}

 {padded_string: pad_left(a, `2`, -)} {"padded_string": "string"}

 

pad_right

Adds characters to the end of a specified string to return a string of at least the specified width. The optional pad string parameter specifies the character that will be added, but if not specified, a space will be used. 

Syntax:

pad_right(subject_string, width, pad_character)

Examples:

Input Expression Output
{"a": "string"} {padded_string: pad_right(a, `7`)}

{"padded_string": "string "}

{"a": "string"}

 {padded_string: pad_right(a, `10`, -)} {"padded_string": "string----"}

{"a": "string"}

 {padded_string: pad_right(a, `2`, -)}  {"padded_string": "string"}

 

replace

Replaces the occurrences of the specified old substring with the defined new substring in a given subject string. 

If the count option parameter is added, it will determine how many occurrences of the old substring are to be replaced. If omitted, all instances will be replaced.

Syntax:

replace(subject_string, old_substring, new_substring, count)

Examples:

Input Expression Output
{"Summary": "The SVP of Marketing, James, is in charge of all marketing efforts. James reports results to the CEO every quarter. One of James's duties is social marketing."} {New_Summary: replace(Summary, 'James', 'Amanda')

{"New_Summary": "The SVP of Marketing, Amanda, is in charge of all marketing efforts. Amanda reports results to the CEO every quarter. One of Amanda's duties is social marketing."}

 

reverse

Reverses the order of the specified string or array.

Syntax:

revers(string) or reverse(array[])

Examples:

Input Expression Output
{"string": "AB|CD|EF"} {rev_string: reverse(string)}

{"rev_string": "FE|DC|BA"}

{"array": [1,2,3,4]}

 {rev_array: reverse(array)} {"rev_array": [4,3,2,1]}

 

sort

Returns a sorted list of elements in an array. The input array must be a list of strings or numbers.

Syntax:

sort(array[number]) or sort(array[string])

Examples:

Input Expression Output
{"Numbers": [4, 7, -2, 0.1]} {OrderedNumbers: sort(Numbers)} 

{"OrderedNumbers": [-2, 0.1, 4, 7]}

{"Strings": ["Orange", "Banana", "Apple", "Grape"]}

 {OrderedStrings: sort(Strings) {"OrderedStrings":["Apple", "Banana", "Grape", "Orange"]}

 

sort_by

Sorts an array by the provided expression as the sort key. For each element of the array, the expression is evaluated and the resulting value is used when sorting the elements. The result of the expression must be a number or a string.

Syntax:

sort_by(array[elements], expression)

Examples:

Input Expression Output

{"people": [

  {"name":"AB", "tenure": 2},

  {"name":"CD", "tenure": 7},

  {"name":"EF", "tenure": 1}

]}

 {PeopleSort: sort_by(people, &tenure)}

{"PeopleSort": [

  {"name":"EF", "tenure": 1},

  {"name":"AB", "tenure": 2},

  {"name":"CD", "tenure": 7}

]}

 

split

Given a subject string, this function breaks up the string on occurrence of the provided search string and returns an array containing each partial string between occurrences of the search string.

The optional count parameter specifies the max number of split points in the in string. If omitted, all instances of the search parameter are split

Syntax:

split(SubjectString, SearchString, Count)

Examples:

Input Expression Output
{"ProdList": "AB|CD|EF|GH"} {SplitArray: split(ProdList, '|')}

{"SplitArray": ["AB", "CD", "EF", "GH"]}

{"String": "red-blue-yellow-green-orange-purple"}

 {SplitArray: split(String, '-', `3`)} {"SplitArray": ["red", "blue", "yellow", "green-orange-purple"]}

 

starts_with

Returns true if the provided subject string starts with the specified prefix string, otherwise returns false

Syntax:

starts_with(SubjectString, prefix)

Examples:

Input Expression Output
{"String": "Value"} {result: starts_with(String, 'va')}

{"result": true}

 

sum

Returns the sum of the provided array

Syntax:

sum(array[number])

Examples:

Input Expression Output
{"Numbers": [1, 4, 20, 5]} {Total: sum(Numbers)}

{"Total": 30}

{"OrderDetails": [

   {

    "OrderID": 1234,

    "Quantity": 11

   },

  {

    "OrderID": 1248,

    "Quantity": 42

   }

 ]}

 {Total: sum(OrderDetails[*].Quantity)} {"Total": 53}

 

to_array

Returns a one element array containing the passed argument. If argument is already an array, the passed in value will be returned.

Syntax:

to_array(argument)

Examples:

Input Expression Output
{"String": "String Value"} {Array: to_array(String)}

{"Array": ['String Value']}

{"Number": 20}

  {Array: to_array(Number)} {"Array": [20]}

 

to_datetime

Custom Templafy function

Returns a standardized datetime object from a provided string. The format parameter is expected as a datetime format string to use for parsing the provided string following this standard. The culture parameter is a language culture name to define the output date format based on ISO-639-1.

Syntax:

to_datetime(string, format, culture)

Examples:

Input Expression Output
{"DateString": "3 Feb 2021"} {date: to_datetime("DateString, 'd MMM yyyy', 'en-US')}

{"date":"2021-02-03T00:00:00"}

 

    

 

to_number

Returns the parsed number of an input string. If a number is passed, that number value will be returned. Any other data types passed will return null.

Syntax:

to_number(argument)

Examples:

Input Expression Output
{"OrderQuantity": "47"} {OrdersNumber: to_number(OrderQuantity)}

{"OrdersNumber": 47}

{"OrderQuantity": "47 units"}

 {OrdersNumber: to_number(OrderQuantity)} {"OrdersNumber": null}

 

to_string

Returns the JSON encoded value of the object. If a string is provided, will return the passed in string value.

Syntax:

to_string(argument)

Examples:

Input Expression Output
{"OrderID": 1234} {StringOrder: to_string(OrderID)}

{"StringOrder": "1234"}

{"ShippingID": null}

 {ShippingString: to_string(ShippingID)} {"ShippingString": "null"}

{"Numbers": [1, 4, 20, 5]}

 {String: to_string(Numbers)} {"String": "[1, 4, 20, 5]"}

 

trim

Removes the leading and trailing characters from a given subject string found in the optional characters parameter. If this is not present, whitespace characters are removed from the subject string. 

Syntax:

trim(SubjectString, characters)

Examples:

Input Expression Output
{"Email": "email@example.com  " {CleanEmail: trim(Email)}

{"CleanEmail": "email@example.com"}

{"String": "subject string"}

 {NewString: trim(String, 'ugs')} {"NewString": "bject strin"}

 

trim_left

Removes the leading characters from a given subject string found in the optional characters parameter. If the characters parameter is not present, whitespace characters are removed from the subject string.

Syntax:

trim_left(SubjectString, characters)

Examples:

Input Expression Output
{"Email": "  name@example.com "} {NewEmail: trim_left(Email)}

{"NewEmail": "name@example.com "}

{"String": "subject string"}

 {NewString: trim_left(String, 'ugs')} {"NewString": "bject string"}

 

trim_right

Removes the trailing characters from a given subject string found in the optional characters parameter. If the characters parameter is not present, whitespace characters are removed from the subject string.

Syntax:

trim_right(SubjectString, characters)

Examples:

Input Expression Output
{"Email": " name@example.com  "} {NewEmail: trim_right(Email)}

{"NewEmail":
" name@example.com"}

{"String": "subject string"}

 {NewString: trim_right(String, 'ugs')} {"NewString": "subject strin"}

 

type

Returns the JavaScript data type of the provided argument as a string value. Possible return values are:

  • number
  • string
  • boolean
  • array
  • object
  • null

Syntax:

type(argument)

Examples:

Input Expression Output
{"subject": false} {subjectType: type(subject)}

{"subjectType": "boolean"}

{"subject": "customer name"}

 {subjectType: type(subject)} {"subjectType": "string"}

{"subject": ["abc":123, "def":456]}

 {subjectType: type(subject)} {"subjectType": "array"}

 

upper

Returns the uppercase subject string.

Syntax:

upper(SubjectString)

Examples:

Input Expression Output
{"String": "string value"} {UpperString: upper(String)}

{"UpperString": "STRING VALUE"}

 

values

Returns the values of the provided object.

Note: the values associated with a provided object are inherently unordered. This means that values will not be returned in any specific order.

Syntax:

values(object{})

Examples:

Input Expression Output
{"Products": {"AB": 2, "CD":5, "EF": 10}} {ProdValues: values(Products)} 

{"ProdValues": [5, 2, 10]}

 

zip

Accepts one or more arrays as arguments and returns an array of arrays in which the i-th array contains the i-th element from each of the argument arrays. The returned array is truncated to the length of the shortest argument array.

Syntax:

zip(array[any], array[any], ...)

Examples:

Input Expression Output

{"array_one":[1,2,3],
 "array_two": [4,5,6]}

 {zippedArray: zip(array_one, array_two)}

{"zippedArray": [

  [1,4], 

  [2,5],

  [3,6]

]}

{"A_Products": ["AB": 2, "CD":5, "EF": 10],

 "B_Products": ["GH": 7, "IJ": 4, "KL": 12, "MN":42]}

 {CompProducts: zip(A_Products, B_Products)}

{"CompProducts": [

["AB":2, "GH": 7],

["CD": 5, "IJ": 4],

["EF": 10, "KL":12]

]}

{"OrderDetails": [

   {

    "OrderID": 1234,

    "Quantity": 11

   },

  {

    "OrderID": 1248,

    "Quantity": 42

   }

 ]}

 {ZippedDetails: zip(OrderDetails)}

{"ZippedDetails":[

[{

    "OrderID": 1234,

    "Quantity": 11

   }],

[{

    "OrderID": 1248,

    "Quantity": 42

   }]

]}

 

Related articles

 

 

Related articles

Comments

0 comments

Article is closed for comments.