Flattening: Data transformation using JSONata
Objective
Some SaaS services allow you to create custom fields. In this case, these are information fields created to track help calls - also known as tickets. Usually, these fields have title or description fields, but the APIs of these services generally use numerical identifiers to refer to them.
Example: A ticket in Zendesk with custom fields contains a property called custom_fields containing a list of IDs and their values.
Each custom filed has its own name, but API communication only uses the ID to refer to the fields. To work with analytical data we will "translate" the IDs into their real names using the table below, which was built just for this purpose:
The goal is to transform the original Zendesk information into an object containing the property names, as in the example below:
Complete Ticket Register
Below is a list of the original JSON content we will be using. Note that there are two tickets in a list in the tickets property and the mapping is in the map property.
And at the end of the transformation we want to get the following result:
Solution
To resolve the situation, follow these steps:
Step 1: Create a function that allows you to find a custom field name based only on its ID.
In other words, by providing an ID, such as 360035700992, the function searches for and identifies the corresponding name within a mapping list.
To do this, we use JSONata's $lookup function, for example:
"ZIP CODE"
Note that the $lookup function takes as parameters a list and a string containing the name of the property to be searched for in the list. The function returns the value of the property found.
Step 2: Map each entry in a custom field to a property whose value is the same as the value in the custom field.
We can perform this operation using the code below:
Note that the $map function received the custom_fields list as a parameter. In addition, we specify a function by calling function($v, $i, $a){}. The variables specified in this call are respectively assigned the following values:
$v : element of the array being processed
$i : index of the element in the array
$a : the entire array
We use the above variables inside the function to reference what we need to process.
This function returns an object for each element in the list that is processed. The content of this object is given by:
In the first interaction of this $map we will have:
$v
$i
0
$a
So the returned object is:
Step 3: Merge objects from a list into a single object
Note that the $map response in the previous step returns a list which each object is a different property. We now need to merge these objects from a list into a single object. To do this, we'll use the $merge function.
In our example, simply pass the result of the previous operation as a parameter to the $merge function. This function concatenates all the properties of all the objects in the list into a single object.
Step 4: $flat Function
We know that this function from step 03 will have to be executed for each of the tickets and therefore we will have to invoke it many times. For this reason, we have created a function that accommodates this code and only waits for the ticket's custom_fields property to be executed.
Note that we have created the $flat function defined as:
The function code is defined inside the braces and is exactly the same as the function defined in the previous step. The only difference is that we have parameterized the list of custom_fields in the form of the $fld variable.
Another change observed is that we need to surround the code with parentheses that encompass all the functional specifications. This allows us to define variables and functions that end with a semicolon (;).
Within this programmatic block we need to invoke the function so that JSONata actually executes the code. This is exactly what we do with the following line:
We call the function by passing the list defined in custom_field in the $fld variable. This gives us the object we want.
Finally, you'll notice that we've used comment lines to document the code. These lines have delimiters in the form of /* text */.
Step 5: Consolidating the mapped fields in the original ticket object with the $merge function
In step 4, we generated an object containing the custom_fields already mapped. We now need to insert all the properties of this new object into the original ticket object. The best way to do this is to use the $merge function. This function receives an array of objects and consolidates all the properties of the individual objects into a single object.
However, we need to build an array that contains two objects, one with the original ticket properties and one with the new properties. We can do this with the following line of code:
The $append function in this case appends two objects to a list, the first object being the ticket and the second object being the result of our $flat operation which also returns an object. In this case, the $append function returns a list containing the two objects mentioned.
All that remains is to execute the $merge function on the generated list. JSONata allows us to use an alternative syntax in which we specify a function that receives the result of the previously specified code as an argument. See the example below:
In the example above, the $merge function receives the result of the $append function as an execution parameter. Below is the complete example for this step:
Note that the response contains all the original properties of the ticket, plus the properties generated in the mapping. Later on, we'll present a way to remove the custom_fields field that became unnecessary after the mapping.
Step 6: Processing multiple tickets using $map and the $all function
Up to step 5 we considered handling a single ticket, but we received multiple tickets in a single array and we need to process each ticket individually. This is exactly the purpose of the $map function, which will be used once again to process each ticket individually.
We'll do this by creating a new function called $all whose purpose is to process all the tickets, as defined below:
Basically, this function performs step 5 for each of the tickets received in the tickets array.
There is just one problem with the above approach, which only manifests itself when we receive an empty array of tickets. As it stands, the result would just be an empty object. To ensure that this execution always returns an array, even an empty one, we execute an empty $append in the function sequence. This $append is innocuous when the operation results in an array of tickets, but it will be useful when the function returns empty, as it will transform the empty object into an empty array.
With this, our code is:
Step 7: Removing unwanted properties with Transform
Note that the result in step 06 still requires some properties that are no longer wanted, such as custom_fields. Let's remove this property as well as sharing_agreements_ids to illustrate how to remove unwanted properties.
Note that we invoke the $all function and then submit its result to the Transform operator, which takes three arguments. The first two $ refer to the root object and the next list contains the properties that are removed.
This brings us to our final result:
Last updated