JSON Transformation

JSON Transformation is a step that facilitates the manipulation and transformation of JSON data between different systems and applications. It’s an essential feature to ensure data consistency and compatibility across various APIs and data sources.

To transform the JSON, you will use the JSONata language. To assist you, the JSONata Exerciser can be accessed directly from the step form. In it, you can:

Define the structure of the input data.
Create transformation expressions that generate the output data in the desired format.
Check for errors in the expression.

To understand this language in detail, refer to the official JSONata documentation.

Next, learn how to add JSON Transformation to the canvas and configure its form.

Add JSON Transformation to the Canvas

Click on the icon on the left editing menu.
Select JSON Transformation to add it to the canvas. You can use the search bar to find it.

You can add it as many times as you need. In this case, each time you add this tool, a number is added next to its name (JSON Transformation 1, JSON Transformation 2, JSON Transformation 3).
Connect JSON Transformation to the flow steps. This tool can connect to a previous step and a following step.

To copy the step, click the

button. If the step form is already configured, the settings will be copied too.

To remove JSON Transformation from the canvas, select it and click the trash can icon .

Configure the Form

Select JSON Transformation on the canvas.
Click the editing icon.
Fill in the following form fields:
- Name: enter a unique name for the step. By default, you will see it as "JSON Transformation".
- Input Data: select the step and its payload of origin to apply the data transformation. You will see the connector’s request and response data and the request data from the trigger.
Click the GO TO JSONATA button . You will be redirected to the JSONata Exerciser.
Ask the application provider for the data model for your input mapping.
Enter the data model on the left side of the screen.
Build your expression in the top right-hand corner of the screen using the JSONata language.
Use version 2.0.x. You can find it in the top right-hand corner of the screen.

See an example of how to create an expression in a flow that integrates E-commerce and CRM.

If you have any doubts, you can refer to JSONata documentation.
Copy the finished expression.
Return to the JSON Transformation screen on Sensedia Integrations.
- In the JSONata field, paste the expression obtained in step 7 and copied in step 9 to map the data for the transformation process.
  
  If the expression has any errors, you will see an error message in red in the bottom right corner of this field.
Click SAVE.

Setting up an expression in JSONata Exerciser

jsonata en

To use the response in connectors, see the documentation for Data Mapping with Full Body.

Example

See an example of how to use the JSONata Exerciser to transform a JSON:

Integrating E-commerce and CRM

Let’s consider a flow that integrates an E-commerce platform with a CRM.

In this scenario, when a new order is placed on the E-commerce platform, the order details need to be transformed from the JSON format of the E-commerce platform to a format compatible with the CRM system.

Thus, we have the initial data from the E-commerce platform:

Source data (E-commerce)

{

  "orderId": "123456",

  "customerName": "John Doe",

  "email": "john@example.com",

  "totalAmount": 100.50,

  "items": [

    {

      "productId": "ABC123",

      "productName": "Widget",

      "quantity": 2,

      "price": 25.25

    },

    {

      "productId": "DEF456",

      "productName": "Gadget",

      "quantity": 1,

      "price": 50.00

    }

  ]

}

And the data in the desired format to be included in the CRM.

Target data (CRM)

{

  "order_id": "123456",

  "customer": {

    "name": "John Doe",

    "email": "john@example.com"

  },

  "products": {

    "id": [

      "ABC123",

      "DEF456"

    ],

    "name": [

      "Widget",

      "Gadget"

    ],

    "quantity": [

      2,

      1

    ],

    "price": [

      25.25,

      50

    ]

  },

  "total_amount": 100.5

}

The data is the same, but the naming and way of organizing the information are different for the two systems.

To achieve the desired result, let’s map the JSON format from the source to the JSON format of the destination.

This mapping may involve renaming fields, restructuring data, and performing calculations.

For example:

Map "orderId" to "order_id".
Map "customerName" to "customer.name".
Map "email" to "customer.email".
Map each item in the "items" array to the corresponding structure in the "products" array.
Add up the price of all items to calculate the total.

In this specific case, the expression we will use to obtain the mapping above will be:

JSON Transformation expression

{

  "order_id": orderId,

  "customer": {

    "name": customerName,

    "email": email

  },

  "products": items {

    "id": productId,

    "name": productName,

    "quantity": quantity,

    "price": price

  },

  "total_amount": totalAmount

}

After applying the JSON transformation expression, the resulting JSON data will be compatible with the CRM system and can be sent as a request to create a new order record.

Thanks for your feedback!

EDIT

Share your suggestions with us!
Click here and then [+ Submit idea]