How to use JMESPath for External Data Source Transformation

Templafy's data transformation feature uses JMESPath to transform JSON data. This article provides a deeper technical breakdown of syntax, best practices, and examples.

What is JMESPath?

JMESPath is a query language that is used to transform JSON data. More information about JMESPath can be found here: https://jmespath.org/.

A basic tutorial for JMESPath is available here: https://jmespath.org/tutorial.html. Using the tutorial as a guide, those concepts can be applied to the data transformation in Templafy. 

Getting Started with the basic syntax

To start writing a transformation, it must be wrapped in curly brackets {}. This is called a hash and allows for the selection of multiple objects to include in the output. Templafy uses JMESPath multi-select hashes. 

Example:

{
   Output_Name_One: Input_Name_One,
   Output_Name_Two: Input_Name_Two
}

Typically, the format that will be used for transformation is OutputKeyName: <Expression using raw input fields>. Each expression, or line, should be separated by a comma.

Use Cases

Use Case 1: Create an Output that is identical to the Input

Before writing any transformation, the first step is to select the related entities to include on the external data source. In cases where no actual transformation is needed, but the related entities should be limited based on the selections chosen, the @ symbol can be used. In JMESPath, the @ symbol represents the current 'node' being evaluated, and can be used to refer to the entire input.

Example:

{
  "Field1": "Value1",
  "NestedObject": {
     "NestedKeyName1": "NestedValue1",
     "NestedKeyName2": "NestedValue2"
   }
}

@

{
  "Field1": "Value1",
  "NestedObject": {
     "NestedKeyName1": "NestedValue1",
     "NestedKeyName2": "NestedValue2"
   }
}

Use Case 2: One-to-One Mapping of the Input to Output field

It is likely that in the case of transforming a data source, there will be several fields needed in a template that do not need to be changed. In this case, the expression will simply be the KeyName of the input.

Example:

{
  "InputKeyName": "Input Value"
}

{
  OutputKeyName: InputKeyName
}

{
  "OutputKeyName": "Input Value"
}

Use Case 3: Getting Nested Data

In cases where data is nested, and the desired output is a flattened data structure, the nested data can be returned with a subexpression.

Example:

{
   "Field1": "Value1",
   "NestedObject": {
      "NestedKeyName1": "NestedValue1",
      "NestedKeyName2": "NestedValue2"
    }
}

{
 OutputKeyName: NestedObject.NestedKeyName1
}

{
 "OutputKeyName": "NestedValue1"
}

Use Case 4: Getting Specific Data from Arrays

In cases where the desired index of an array is known, it is possible to get just information from that item. This will use Index Expressions. When working with index expressions, 0 is the first element in the array, 1 is the second element in the array, etc..

To find the last element in an array, -1 is used.

Example:

{
   "Username": "russellwhyte",
   "Trips": [
     {
       "TripId": 1,
       "Name": "Trip in New York"
    },
    {
      "TripId": 2,
      "Name": "Trip in Beijing"
    },
    {
     "TripId": 3,
     "Name": "Trip in London"
    }
  ]
}

{
   ArrayOutput: Trips[0]
}

{
  "ArrayOutput": {
    "TripId": 1,
    "Name": "Trip in New York"
  }
}

Use Case 5: Getting Specific Fields from Each Item in an Array

In cases where specific fields are required from each element in an array, a Wildcard Expression (*) can be used in combination with another Multi-Select Hash.

Example:

{
  "Username": "russellwhyte",
  "Trips": [
  {
   "TripId": 1,
   "Name": "Trip in New York",
   "Budget": 3000
  },
  {
   "TripId": 2,
   "Name": "Trip in Beijing",
   "Budget": 2000
  },
  {
   "TripId": 3,
   "Name": "Trip in London",
   "Budget": 2600
  }
 ] 
}

{
  ArrayOutput: Trips[*].
  {
   Name: Name,
   Budget: Budget
  }
}

{
  "ArrayOutput": [
   {
    "Name": "Trip in New York",
    "Budget": 3000
   },
   {
    "Name": "Trip in Beijing",
    "Budget": 2000
   },
   {
    "Name": "Trip in London",
    "Budget": 2600
   }
 ]
}

Use Case 6: Filtering Arrays to return only items that match specified conditions

In cases where only certain elements in an array are needed based on specific conditions, Filter Expressions can be used.

Example:

{
   "Username": "russellwhyte",
   "Trips": [
   {
    "TripId": 1,
    "Name": "Trip in New York",
    "Budget": 3000
   },
   {
    "TripId": 2,
    "Name": "Trip in Beijing",
    "Budget": 2000
   },
   {
    "TripId": 3,
    "Name": "Trip in London",
    "Budget": 2600
   }
  ] 
}

{
HighBudgetTrips: Trips[?Budget > `2500`]
}

{
  "HighBudgetTrips": [
   {
    "TripId": 1,
    "Name": "Trip in New York",
    "Budget": 3000
   },
   {
    "TripId": 3,
    "Name": "Trip in London",
    "Budget": 2600
   }
  ]
}

Use Case 7: Output one value if a condition is true and output a different value otherwise (If-Else logic)

In cases where one value should be added where a condition is true, and a different value should be added if that condition is not true, a specific syntax can be used to take advantage of the operators && and ||.

This syntax is: <condition> && <value if condition is true> || <value if condition is false> 

Example:

{
  "SubscriptionId": 2,
  "CustomerId": 234,
  "ShowContractIn": "Monthly",
  "AnnualCost": 1200.00,
  "MonthlyCost": 100.00 
}

{
  ContractCost: ShowContractIn == 'Monthly' && MonthlyCost || AnnualCost
}

{
  "ContractCost": 100.00
}

Use Case 8: Perform math on input values for a calculated output

In cases where simple arithmetic is needed on input values, this can be accomplished easily in JMESPath by using the following operators:

• add: +
• subtract: -
• multiply: *
• divide: /

Example:

{
  "ProductId": 1,
  "Price": 100.00,
  "Discount": 5.00
}

{
  NetPrice: Price - Discount
}

{
  "NetPrice": 95.00
}

Use Case 9: Use Function Expressions

Templafy uses a comprehensive community library of supported functions as well as some custom functions. This article includes syntax and examples for each function, but the below example demonstrates how this will look in Templafy.

Example:

{
  "FirstName": "Luisa",
  "LastName": "Green",
  "UserID": "lgreen01@comapny.com"
}

{
  DisplayName: join(', ',[LastName, FirstName]),
  FullName: join(' ',[FirstName, LastName])
}

{
  "DisplayName": "Green, Luisa",
  "FullName": "Luisa Green"
}

Use Case 10: Transform the result of another transformation

In cases where the result of a transformation should be used in a final transformation, pipe expressions can be used.

Example:

{
  "Username": "russellwhyte",
  "Trips": [
  {
    "TripId": 1,
    "Name": "Trip in New York",
    "Budget": 3000
  },
  {
    "TripId": 2,
    "Name": "Trip in Beijing",
    "Budget": 2000
  },
  {
    "TripId": 3,
    "Name": "Trip in London",
    "Budget": 2600
  }
 ] 
}

{
  Output: Trips[?Budget >= `2500`] | [0]
}

{
  "Output": {
    "TripId": 1,
    "Name": "Trip in New York",
    "Budget": 3000
  }
}

Use Case 11: Perform Math Operations on Array Values

In cases where math operations on values in an array should be used, it is possible to do this with JMESPath by combining the concepts of wildcards or filter expressions and functions.

Example:

{
  "Username": "russellwhyte",
  "Trips": [
   {
    "TripId": 1,
    "Name": "Trip in New York",
    "Budget": 3000
  },
  {
    "TripId": 2,
    "Name": "Trip in Beijing",
    "Budget": 2000
  },
  {
    "TripId": 3,
    "Name": "Trip in London",
    "Budget": 2650
  }
 ] 
}

{
  TotalBudget: sum(Trips[*].Budget),
  AverageBudget: avg(Trips[*].Budget),
  MostExpensive: max(Trips[*].Budget),
  LeastExpensive: min(Trips[*].Budget) 
}

{
 "TotalBudget": 7650,
 "AverageBudget": 2550.0,
 "MostExpensive": 3000,
 "LeastExpensive": 2000
}