JSONtransforms

This feature is only enabled on certain environments and tenants. Contact support for more information about this feature.

With this transform type, you can convert JSON content to requests and responses that are compatible with the Spark Execute API. JSONata is a "lightweight query and transformation language for JSON data". You can test your JSONata transforms in the JSONata Exerciser.

We recommend this transform type if you have a complex JSON transformation that you would like to perform on a single request and 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.
target_api_version	Optional parameter. This can be either v3 or v4 depending on whether to target the Execute API (v3) or Execute API (v4). 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 Execute API 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 Execute API.

Create the transform document

The transform document for JSONtransforms can be constructed through the Spark UX through the Folder overview.

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), 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, 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 New transform to upload the transform document to Spark.

Sample transform document

{
  "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

POST /{tenant}/api/v4/transforms/{transform_folder}/{transform}/for/{folder}/{service}

Authorization

• Bearer {token} accessible from Authorization - Bearer token or systematically via Client credentials.

  • The request headers should include a key for Authorization with the value Bearer {token}.

• API key created from Authorization - API keys.

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

Key	Value
Content-Type	application/json
x-meta	Optional 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) Request body parameters. Any double quotation marks " need to be escaped and " is replaced with \". Example: "{\"version_id\":\"86da8bea-b900-4bf2-8bcf-6a365ac91a4d\"}"
x-request-meta	Optional 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) request_meta parameters. Any double quotation marks " need to be escaped and " is replaced with \". Example: "{\"call_purpose\":\"Transform mainframe request\"}"
x-transforms-path	Folder 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

Key	Value
Content-Type	application/json
x-meta	Optional 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) Request body parameters. Any double quotation marks " need to be escaped and " is replaced with \". Example: "{\"version_id\":\"86da8bea-b900-4bf2-8bcf-6a365ac91a4d\"}"
x-request-meta	Optional 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) request_meta 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 encounters an error, the error from the Execute API 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).

• Xoutput_add = Xinput_add_1+Xinput_add_2

• Xoutput_multiply = Xinput_multiply_1*Xinput_multiply_2

• For the Spark Execute API (v3) call, we also want to note the call_purpose.

• In the response, we would like to include the response_meta call_id.

arithmetic.xlsx

9KB

Open

The request is transformed to match the Spark Execute API (v3) format and the response is transformed to match the required system response.

Request	JSONata transform	Transformed 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 response	JSONata transform	Transformed 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" } }

arithmetic_transform.json

320B

Open

Sample request

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
    }
}'

Sample response

HTTP 200 OK Content-Type: application/json

{
  "add": {
    "result": 7
  },
  "multiply": {
    "result": 30
  },
  "meta": {
    "id": "d15446c7-20be-4e5c-9062-0c723b2f3eda"
  }
}

Sample JSONata

Convert JSON "records" table to array table

Sample request body

{
  "table": [
    { "a": 2, "b": 1                 },
    {         "b": 3, "c": 4         },
    {                 "c": 5, "d": 6 }
  ]
}

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)
    };
)

Sample response body

{
  "csv_table": [
    [ "a" , "b" , "c",  "d"  ],
    [    2,    1, null, null ],
    [ null,    3,    4, null ],
    [ null, null,    5,    6 ]
  ]
}

Convert items with index suffices into an array of objects

Select items with the index suffices _# identified in number_of_plans.

Sample request body

{
  "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
    }
  }
}

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;    
)

Sample response body

[
  {
    "PlanNumber": 2,
    "AnnualPremium": 46.2,
    "Payment": { "Freq": 2 }
  },
  {
    "PlanNumber": 3,
    "AnnualPremium": 46.3,
    "Payment": { "Freq": 3 }
  }
]

Developer tools

For JavaScript developers, you can try the Node Transform Document Builder which facilitates the build and validation of transform documents. It includes support for JSONata.