Filter for Specific Fields and Values (JMESPath)

Table of Contents

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. 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
❓ This query syntax is designed for JSON responses only.

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.

filter-fields-img1

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:

syntax: PARENT_ARRAY_NAME[*].{NEW_FIELD_NAME:PATH_TO_CHILD_ID, NEW_FIELD_NAME:PATH_TO_CHILD_ID}

Additional fields would be appended to the list using the same syntax, separated by a comma each time.

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

If there is no parent array, just leave that piece out at the beginning, such that your JMESPath query looks like this:

{order_id:order_id, email:buyer_email}

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

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!

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

Comments:4

    1. 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.

    1. 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.

Leave a Reply

Your email address will not be published.