In this article, let us see how to use functions that operate over lists in DataRaptor Formulas.
We will see examples of the below ‘List’ functions.
MAX
MIN
SUM
AVG
LISTSIZE
ISBLANK
ISNOTBLANK
LIST
SORTBY
FILTER
LISTMERGE
LISTMERGEPRIMARY
NOTE: ‘FORMULAS’ tab is available in the following DataRaptor types - Extract, Transform and Load. DataRaptor of type ‘Turbo Extract’ doesn’t have a ‘FORMULAS’ tab.
The below examples are executed from a DataRaptor Transform.
FUNCTIONS:
1) MAX: Returns the maximum value from a list of numbers.
In the ‘FORMULAS’ tab, click the ‘Add Formula’ button and add a new formula.
Input:
{
"Numbers": [
{
"Item": 30
},
{
"Item": 40
},
{
"Item": 10
}
]
}Formula: MAX(Numbers:Item)
Here, ‘Numbers:Item’ passes the values 30, 40 and 10 to the MAX function.
Formula Result Path: MaxNumber
Result Mapping: After adding a formula to the ‘FORMULAS’ tab, the variable used in the ‘Formula Result Path’ should be mapped to get the expected output.
In DataRaptor Transform, the ‘Formula Result Path’ variable should be added in the ‘INPUT JSON PATH’ and mapped to a variable in the ‘OUTPUT JSON PATH’ as below.
NOTE: Similarly, mapping should be done in
‘Output’ tab in a DataRaptor Extract
‘Fields’ tab in a DataRaptor Load
Output:
In the ‘PREVIEW’ tab, the above ‘Input’ JSON data is entered and the ‘Execute’ button is clicked.
Now the following response is generated:
2) MIN: Returns the minimum value from a list of numbers.
Input:
{
"Numbers": [
{
"Item": 30
},
{
"Item": 40
},
{
"Item": 10
}
]
}Formula: MIN(Numbers:Item)
Formula Result Path: MinNumber
Output:
3) SUM: Returns the sum of a list of numbers.
Input:
{
"Numbers": [
{
"Item": 30
},
{
"Item": 40
},
{
"Item": 10
}
]
}Formula: SUM(Numbers:Item)
Formula Result Path: Total
Output:
4) AVG: Returns the average of a list of numbers.
Input:
{
"Numbers": [
{
"Item": 10
},
{
"Item": 5
}
]
}Formula: AVG(Numbers:Item)
Formula Result Path: Average
Output:
5) LISTSIZE: Returns the number of items in the list.
Input:
{
"Contacts": [
{
"FirstName": "Aaron",
"LastName": "Xavier"
},
{
"FirstName": "Zellie",
"LastName": "Woods"
},
{
"FirstName": "Mike",
"LastName": "Smith"
}
]
}Formula: LISTSIZE(Contacts)
Here ‘Contacts’ is the array whose size needs to be calculated.
Formula Result Path: Count
Output:
LIMITATION:
If we use an empty array inside the LISTSIZE function, it still returns the size as ‘1’.
To overcome this limitation, use the ‘ISBLANK’ function to check for an empty list.
Formula: IF(ISBLANK(Contacts),0,LISTSIZE(Contacts))
This formula checks if the ‘Contacts’ array is empty or not and returns the count accordingly. If empty, it returns the size as '0'; otherwise, it returns the actual count based on the number of elements in the array.
After updating the formula, if we click the ‘Execute’ button, it gives the below response.
Output:
6) ISBLANK: Returns true or false depending on whether the array passed as an argument is blank or not.
Input:
{
"Contacts": [ ]
}Formula: ISBLANK(Contacts)
Formula Result Path: Result
Output:
And for an array with elements, it will show the response as ‘false’.
7) ISNOTBLANK: Returns false or true depending on whether the array passed as an argument is blank or not.
This function is just the opposite of the ‘ISBLANK’ function, as evident from the function name.
Formula: ISNOTBLANK(Contacts)
8) LIST: Converts the arguments passed to a list format.
Input:
{
"PreferredLocation1" : "Chennai",
"PreferredLocation2": "Bangalore"
}Formula: LIST(PreferredLocation1, PreferredLocation2)
Formula Result Path: PreferredLocations
Output:
NOTE: If we pass arrays as arguments, the LIST function will create a single list containing all the elements from both arrays.
Formula: LIST(NewProducts, OldProducts)
Formula Result Path: AllProducts
Input and Output:
9) SORTBY: Sorts a list of JSON objects based on the nodes passed as arguments. By default, the sort is ascending. For a descending sort, specify the optional [:DSC] parameter.
NOTE: This function is supported only for DataRaptor Transforms.
Input:
{
"Contacts": [
{
"FirstName": "Aaron",
"LastName": "Xavier"
},
{
"FirstName": "Mike",
"LastName": "Smith"
},
{
"FirstName": "Zellie",
"LastName": "Xavier"
}
]
}Formula for Ascending Sort Order:
SORTBY(LIST(Contacts), 'LastName', 'FirstName')
Formula Result Path: SortedContacts
Output:
Here, the elements in the ‘SortedContacts’ array are sorted based on the ‘LastName’ value first and then by the ‘FirstName’ value in an ascending order.
Firstly, after sorting based on the ‘LastName’ values, we get the values in the order: Smith, Xavier, Xavier.
Here, there are two contacts with the same ‘LastName’ value: ‘Xavier’.
Now the second sorting is done based on the ‘FirstName’ values for the contacts with LastName as ‘Xavier’.
So the available ‘FirstName’ values corresponding to the contacts with LastName as ‘Xavier’ are sorted in an ascending order as: ‘Aaron’ and ‘Zellie’.
This is how the ‘SORTBY’ function sorts the list elements.
Formula for Descending Sort Order:
SORTBY(LIST(Contacts), 'LastName', 'FirstName', '[:DSC]')
Formula Result Path: SortedContacts
Output:
Now, the elements in the ‘SortedContacts’ array are sorted based on the ‘LastName’ value first and then by the ‘FirstName’ value in a descending order.
10) FILTER: Filters a list of JSON objects and returns the subset that matches the specified conditions.
Input:
{
"Contacts": [
{
"FirstName": "Aaron",
"LastName": "Xavier"
},
{
"FirstName": "Mike",
"LastName": "Smith"
},
{
"FirstName": "Zellie",
"LastName": "Xavier"
}
]
}Formula: FILTER(LIST(Contacts), 'LastName == "Xavier"')
Formula Result Path: FilteredContacts
Output:
Here, the ‘LastName’ values present in the ‘Contacts’ array are: ‘Smith’ and ‘Xavier’.
The ‘FILTER’ function filters the contacts with ‘LastName’ as ‘Xavier’ and displays the two contacts matching the criteria.
If we need to pass dynamic values as the matching filter criteria, use the below formula.
Formula with dynamic filter criteria:
FILTER(LIST(Names), 'LastName == "' + GetName + '"')
Here, the ‘GetName’ node holds the dynamic ‘LastName’ value.
Please read this blog for the usage of the functions: 'LISTMERGE' and 'LISTMERGEPRIMARY' in detail.
References: