> For the complete documentation index, see [llms.txt](https://docs.coherent.global/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.coherent.global/spark-apis/transforms-api/jsontransforms.md). # JSONtransforms {% hint style="info" %} This feature is only enabled on certain environments and tenants. [Contact support](/support/contact-support.md) for more information about this feature. {% endhint %} With this transform type, you can convert JSON content to requests and responses that are compatible with the Spark [Execute API](/spark-apis/execute-api.md). [JSONata](https://jsonata.org) is a "lightweight query and transformation language for JSON data". You can test your JSONata transforms in the [JSONata Exerciser](https://try.jsonata.org/). We recommend this transform type if you have a complex JSON transformation that you would like to perform on a single request and response. ```mermaid flowchart LR Request[/System request/] Transform["Call the Spark
/transform endpoint"] Doc["Fetch transform document"] Response[/Spark response/] Request --> Transform --> Doc subgraph Runtime["JSONata runtime"] direction LR Input["Apply input transform
to the system request"] Execute["Call /execute
endpoint"] Output["Apply output transform
to the Spark response"] Input --> Execute --> Output end Doc --> Input Output --> Response ``` ## Transform document Transform documents are used to define the code relating to a transform. For `JSONtransforms`, the transform document is a JSON document defined by the following objects: | Key | Value | | ----------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `transform_type` | `JSONtransforms_v1.0.0` or `JSONtransforms_v1.0.1`. Note the JSONata package versions used in [Update roadmap](/spark-apis/transforms-api/update-roadmap.md). | | `target_api_version` |

Optional parameter.
This can be either v3 or v4 depending on whether to target the /pages/nsJig8FRhYDL3hcSAwbt or /pages/F63N1Fpz7fYruDnmWkEQ.
If omitted, then the default is v3.

| | `input_body_transform` |

Optional parameter.
Single string containing the code for the input transform. Any double quotation marks " need to be escaped, where " is replaced with ". This will be processed against the request body to the /transforms endpoint.
If omitted, no transformation will take place.
The result will automatically be put under the /pages/i1Gw4hnm5WLCg1qp8zNM inputs object.

| | `output_body_transform` |

Optional parameter.
Single string containing the code for the output transform. Any double quotation marks " need to be escaped, where " is replaced with ".
If omitted, no transformation will take place.
This will be processed against the response body of the /pages/i1Gw4hnm5WLCg1qp8zNM.

| ### Create the transform document The transform document for `JSONtransforms` can be constructed through the Spark UX through the [Folder overview](/navigation/folder-overview.md). The document can also be manually created and uploaded to Spark using the **New transforms** feature. 1. The `transform_type` is `JSONata_v1.0.1`. 2. Since we are using [Execute API (v3)](/spark-apis/execute-api/execute-api-v3.md), the `target_api_version` is `v3`. 3. Both the `input_body_transform` and `output_body_transform` must be a single string containing the code for the transform. 1. Remove any lines containing comments. 2. Any double quotation marks `"` need to be escaped, where `"` is replaced with `\"`. * Note that JSONata allows for single quotation marks `'` to be interchanged with double quotation marks `"`. If `'` are used exclusively in lieu of `"`, then the escape character is not needed. 3. If you are using [Visual Studio Code](https://code.visualstudio.com/), you can use the **Join lines** feature to make it easy to combine multiple lines into a single string. 1. Select the transform text that you would like to combine onto a single line. 1. Access the **Command Palette** with the keyboard shortcut `F1` or `Ctrl+Shift+P` on Windows or `Shift+⌘+P` on Mac. 2. Type in the command **Join lines** and select it. 4. As an alternative to **Join lines**, line breaks `Ctrl+Enter` can be replaced with the new line character `\n`. 4. Save the document as a `JSON` file. The filename must end with `_transform.json`. 5. Follow the instructions in [Transforms](/navigation/folder-overview/transforms.md#new-transform) to upload the transform document to Spark. ### Sample transform document
```json { "transform_type": "JSONtransforms_v1.0.0", "target_api_version": "v3", "input_body_transform":"{\"add_1\":add.a,\"add_2\":add.b,\"multiply_1\":multiply.a,\"multiply_2\":multiply.b}", "output_body_transform":"{\"add\":{\"result\":response_data.outputs.add},\"multiply\":{\"result\":response_data.outputs.multiply},\"meta\":{\"id\":response_meta.call_id}}" } ```
## Transforms API {% code overflow="wrap" %} ```shellscript POST /{tenant}/api/v4/transforms/{transform_folder}/{transform}/for/{folder}/{service} ``` {% endcode %} ### Authorization * `Bearer {token}` accessible from [Authorization - Bearer token](/spark-apis/authorization-bearer-token.md) or systematically via [Client credentials](/identity-and-access-management/client-credentials.md). * The request headers should include a key for `Authorization` with the value `Bearer {token}`. * API key created from [Authorization - API keys](/spark-apis/authorization-api-keys.md). * The request headers should include the keys `x-synthetic-key` and `x-tenant-name` with the values of the API key and tenant name respectively. Note that the authorization must include permissions that can access: * The requested Spark service. * The requested transform. * Either the folder that the transform is stored OR * If the transform is in the `apps/transforms` folder. This would typically require `user:pf` or `supervisor:pf`. ### Path parameters | Key | Value | | --------------------- | ------------------------------------------------------------------------------------------------------------------------- | | `tenant` \* | Tenant is part of your sign-in URL and also available in the [Navigation menu](/navigation/navigation-menu.md#user-menu). | | `transform_folder` \* | Folder associated with the transform document of the request. | | `transform` \* | Transform document associated with the transform document of the request (without `_transform.json`). | | `folder` \* | Folder associated with the Spark service of the request. | | `service` \* | Service associated with the Spark service of the request. | ### Request headers Meta parameters are included in the request headers to keep the request body as generic as possible to conform to the calling system format.
KeyValue
Content-Typeapplication/json
x-metaOptional parameter.
If using target_api_version v4, this should contain a single string containing a JSON object with key value pairs of the Execute API (v4) Execute API (v4) parameters. Any double quotation marks " need to be escaped and " is replaced with \".
Example:
"{\"version_id\":\"86da8bea-b900-4bf2-8bcf-6a365ac91a4d\"}"
x-request-metaOptional parameter.
If using target_api_version v3, this should contain a single string containing a JSON object with key value pairs of the Execute API (v3) Execute API (v3) parameters. Any double quotation marks " need to be escaped and " is replaced with \".
Example:
"{\"call_purpose\":\"Transform mainframe request\"}"
x-transforms-pathFolder name if the transform document is stored in a folder and the URI pattern does not include transform_folder.
### Request body `Content-Type: application/json` ### HTTP status code By default, the status code for a successful transformation will be `200`. You can also ask the transform to return a custom status code that can be consumed by your application. If the transformation was successful and the completed output transform includes the key `spark_transform_response_httpstatuscode` then Spark will use the value of this key as the returned status code. For example: 
{
  ...
  "spark_transform_response_httpstatuscode": 422
  ...
}
This would result in the HTTP status code returned as 422 Unprocessable Entity
### Response headers
KeyValue
Content-Typeapplication/json
x-metaOptional parameter.
If using target_api_version v4, this should contain a single string containing a JSON object with key value pairs of the Execute API (v4) Execute API (v4) parameters. Any double quotation marks " need to be escaped and " is replaced with \".
Example:
"{\"version_id\":\"86da8bea-b900-4bf2-8bcf-6a365ac91a4d\"}"
x-request-metaOptional parameter.
If using target_api_version v3, this should contain a single string containing a JSON object with key value pairs of the Execute API (v3) Execute API (v3) parameters. Any double quotation marks " need to be escaped and " is replaced with \".
Example:
"{\"call_purpose\":\"Transform mainframe request\"}"
### Response body `Content-Type: application/json` If the Spark [Execute API](/spark-apis/execute-api.md) encounters an error, the error from the [Execute API](/spark-apis/execute-api.md) will be returned. ### Limitations * The API call has a maximum lifetime of `270 s`. ## Example The calling system would like to perform addition and multiplication operations but it is in a different format to the Spark service. The Spark service has its inputs and outputs defined as: * This example uses [Execute API (v3)](/spark-apis/execute-api/execute-api-v3.md). * `Xoutput_add` = `Xinput_add_1+Xinput_add_2` * `Xoutput_multiply` = `Xinput_multiply_1*Xinput_multiply_2` * For the Spark [Execute API (v3)](/spark-apis/execute-api/execute-api-v3.md) call, we also want to note the `call_purpose`. * In the response, we would like to include the [Execute API (v3)](/spark-apis/execute-api/execute-api-v3.md#response_meta) `call_id`. {% file src="/files/3cLTZIZEwSI6W48k2YcY" %} The request is transformed to match the Spark [Execute API (v3)](/spark-apis/execute-api/execute-api-v3.md) format and the response is transformed to match the required system response.
RequestJSONata transformTransformed request
{ 
 "add": {
   "a":3,
   "b":4
  },
  "multiply": {
    "a":5,
    "b":6
  }
}

{
 "add_1": add.a,
 "add_2": add.b,
 "multiply_1": multiply.a,
 "multiply_2": multiply.b
}

{
  "add_1": 3,
  "add_2": 4,
  "multiply_1": 5,
  "multiply_2": 6
}
Spark responseJSONata transformTransformed response
{
  "response_data": {
    "outputs": {
      "add": 7,
      "multiply": 30
    },
    "warnings": null,
    "errors": null
  },
  "response_meta": {
    ...,
    "call_id": "3790cd02-b423-47f3-96f4-262bbf6c306e",
    ...
  }
}

{
 "add":{
  "result": response_data.outputs.add
  },
 "multiply":{
  "result": response_data.outputs.multiply
 },
 "meta":{
  "id": response_meta.call_id
 }
}

{
  "add": {
    "result": 7
  },
  "multiply": {
    "result": 30
  },
  "meta": {
    "id": "3790cd02-b423-47f3-96f4-262bbf6c306e"
  }
}
{% file src="/files/l7G26RbO0UNUFtslw40Q" %} {% tabs %} {% tab title="Sample request" %}
```sh curl --location 'https://excel.myenvironment.coherent.global/mytenant/api/v4/transforms/mytransformsfolder/mytransformsname/for/myservicefolder/myservicename' \ --header 'Accept-Encoding: gzip' \ --header 'Content-Type: application/json' \ --header 'x-request-meta: "{\"call_purpose\":\"Documentation example - arithmetic\"}"' \ --header 'Authorization: Bearer {token}' \ --data '{ "add": { "a": 3, "b": 4 }, "multiply": { "a": 5, "b": 6 } }' ```
{% endtab %} {% tab title="Sample response" %} `HTTP 200 OK` `Content-Type: application/json`
```json { "add": { "result": 7 }, "multiply": { "result": 30 }, "meta": { "id": "d15446c7-20be-4e5c-9062-0c723b2f3eda" } } ```
{% endtab %} {% endtabs %} ## Sample JSONata ### Convert JSON "records" table to array table {% tabs %} {% tab title="Sample request body" %}
```json { "table": [ { "a": 2, "b": 1 }, { "b": 3, "c": 4 }, { "c": 5, "d": 6 } ] } ```
{% endtab %} {% tab title="JSONata" %}
``` ( /* convert JSON record table to array table */ $table_to_array := function($input_table) {( $table_keys := $keys($input_table); /* create key value pair of all keys with a value of null */ $key_null := [$reduce($table_keys, function($acc, $v) { $merge([$acc, {$v: null}]) }, {})]; /* for each row merge key_null with the table record */ /* keys without values will be null */ /* convert the result to a csv of values */ $csv_row := function($input_row) {( $merged_null := $merge([$key_null, $input_row]); $csv := [$each($merged_null, function($v, $k) {$v})]; $csv )}; [[$table_keys], $map($input_table, $csv_row)]; )}; { "csv_table": $table_to_array(table) }; ) ```
{% endtab %} {% tab title="Sample response body" %}
```json { "csv_table": [ [ "a" , "b" , "c", "d" ], [ 2, 1, null, null ], [ null, 3, 4, null ], [ null, null, 5, 6 ] ] } ```
{% endtab %} {% endtabs %} ### Convert items with index suffices into an array of objects Select items with the index suffices `_#` identified in `number_of_plans`. {% tabs %} {% tab title="Sample request body" %}
```json { "response_data": { "outputs": { "number_of_plans": "2,3", "AnnualPremium_1": 46.1, "AnnualPremium_2": 46.2, "AnnualPremium_3": 46.3, "AnnualPremium_4": 46.4, "PaymentFreq_1": 1, "PaymentFreq_2": 2, "PaymentFreq_3": 3, "PaymentFreq_4": 4 } } } ```
{% endtab %} {% tab title="JSONata" %}
``` ( $data := $.response_data.outputs; $plan_output := function($index) {( { "PlanNumber": $index, "AnnualPremium": $lookup($data, "AnnualPremium_"&$index), "Payment": { "Freq": $lookup($data, "PaymentFreq_"&$index) } }; )}; $result := None; $contains($data.number_of_plans, '1') ? $result := $append($result, $plan_output(1)) : $result; $contains($data.number_of_plans, '2') ? $result := $append($result, $plan_output(2)) : $result; $contains($data.number_of_plans, '3') ? $result := $append($result, $plan_output(3)) : $result; $contains($data.number_of_plans, '4') ? $result := $append($result, $plan_output(4)) : $result; ) ```
{% endtab %} {% tab title="Sample response body" %}
```json [ { "PlanNumber": 2, "AnnualPremium": 46.2, "Payment": { "Freq": 2 } }, { "PlanNumber": 3, "AnnualPremium": 46.3, "Payment": { "Freq": 3 } } ] ```
{% endtab %} {% endtabs %} ## Developer tools For JavaScript developers, you can try the [Node Transform Document Builder](https://github.com/floherent/node-transform-builder) which facilitates the build and validation of transform documents. It includes support for JSONata.