Articles in this section

Transformation 2.0 examples

These examples show common Rules 2.0 transformations, each with a sample input record, the rules to configure, and the resulting output. Use them as starting points when you build your own transformation on an export or lookup. Most examples use the same veterinary clinic record so you can compare the techniques. For the full procedure, see Build a transformation with Rules 2.0.

Sample input record

Most examples use this input record or the flat variation noted in an example. The JSON string and rows examples include their own input records.

{
  "id": 104,
  "name": "Lucy",
  "breed": "Bulldog",
  "owner": {
    "owner_name": "Mike Brown",
    "owner_id": 1004,
    "owner_phone": "+1-555-4321"
  },
  "dietary_preferences": {
    "brand": "Blue Buffalo",
    "cups": 1,
    "type": "Grain"
  },
  "store_id": 124,
  "store_name": "Furry Friends Animal Hospital"
}

Flatten a nested object

Flattening moves fields out of a nested object to the top level of the record, so later steps can reference them without a path. This example creates a new record that contains the pet's name and the owner's details at the top level.

Settings: JSON structure = record { }, Input context = record

Output field Data type Input field
petName String $.name
ownerName String $.owner.owner_name
ownerPhone String $.owner.owner_phone

Output:

{
  "petName": "Lucy",
  "ownerName": "Mike Brown",
  "ownerPhone": "+1-555-4321"
}

Because JSON structure is record { }, fields without a rule don't appear in the output.

Build a string array from several fields

You can combine values from different input fields into one String array output field by entering comma-separated JSONPath expressions in the Input column. This example uses a flat variation of the sample record, where owner_name, owner_id, and owner_phone are top-level fields.

Settings: JSON structure = Modify input record { }, Input context = record

Output field Data type Input field
ownerContact String array $.owner_name,$.owner_id,$.owner_phone

Output (added field):

"ownerContact": [
  "Mike Brown",
  "1004",
  "+1-555-4321"
]

Rules 2.0 converts the number 1004 to the string "1004" because the output data type is String array. Because JSON structure is Modify input record { }, the rest of the input record passes through unchanged.

Build an object array from several sources

You can build one Object array output field from several input objects. Enter the source paths, comma-separated, in the Input column. The editor then shows one tab per source, and you map child fields for each source on its own tab. Each source becomes one object in the output array.

Settings: JSON structure = Modify input record { }, Input context = record

Output field contacts, data type Object array, input field $.owner,$.dietary_preferences.

Source tab Output child field Input field
$.owner parent $.owner.owner_name
$.owner phone $.owner.owner_phone
$.dietary_preferences brand $.dietary_preferences.brand
$.dietary_preferences grain $.dietary_preferences.type

Output (added field):

"contacts": [
  {
    "parent": "Mike Brown",
    "phone": "+1-555-4321"
  },
  {
    "brand": "Blue Buffalo",
    "grain": "Grain"
  }
]

Translate values with a static lookup

A static lookup replaces an input value with a value you define. This example replaces the store name with an internal location code.

Settings: JSON structure = Modify input record { }, Input context = record

  1. Add an output field location with data type String and input field $.store_name.
  2. Select Settings, and set Field transformation type to Lookup.
  3. Select Edit mapping, and add a row with Input field value Furry Friends Animal Hospital and Output field value location A.
  4. In Lookup name, enter Store.
  5. In If lookup fails, keep Fail record, so records from unknown stores fail instead of continuing without a location.

Output (added field):

"location": "location A"

Convert a JSON string into an object

Some applications return JSON as a string inside a field. Copying that string as-is to an object output field turns it into a real object, so later steps can reference its child fields.

In this example, the input field ai contains a JSON string.

Settings: Input context = envelope

  1. Add an output field aiAnswer. Select its data type selector (T ▾), and under Convert to, select Object.
  2. In the Input column, enter $.record.ai, and set the input data type to String.
  3. Select Settings. For Copy an object from the source as-is?, select Yes, and set Field transformation type to Standard.
  4. In Action to take if input field has no value, select Use null as default value.
  5. Select Confirm.

Input (excerpt):

{
  "record": {
    "ai": "{\"customer_profile\":\"Titan Distributing is a US-based distributor.\",\"customer_business_overview\":{\"summary\":\"Operates in e-commerce distribution.\"}}"
  }
}

Output (excerpt):

{
  "aiAnswer": {
    "customer_profile": "Titan Distributing is a US-based distributor.",
    "customer_business_overview": {
      "summary": "Operates in e-commerce distribution."
    }
  }
}

Convert a record into rows

The rows [ ] structure turns one input record into an array of rows. Use it when the next step writes a file in which related rows must stay together, such as a CSV file with one row per order line.

Settings: JSON structure = rows [ ], Input context = record

Input:

{
  "orderId": "SO-1001",
  "lines": [
    { "sku": "A-1", "qty": 2 },
    { "sku": "B-7", "qty": 1 }
  ]
}

With rows [ ] selected, the editor shows a root array row with no output field name. Enter the source array in that row's Input column, with [*], and add the row's fields as its child fields.

Output field Data type Input field
(root array) Object array $.lines[*]
orderId String $.orderId
sku String $.lines.sku
qty Number $.lines.qty

Child paths that point into the array omit [*], so each row gets the value from its own array item. A path outside the array, such as $.orderId, repeats the same value on every row. If you add [*] to a child path, such as $.lines[*].sku, each row gets every item's value, such as A-1,B-7, instead of its own.

Output:

[
  { "orderId": "SO-1001", "sku": "A-1", "qty": 2 },
  { "orderId": "SO-1001", "sku": "B-7", "qty": 1 }
]