# 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. ```json "custom_fields": [ { "id": 360032901812, "value": "0457000" }, { "id": 360032943991, "value": "Depto Suporte" }, { "id": 360030824791, "value": "XDY667" } ] ``` 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: ```json "map": { "360032901812": "ZIP CODE", "360032943991": "Contact Service", "360030824791": "Coupon" } ``` The goal is to transform the original Zendesk information into an object containing the property names, as in the example below: ```json "resp": { "ZIP CODE": "0457000", "Contact Service": "Support Dept.", "Coupon": "XDY667" } ``` ### **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. ```json { "tickets": [ { "url": "https://soft.zendesk.com/api/v2/tickets/612022.json", "id": 612022, "external_id": null, "created_at": "2023-10-18T08:12:42Z", "updated_at": "2023-10-18T08:212:42Z", "subject": "Non-functional import", "description": "Client complains that import crashes with his file", "custom_fields": [ { "id": 360032901812, "value": "0457000" }, { "id": 360032943991, "value": "Support Dept." }, { "id": 360030824791, "value": "XDY667" } ], "satisfaction_rating": null, "sharing_agreement_ids": [], "custom_status_id": 1900000838127 }, { "url": "https://soft.zendesk.com/api/v2/tickets/612011.json", "id": 612011, "external_id": null, "created_at": "2023-10-18T03:26:42Z", "updated_at": "2023-10-18T03:26:42Z", "subject": "Website access problem. New client", "description": "Customer complains that browser won't access site", "custom_fields": [ { "id": 360032901812, "value": "0381000" }, { "id": 360032943991, "value": "Partner" }, { "id": 360030824791, "value": "WQK890" } ], "satisfaction_rating": null, "sharing_agreement_ids": [], "custom_status_id": 1900000838249 } ], "map": { "360032901812": "ZIP CODE", "360032943991": "Contact Service", "360030824791": "Coupon" } } ``` And at the end of the transformation we want to get the following result: ```json { "tickets": [ { "url": "https://soft.zendesk.com/api/v2/tickets/612022.json", "id": 612022, "external_id": null, "created_at": "2023-10-18T08:12:42Z", "updated_at": "2023-10-18T08:212:42Z", "subject": "Non-functional import", "description": "Client complains that import crashes with his file", "satisfaction_rating": null, "custom_status_id": 1900000838127, "ZIP CODE": "0457000", "Contact Service": "Support Dept.", "Coupon": "XDY667" }, { "url": "https://soft.zendesk.com/api/v2/tickets/612011.json", "id": 612011, "external_id": null, "created_at": "2023-10-18T03:26:42Z", "updated_at": "2023-10-18T03:26:42Z", "subject": "Website access problem. New client", "description": "Customer complains that browser won't access site", "satisfaction_rating": null, "custom_status_id": 1900000838249, "ZIP CODE": "0381000", "Contact Service": "Partner", "Coupon": "WQK890" } ] } ``` #### 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:

Input	JSONata	Output
`{ "map": { "360032901812": "ZIP CODE", "360032943991": "Contact Service", "360030824791": "Coupon" } }`	`$lookup(map, "360032901812")`	"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.** To do this, we'll use JSONata's [**$map**](https://docs.jsonata.org/higher-order-functions#map) function. This function receives a list and allows you to define another function that will be executed for each of the elements in the list. It's equivalent to a FOR loop that will execute the function for each of the elements in the list. We can perform this operation using the code below:

Input JSONata Output

Input	JSONata	Output
`{ "custom_fields": [ { "id": 360032901812, "value": "0457000" }, { "id": 360032943991, "value": "Support Dept." }, { "id": 360030824791, "value": "XDY667" } ], "map": { "360032901812": "ZIP CODE", "360032943991": "Contact Service", "360030824791": "Coupon" } }`	$map(custom\_fields, function($v, $i, $a) { ``` {$lookup(map,$string($v.id)) : $v.value} }) ```	`\[ { "ZIP CODE": "0457000" }, { "Contact Service": "Support Dept." }, { "Coupon": "XDY667" } ]`

{  
  "custom_fields": [
    {
      "id": 360032901812,
      "value": "0457000"
    },
    {
      "id": 360032943991,
      "value": "Support Dept."
    },
    {
      "id": 360030824791,
      "value": "XDY667"
    }
  ],
  "map": {
    "360032901812": "ZIP CODE",
    "360032943991": "Contact Service",
    "360030824791": "Coupon"
  }
}

$map(custom\_fields, function($v, $i, $a) {

```
    {$lookup(map,$string($v.id)) : $v.value}

})
```

\[
{
"ZIP CODE": "0457000"
},
{
"Contact Service": "Support Dept."
},
{
"Coupon": "XDY667"
}
]

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: ``` {$lookup(map,$string($v.id)) : $v.value} ``` Note that the name of the property in the response is exactly the result of the **$lookup** function when we search the **map** object for the property whose name is the value of the **id** property of the element being processed (**$**[**v.id**](http://v.id/)). And the value of the response is exactly the value of the **value** property of the same element. In the first interaction of this **$map** we will have:

1	2
$v	`{ "id": 360032901812, "value": "0457000" },`
$i	0
$a	`[ { "id": 360032901812, "value": "0457000" }, { "id": 360032943991, "value": "Support Dept." }, { "id": 360030824791, "value": "XDY667" } ]`

So the returned object is: ``` {"ZIP CODE" : "0457000"} ``` *** **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.

Input JSONata Output

Input	JSONata	Output
`{ "custom_fields": [ { "id": 360032901812, "value": "0457000" }, { "id": 360032943991, "value": "Support Dept." }, { "id": 360030824791, "value": "XDY667" } ], "map": { "360032901812": "ZIP CODE", "360032943991": "Contact Service", "360030824791": "Coupon" } }`	$merge($map(custom\_fields, function($v, $i, $a) { ``` {$lookup(map,$string($v.id)) : $v.value} })) ```	`{ "ZIP CODE": "0457000", "Contact Service": "Support Dept.", "Coupon": "XDY667" }`

{  
  "custom_fields": [
    {
      "id": 360032901812,
      "value": "0457000"
    },
    {
      "id": 360032943991,
      "value": "Support Dept."
    },
    {
      "id": 360030824791,
      "value": "XDY667"
    }
  ],
  "map": {
    "360032901812": "ZIP CODE",
    "360032943991": "Contact Service",
    "360030824791": "Coupon"
  }
}

$merge($map(custom\_fields, function($v, $i, $a) {

```
    {$lookup(map,$string($v.id)) : $v.value}

}))
```

{
"ZIP CODE": "0457000",
"Contact Service": "Support Dept.",
"Coupon": "XDY667"
}

*** **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.

Input JSONata Output

Input	JSONata	Output
`{ "custom_fields": [ { "id": 360032901812, "value": "0457000" }, { "id": 360032943991, "value": "Support Dept." }, { "id": 360030824791, "value": "XDY667" } ], "map": { "360032901812": "ZIP CODE", "360032943991": "Contact Service", "360030824791": "Coupon" } }`	( /\* $flat is a function that receives a custom\_fieds in the variable $fld \/ $flat := function($fld) { $merge($map($fld, function($v, $i, $a) { ``` {$lookup(map,$string($v.id)) : $v.value} ``` }))}; /\ Example function call \*/ {"resp" : $flat(custom\_fields)}; )	`{ "resp": { "ZIP CODE": "0457000", "Contact Service": "Support Dept.", "Coupon": "XDY667" } }`

{ 
 "custom_fields": [
   {
     "id": 360032901812,
     "value": "0457000"
   },
   {
     "id": 360032943991,
     "value": "Support Dept."
   },
   {
     "id": 360030824791,
     "value": "XDY667"
   }
 ],
 "map": {
   "360032901812": "ZIP CODE",
   "360032943991": "Contact Service",
   "360030824791": "Coupon"
 }
}

(

/\* $flat is a function that receives a custom\_fieds in the variable $fld \*/

$flat := function($fld) {

$merge($map($fld, function($v, $i, $a) {

```
   {$lookup(map,$string($v.id)) : $v.value}
```

}))};

/\* Example function call \*/

{"resp" : $flat(custom\_fields)};

)

{
"resp": {
"ZIP CODE": "0457000",
"Contact Service": "Support Dept.",
"Coupon": "XDY667"
}
}

Note that we have created the **$flat** function defined as: ``` $flat := function($fld) { }; ``` 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: ``` {"resp" : $flat(custom_fields)}; ``` 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: ``` $append(ticket, $flat(ticket.custom_fields)) ``` 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: ``` $append(ticket, $flat(ticket.custom_fields)) ~> merge() ``` 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:

Input JSONata Output

Input	JSONata	Output
`{ "tickets": [ { "description": "Client complains that import crashes with his file", "custom_fields": [ { "id": 360032901812, "value": "0457000" }, { "id": 360032943991, "value": "Support Dept." }, { "id": 360030824791, "value": "XDY667" } ] } ], "map": { "360032901812": "ZIP CODE", "360032943991": "Contact Service", "360030824791": "Coupon" } }`	( /\* Function that maps the customs\_fields and returns an object with the properties \/ $flat := function($fld) ``` {$merge($map($fld, function($v, $i, $a) { {$lookup(map,$string($v.id)) : $v.value} }))}; ``` { ``` / test code */ "register":$append(tickets[0], $flat(tickets[0].custom_fields)) ~> $merge() ``` } )	`{ "register": { "description": "Client complains that import crashes with his file", "custom\_fields": \[ { "id": 360032901812, "value": "0457000" }, { "id": 360032943991, "value": "Support Dept." }, { "id": 360030824791, "value": "XDY667" } ], "ZIP CODE": "0457000", "Contact Service": "Support Dept.", "Coupon": "XDY667" } }`

{
 "tickets": [
   {
     "description": "Client complains that import crashes with his file",
     "custom_fields": [
       {
         "id": 360032901812,
         "value": "0457000"
       },
       {
         "id": 360032943991,
         "value": "Support Dept."
       },
       {
         "id": 360030824791,
         "value": "XDY667"
       }
     ]
   }
 ],
 "map": {
   "360032901812": "ZIP CODE",
   "360032943991": "Contact Service",
   "360030824791": "Coupon"
 }
}

(

/\* Function that maps the customs\_fields and returns an object with the properties \*/

$flat := function($fld)

```
   {$merge($map($fld, function($v, $i, $a) {

       {$lookup(map,$string($v.id)) : $v.value}

   }))};
```

{

```
   /* test code */

   "register":$append(tickets[0], $flat(tickets[0].custom_fields)) ~> $merge()
```

}

)

{
"register": {
"description": "Client complains that import crashes with his file",
"custom\_fields": \[
{
"id": 360032901812,
"value": "0457000"
},
{
"id": 360032943991,
"value": "Support Dept."
},
{
"id": 360030824791,
"value": "XDY667"
}
],
"ZIP CODE": "0457000",
"Contact Service": "Support Dept.",
"Coupon": "XDY667"
}
}

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: ``` $all := $map(tickets, function($r, $s, $t) { $append($r, $flat($r.custom_fields)) ~> $merge() }) ; ``` 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. ``` $all := $map(tickets, function($r, $s, $t) { $append($r, $flat($r.custom_fields)) ~> $merge() }) ~> $append([]); ``` With this, our code is:

Input JSONata Output

Input	JSONata	Output
{ "tickets": [ { "url": "https://soft.zendesk.com/api/v2/tickets/612022.json", "id": 612022, "external_id": null, "created_at": "2023-10-18T08:12:42Z", "updated_at": "2023-10-18T08:212:42Z", "subject": "Non-functional import", "description": "Client complains that import crashes with his file", "custom_fields": [ { "id": 360032901812, "value": "0457000" }, { "id": 360032943991, "value": "Support Dept." }, { "id": 360030824791, "value": "XDY667" } ], "satisfaction_rating": null, "sharing_agreement_ids": [], "custom_status_id": 1900000838127 }, { "url": "https://soft.zendesk.com/api/v2/tickets/612011.json", "id": 612011, "external_id": null, "created_at": "2023-10-18T03:26:42Z", "updated_at": "2023-10-18T03:26:42Z", "subject": "Website access problem. New client", "description": "Customer complains that browser won't access site", "custom_fields": [ { "id": 360032901812, "value": "0381000" }, { "id": 360032943991, "value": "Partner" }, { "id": 360030824791, "value": "WQK890" } ], "satisfaction_rating": null, "sharing_agreement_ids": [], "custom_status_id": 1900000838249 } ], "map": { "360032901812": "ZIP CODE", "360032943991": "Contact Service", "360030824791": "Coupon" } }	( /\* Function that maps the customs\_fields and returns an object with the properties \/ $flat := function($fld) ``` {$merge($map($fld, function($v, $i, $a) { {$lookup(map,$string($v.id)) : $v.value} }))}; ``` /\ Function that performs the mapping for each ticket \/ $all := $map(tickets, function($r, $s, $t) { ``` $append($r, $flat($r.custom_fields)) ~> $merge() ``` }) \~> $append(\[]); { ``` / executes function */ "registers":$all ``` } )	{ "registers": \[ { "url": "", "id": 612022, "external\_id": null, "created\_at": "2023-10-18T08:12:42Z", "updated\_at": "2023-10-18T08:212:42Z", "subject": "Non-functional import", "description": "Client complains that import crashes with his file", "custom\_fields": \[ { "id": 360032901812, "value": "0457000" }, { "id": 360032943991, "value": "Support Dept." }, { "id": 360030824791, "value": "XDY667" } ], "satisfaction\_rating": null, "sharing\_agreement\_ids": \[], "custom\_status\_id": 1900000838127, "ZIP CODE": "0457000", "Contact Service": "Support Dept.", "Coupon": "XDY667" }, { "url": "", "id": 612011, "external\_id": null, "created\_at": "2023-10-18T03:26:42Z", "updated\_at": "2023-10-18T03:26:42Z", "subject": "Website access problem. New client", "description": "Customer complains that browser won't access site", "custom\_fields": \[ { "id": 360032901812, "value": "0381000" }, { "id": 360032943991, "value": "Partner" }, { "id": 360030824791, "value": "WQK890" } ], "satisfaction\_rating": null, "sharing\_agreement\_ids": \[], "custom\_status\_id": 1900000838249, "ZIP CODE": "0381000", "Contact Service": "Partner", "Coupon": "WQK890" } ] }

{
 "tickets": [
   {
     "url": "https://soft.zendesk.com/api/v2/tickets/612022.json",
     "id": 612022,
     "external_id": null,
     "created_at": "2023-10-18T08:12:42Z",
     "updated_at": "2023-10-18T08:212:42Z",
     "subject": "Non-functional import",
     "description": "Client complains that import crashes with his file",
     "custom_fields": [
       {
         "id": 360032901812,
         "value": "0457000"
       },
       {
         "id": 360032943991,
         "value": "Support Dept."
       },
       {
         "id": 360030824791,
         "value": "XDY667"
       }
     ],
     "satisfaction_rating": null,
     "sharing_agreement_ids": [],
     "custom_status_id": 1900000838127
   },
   {
     "url": "https://soft.zendesk.com/api/v2/tickets/612011.json",
     "id": 612011,
     "external_id": null,
     "created_at": "2023-10-18T03:26:42Z",
     "updated_at": "2023-10-18T03:26:42Z",
     "subject": "Website access problem. New client",
     "description": "Customer complains that browser won't access site",
     "custom_fields": [
       {
         "id": 360032901812,
         "value": "0381000"
       },
       {
         "id": 360032943991,
         "value": "Partner"
       },
       {
         "id": 360030824791,
         "value": "WQK890"
       }
     ],
     "satisfaction_rating": null,
     "sharing_agreement_ids": [],
     "custom_status_id": 1900000838249
   }
 ],
 "map": {
   "360032901812": "ZIP CODE",
   "360032943991": "Contact Service",
   "360030824791": "Coupon"
 }
}

(

/\* Function that maps the customs\_fields and returns an object with the properties \*/

$flat := function($fld)

```
   {$merge($map($fld, function($v, $i, $a) {

       {$lookup(map,$string($v.id)) : $v.value}

   }))};
```

/\* Function that performs the mapping for each ticket \*/

$all := $map(tickets, function($r, $s, $t) {

```
   $append($r, $flat($r.custom_fields)) ~> $merge()
```

}) \~> $append(\[]);

{

```
   /* executes function */

   "registers":$all
```

}

)

{
"registers": \[
{
"url": "",
"id": 612022,
"external\_id": null,
"created\_at": "2023-10-18T08:12:42Z",
"updated\_at": "2023-10-18T08:212:42Z",
"subject": "Non-functional import",
"description": "Client complains that import crashes with his file",
"custom\_fields": \[
{
"id": 360032901812,
"value": "0457000"
},
{
"id": 360032943991,
"value": "Support Dept."
},
{
"id": 360030824791,
"value": "XDY667"
}
],
"satisfaction\_rating": null,
"sharing\_agreement\_ids": \[],
"custom\_status\_id": 1900000838127,
"ZIP CODE": "0457000",
"Contact Service": "Support Dept.",
"Coupon": "XDY667"
},
{
"url": "",
"id": 612011,
"external\_id": null,
"created\_at": "2023-10-18T03:26:42Z",
"updated\_at": "2023-10-18T03:26:42Z",
"subject": "Website access problem. New client",
"description": "Customer complains that browser won't access site",
"custom\_fields": \[
{
"id": 360032901812,
"value": "0381000"
},
{
"id": 360032943991,
"value": "Partner"
},
{
"id": 360030824791,
"value": "WQK890"
}
],
"satisfaction\_rating": null,
"sharing\_agreement\_ids": \[],
"custom\_status\_id": 1900000838249,
"ZIP CODE": "0381000",
"Contact Service": "Partner",
"Coupon": "WQK890"
}
]
}

*** **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. We use the [**Transform**](https://docs.jsonata.org/other-operators#-------transform) operator to perform this type of removal. The operation below removes the properties: ``` $all ~> |$ |$, ["custom_fields", "sharing_agreement_ids"]| ``` 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:

Input JSONata Output

Input	JSONata	Output
{ "tickets": [ { "url": "https://soft.zendesk.com/api/v2/tickets/612022.json", "id": 612022, "external_id": null, "created_at": "2023-10-18T08:12:42Z", "updated_at": "2023-10-18T08:212:42Z", "subject": "Non-functional import", "description": "Client complains that import crashes with his file", "custom_fields": [ { "id": 360032901812, "value": "0457000" }, { "id": 360032943991, "value": "Support Dept." }, { "id": 360030824791, "value": "XDY667" } ], "satisfaction_rating": null, "sharing_agreement_ids": [], "custom_status_id": 1900000838127 }, { "url": "https://soft.zendesk.com/api/v2/tickets/612011.json", "id": 612011, "external_id": null, "created_at": "2023-10-18T03:26:42Z", "updated_at": "2023-10-18T03:26:42Z", "subject": "Website access problem. New client", "description": "Customer complains that browser won't access site", "custom_fields": [ { "id": 360032901812, "value": "0381000" }, { "id": 360032943991, "value": "Partner" }, { "id": 360030824791, "value": "WQK890" } ], "satisfaction_rating": null, "sharing_agreement_ids": [], "custom_status_id": 1900000838249 } ], "map": { "360032901812": "ZIP CODE", "360032943991": "Contact Service", "360030824791": "Coupon" } }	( /\* Function that maps the customs\_fields and returns an object with the properties \/ $flat := function($fld) ``` {$merge($map($fld, function($v, $i, $a) { {$lookup(map,$string($v.id)) : $v.value} }))}; ``` /\ Function that performs the mapping for each ticket \/ $all := $map(tickets, function($r, $s, $t) { ``` $append($r, $flat($r.custom_fields)) ~> $merge() ``` }) \~> $append(\[]); { ``` / executes and removes unwanted properties */ "tickets":$all ~> \|$ \|$, ["custom_fields", "sharing_agreement_ids"]\| ``` } )	{ "tickets": \[ { "url": "", "id": 612022, "external\_id": null, "created\_at": "2023-10-18T08:12:42Z", "updated\_at": "2023-10-18T08:212:42Z", "subject": "Non-functional import", "description": "Client complains that import crashes with his file", "satisfaction\_rating": null, "custom\_status\_id": 1900000838127, "ZIP CODE": "0457000", "Contact Service": "Support Dept.", "Coupon": "XDY667" }, { "url": "", "id": 612011, "external\_id": null, "created\_at": "2023-10-18T03:26:42Z", "updated\_at": "2023-10-18T03:26:42Z", "subject": "Website access problem. New client", "description": "Customer complains that browser won't access site", "satisfaction\_rating": null, "custom\_status\_id": 1900000838249, "ZIP CODE": "0381000", "Contact Service": "Partner", "Coupon": "WQK890" } ] }

{
 "tickets": [
   {
     "url": "https://soft.zendesk.com/api/v2/tickets/612022.json",
     "id": 612022,
     "external_id": null,
     "created_at": "2023-10-18T08:12:42Z",
     "updated_at": "2023-10-18T08:212:42Z",
     "subject": "Non-functional import",
     "description": "Client complains that import crashes with his file",
     "custom_fields": [
       {
         "id": 360032901812,
         "value": "0457000"
       },
       {
         "id": 360032943991,
         "value": "Support Dept."
       },
       {
         "id": 360030824791,
         "value": "XDY667"
       }
     ],
     "satisfaction_rating": null,
     "sharing_agreement_ids": [],
     "custom_status_id": 1900000838127
   },
   {
     "url": "https://soft.zendesk.com/api/v2/tickets/612011.json",
     "id": 612011,
     "external_id": null,
     "created_at": "2023-10-18T03:26:42Z",
     "updated_at": "2023-10-18T03:26:42Z",
     "subject": "Website access problem. New client",
     "description": "Customer complains that browser won't access site",
     "custom_fields": [
       {
         "id": 360032901812,
         "value": "0381000"
       },
       {
         "id": 360032943991,
         "value": "Partner"
       },
       {
         "id": 360030824791,
         "value": "WQK890"
       }
     ],
     "satisfaction_rating": null,
     "sharing_agreement_ids": [],
     "custom_status_id": 1900000838249
   }
 ],
 "map": {
   "360032901812": "ZIP CODE",
   "360032943991": "Contact Service",
   "360030824791": "Coupon"
 }
}

(

/\* Function that maps the customs\_fields and returns an object with the properties \*/

$flat := function($fld)

```
   {$merge($map($fld, function($v, $i, $a) {

       {$lookup(map,$string($v.id)) : $v.value}

   }))};
```

/\* Function that performs the mapping for each ticket \*/

$all := $map(tickets, function($r, $s, $t) {

```
   $append($r, $flat($r.custom_fields)) ~> $merge()
```

}) \~> $append(\[]);

{

```
   /* executes and removes unwanted properties */

   "tickets":$all ~> |$ |$, ["custom_fields", "sharing_agreement_ids"]|
```

}

)

{
"tickets": \[
{
"url": "",
"id": 612022,
"external\_id": null,
"created\_at": "2023-10-18T08:12:42Z",
"updated\_at": "2023-10-18T08:212:42Z",
"subject": "Non-functional import",
"description": "Client complains that import crashes with his file",
"satisfaction\_rating": null,
"custom\_status\_id": 1900000838127,
"ZIP CODE": "0457000",
"Contact Service": "Support Dept.",
"Coupon": "XDY667"
},
{
"url": "",
"id": 612011,
"external\_id": null,
"created\_at": "2023-10-18T03:26:42Z",
"updated\_at": "2023-10-18T03:26:42Z",
"subject": "Website access problem. New client",
"description": "Customer complains that browser won't access site",
"satisfaction\_rating": null,
"custom\_status\_id": 1900000838249,
"ZIP CODE": "0381000",
"Contact Service": "Partner",
"Coupon": "WQK890"
}
]
}

--- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.skyone.cloud/english/skyone-studio/how-to/flattening-data-transformation-using-jsonata.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.