Response JMESPath
Last updated
Last updated
Data Fetcher lets you use JMESPath to transform the JSON response from an API. JMESPath is a powerful query language for JSON with many useful applications:
You can test your expression with your response JSON on JMESPath's website. Once you are happy with it, you can use it in Data Fetcher:
On the create request screen, click to open the advanced settings.
Scroll down to Response JMESPath and enter an expression.
You will need to reconfigure the response field mappings, because the shape of the response JSON has changed. Run the request again or refresh the response field mappings.
You can follow the official JMESPath tutorial to learn how to write JMESPath expressions or learn from the following examples.
In these examples, the result is always JSON, i.e. not a string or number. This is important as Data Fetcher will then transform the JSON to Airtable fields and records using your selected response data transformation.
We can choose to keep only specific field names for each object. Let's imagine we have the following JSON and want to keep only the id and name fields:
All our data is nested inside the coins array, so our JMESPath expression will start with coins[].
We then need to query the nested object fields using coins[]{}. In between the curly braces {} we list the object field names that we want to keep. Any field names not included will be filtered out.
Our final expression is coins[].{id:id, name:name}. We will explain the colon : between the field names in the next example.
Our transformed JSON will look like this:
We can rename field names to make them easier to work with. Let's imagine we have the following JSON and want to rename the current_price to price.
Our expression would be: coins[].{price:current_price}.
As in the previous example, our expression starts with coins[].{} because the array of objects is nested under a coins field. In JMESPath expressions, the new field name goes to the left of a colon, and the existing field or path name goes to the right, so we enter price:current_price between the curly braces {}.
Our transformed JSON looks like this:
Conditions begin with a question mark ? and use operators to evaluate data.
Here is a list of common operators with example expressions. In the expressions, square brackets [] enclose each condition because the condition is applied to an array.
==
countries[?code == 'GB']
In the countries array, keep objects where the code is equal to GB.
>
coins[?price > `100`]
In the coins array, keep objects where the price is greater than 100.
>=
coins[?price >= `100`]
In the coins array, keep objects where the price is greater than or equal to 100.
<
coins[?price < `100`]
In the coins array, keep objects where the price is less than 100.
<=
coins[?price <= `100`]
In the coins array, keep objects where the price is less than or equal to 100.
||
countries[?code == 'GB' || code == 'BR']
In the countries array, keep objects where the code is equal to GB or BR.
&&
coins[?price > `100` && price < `1000`]
In the coins array, keep objects where the price is greater than 100 and less than 100.
Let's imagine we have the following JSON and want to keep only the objects where current_price is greater than 100.
We would use the expression coins[?current_price > 100]. The filtered JSON would look like this:
You can also query nested objects and arrays.
Arrays are accessed with the syntax array_name[].
Objects are accessed with the syntax object_name.
Let's say we have the following JSON, which contains nested objects.
You could use the expression coins[].{id:id, price:stats.current_price} to make the nested current_price field a top-level field called price. Noticed that we're using stats. at the start of stats.current_price, since it's a nested object field.
The transformed JSON would be:
Let's say we have the following JSON, which has two top-level arrays, coins and exchanges :
By default, Data Fetcher will split our data into records based on the first array field, coins. If we instead want to split based on exchanges, we can use the following expression: exchanges[]. We will get a different record for each exchange rather than each coin, and our transformed JSON will look like this:
