Filter for Specific Fields and Values (JMESPath)

By default, API Connector displays the entire response returned by any API request. However, there may be cases where you would like to query for specific fields or values in order to limit the data that gets returned or fix columns in place. API Connector provides this functionality through the JMESPath query syntax, which enables querying JSON APIs for specific fields and values.

This is a paid feature, please upgrade to access.

Contents

Why Filter Responses?

  • avoid running up against cell limits in Sheets (currently 5 million cells maximum)
  • improve Sheets performance by reducing data size
  • simplify reporting by requesting only the fields you need
  • enable consistent column order when an API shifts response fields between pulls
This query syntax is designed for JSON responses only.

General Syntax

The full JMESPath specification is a powerful but complex query language, so this article will focus only on the most common use case for API Connector: limiting the fields returned to your spreadsheet. The general syntax for querying fields is like this:

parent_array[*].{new_field_name1:path_to_value, new_field_name2:path_to_value}

Additional fields would be appended to the list using the same syntax, separated by a comma each time. If there is no parent array at the top, i.e. if all elements are contained within an object, that piece of the query would simply be excluded, like this:

{new_field_name1:path_to_value, new_field_name2:path_to_value}

In general, paths to values in arrays take the format array_name[*].field while paths to value in objects take the format object_name.field.

Example Starting Output

Let’s consider the following example output from API Connector in Google Sheets, with 3 orders by 3 buyers, each containing 2 products. This response output contains fields for order ID, buyer email, product name, and product ID. In this example, both “orders” and “products” are arrays.

filter-fields-img1

Filtered Example 1: Query Specific Fields

In this example, we’ll request the order ID and buyer email from the starting output shown above. They are both at the same level (i.e. not nested), so you can just list their field names.

Note that you name your new field headers yourself by including them in your expression in front of the colons. Here we’ve chosen the field name “email” as the new header for the original “buyer_email” field.

example: orders[*].{order_id:order_id, email:buyer_email}

Enter this into API Connector like this:
filter-fields-img2

This will result in the following output:
filter-fields-img3

Filtered Example 2: Query Specific Fields Nested in an Array

In this second example, we’ll request the order ID and the product ID. The product ID is nested within the “products” array, so in this variation we need to specify the path to the product ID in order to retrieve the correct field. The path to the products ID is specified as products[*].id, with the full request like this:

example: orders[*].{order_id:order_id, product_id:products[*].id}

This will result in the following:

filter-fields-img4

In the case that your JSON keys are all numeric, or contain dashes, you’ll need to enclose them in quotes, e.g. "123"[*].{order_id:order_id} or "my-key"[*].{order_id:order_id}

Filtered Example 3: Query Specific Fields Nested in an Object

In some cases, values may be nested inside an object rather than an array. You can check the original JSON structure by running your request in another API tool like https://inspector.swagger.io/builder. As a simple way to identify whether you’re looking at an array or an object, check whether your element is followed by a square bracket (array) or a curly bracket (object). In this example, “data” is an array, while “billing_details” is an object.

{  "data": [{
	"id": "1234567",
	"object": "charge",
	"amount": 100,
	"billing_details": {
		"email": "apple@email.com",
		"name": "Apple Appleby"
		}
	}]
}

In these cases, you will need to change your syntax slightly, by removing the wildcard [*] expression for projecting over the array. To retrieve the amount and the email address, we’d instead set our query like this:

example: data[*].{amount:amount,billing_email:billing_details.email}

Tip: As a shortcut, if your query doesn’t work, first try removing the wildcard expression within the path!
Tip 2: Occasionally you may run into JSON constructed of only objects. This is an undesirable data structure as it prevents API Connector from determining which data points should be returned as rows vs. columns (it typically results in all data on a single row, with a new column header for each element). In those cases, one option is to convert the data into a more useful structure with the values(@) function, e.g. Data.values(@) if Data is the name of the parent object.

Filtered Example 4: Query Two or More Top-Level Elements

I’m giving this its own section because it took me forever to figure out — and then it turned out the answer is very simple.

You may come across some JSON like this, where you want elements from two different top-level objects or arrays. For example, in this example JSON block you may want to get the number of records from the pagination object as well as date information from within the data array.

{    
  "pagination": {
        "limit": 100,
        "offset": 0,
        "count": 100,
        "total": 5000
    },
    "data": [
        {
            "date": "2020-06-08",
            "status": "scheduled",
            "timezone": "America/New_York"
    }]
}

To grab elements from both the pagination object and the data array at the same time, either of the following structures will work. You may find that one or the other provides a cleaner output for your data set.

{pagination_total:pagination.total,data:data[*].{date:date,timezone:timezone}}
[{pagination_total:pagination.total},data[*].{date:date,timezone:timezone}]

Tips

When you filter responses, pay attention to small differences in your JMESPath syntax. The following two expressions will yield almost the same result, as both expressions produce a column of dates and a column of time zones. However, this first expression evaluates each dataset separately. This means that any missing or null values will be ignored, such that populated data shifts up into the first open cell, and the two data sets may not match up across columns:

{date:data[*].date,timezone:data[*].timezone}

On the other hand, this second expression will evaluate the entire data array, and keep these elements synced between columns:

data[*].{date:date,timezone:timezone}

JMESPath Info & Expression Tester

You may use any features of the JMESPath query language for JSON. Check the tutorial for additional methods of retrieving specific values from JSON: https://jmespath.org/tutorial.html

If you would like to test your JMESPath syntax, you can paste over the JSON examples in the tutorial and enter your own query into the input field, like this:

filter-fields-img5

7 thoughts on “Filter for Specific Fields and Values (JMESPath)”

    • Yeahhh! Glad you discovered it 😀 This is actually one of my favorite features, it has a little bit of a learning curve but is so useful.

      Reply
    • The IMPORTAPI() custom function will execute with JMESPath enabled, just as it would if it were executed from the sidebar, and you’ll see your JMESpath-filtered results in your sheet. The one thing to look out for, though, is that Google allows custom functions to run for only 30 seconds, while sidebar requests can run for 6 minutes (documentation). This means that if you have a particularly large or slow request, you might run into problems if running it as a custom function. Besides that, though, there should be no difference.

      Reply
  1. Hello! Would this be a good place to filter down to specific endpoint values, and not just specific endpoints?

    i.e: Only bring in plays with “Sacks” as a type:

    1Play:
    Type: Sack

    2Play:
    Type: Touchdown

    Thanks

    Reply

Leave a Comment