Response JMESPath
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:
Set the Response JMESPath
You can test your expression with your response JSON here. Once you are happy with it, you can use it in Data Fetcher:
Scroll down to Response JMESPath and enter an expression.
You will need to reconfigure the response field mappings, since the shape of the response JSON has changed. Run the request again or refresh the response field mappings.
Examples
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.
Keep Only Specific Fields
We can 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 {}
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 :
between the field names in the next example.
Our transformed JSON will look like this:
Transform Field Names
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 {}
.
Our transformed JSON looks like this:
Keep Only Data that Meets Specific Conditions
Conditions begin with ?
and use operators to evaluate data.
Here is a list of common operators with example expressions. In the expressions, []
encloses each condition because the condition is applied to an array.
Operator | Example JMESPath | Example in words |
---|---|---|
|
| In the |
|
| In the |
|
| In the |
|
| In the |
|
| In the |
|
| In the |
|
| In the |
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:
Query Nested Data
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:
Select the Correct Top-Level Array
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[]
. Our transformed JSON will look like the following and we will get a different record for each exchange, rather than each coin:
Last updated