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:
  • On the create request screen, click
    to open the advanced settings.
  • Scroll down to Response JMESPath and enter an expression.

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:
{
"coins": [
{
"id": "bitcoin",
"symbol": "btc",
"name": "Bitcoin",
"current_price": 27151
},
{
"id": "ethereum",
"symbol": "eth",
"name": "Ethereum",
"current_price": 1892
},
{
"id": "tether",
"symbol": "usdt",
"name": "Tether",
"current_price": 1.001
}
]
}
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:
[
{
"id": "bitcoin",
"name": "Bitcoin"
},
{
"id": "ethereum",
"name": "Ethereum"
},
{
"id": "tether",
"name": "Tether"
}
]

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.
{
"coins": [
{
"id": "bitcoin",
"symbol": "btc",
"name": "Bitcoin",
"current_price": 27151
},
{
"id": "ethereum",
"symbol": "eth",
"name": "Ethereum",
"current_price": 1892
},
{
"id": "tether",
"symbol": "usdt",
"name": "Tether",
"current_price": 1.001
}
]
}
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:
[
{
"price": 27151
},
{
"price": 1892
},
{
"price": 1.001
}
]

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
==
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.
{
"coins": [
{
"id": "bitcoin",
"symbol": "btc",
"name": "Bitcoin",
"current_price": 27151
},
{
"id": "ethereum",
"symbol": "eth",
"name": "Ethereum",
"current_price": 1892
},
{
"id": "tether",
"symbol": "usdt",
"name": "Tether",
"current_price": 1.001
}
]
}
We would use the expression coins[?current_price > 100]. The filtered JSON would look like this:
[
{
"id": "bitcoin",
"symbol": "btc",
"name": "Bitcoin",
"current_price": 27151
},
{
"id": "ethereum",
"symbol": "eth",
"name": "Ethereum",
"current_price": 1892
}
]

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.
{
"coins": [
{
"id": "bitcoin",
"stats": {
"current_price": 27105,
"market_cap": 525583712973
}
},
{
"id": "ethereum",
"stats": {
"current_price": 1891.76,
"market_cap": 227343546906
}
}
]
}
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:
[
{
"id": "bitcoin",
"price": 27105
},
{
"id": "ethereum",
"price": 1891.76
}
]

Select the Correct Top-Level Array

Let's say we have the following JSON, which has two top-level arrays, coins and exchanges :
{
"coins": [
{
"id": "bitcoin",
"current_price": 27105
},
{
"id": "ethereum",
"current_price": 1891.76
}
],
"exchanges": [
{
"id": "ace",
"name": "ACE"
},
{
"id": "nominex",
"name": "Nominex"
},
{
"id": "felixo",
"name": "Felixo"
}
]
}
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:
[
{
"id": "ace",
"name": "ACE"
},
{
"id": "nominex",
"name": "Nominex"
},
{
"id": "felixo",
"name": "Felixo"
}
]
​