System StatusSamples on GitHub

OutputTransform

The outputTransform property defines how the response from a REST API is transformed into a format suitable for insertion into a local SQLite table. The transform is written using JSONata and allows for both structural mapping and logic-based processing.

Use outputTransform to:

  • Extract and reformat data from an API response.

  • Return a single object or an array of objects.

  • Map data into a structure that can be inserted into local tables.

How Output Transform Works

  • When the result of the outputTransform is an array of objects, each item in the array is inserted as a separate row in the local SQLite table.

  • Each key-value pair in the resulting object becomes a column-value pair in the SQLite row.

  • The insertion is performed using SQLite’s json_extract() function to map JSON fields to database columns.

Extracting Orders from a Customer Record

This transform extracts the orders array from the response. Each order object is inserted into the SQLite table as a separate row.

API Response (JSON)
{
  "customerId": "1432",
  "customerName": "Johson Shipping",
  "shippingAddress1": "7645 1st Street",
  "shippingCity": "Nashville",
  "shippingState": "TN",
  "shippingZip": "78654",
  "orders":[
    {
      "orderNumber": "1234",
      "orderDescription": "Printer Refil",
      "orderTotal": 345.67,
      "customerId": "1432",
      "orderDate": "1-29-2022"
    },
    {
      "orderNumber": "654",
      "orderDescription": "Printer Paper",
      "orderTotal": 185.27,
      "customerId": "1432",
      "orderDate": "1-29-2022"
    },
    {
      "orderNumber": "8934",
      "orderDescription": "Envelopes",
      "orderTotal": 25.88,
      "customerId": "1432",
      "orderDate": "1-29-2022"
    }
  ]
}

Output transform to get an array of orders: $.orders. $ is the root of the document and orders the array with the collection of orders.

The result of the transform is:

JSONata in Output Transforms

You can enhance output transforms using built-in JSONata functions such as $trim(), $substring(), $map(), and $exists().

Example: YouTube Playlist Items

This example shows how to transform complex nested data. It uses logic and helper functions to clean up fields and format dates.

This example shows how to transform complex nested data. It uses logic and helper functions to clean up fields and format dates.

Mapping Nested Arrays

You can also use $map() to transform nested arrays into structured fields within a single object. This example returns a single row with structured ingredients and instructions arrays. Use this pattern when you want to store related subitems within a single SQLite row, e.g., for display in a list jig using $.eval().

PreviousInputTransformNextforRowsinRange

Last updated

Was this helpful?