Search API Connector Documentation

Print

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 install API Connector for a free trial or 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.

Before You Begin

If you haven’t already, click here to install the API Connector add-on from the Google Marketplace.

Get Your JSON

The first step of working with JMESPath is to view the JSON output of your API request.

API Connector doesn’t return raw JSON, since it converts it into a tabular format for Google Sheets. Therefore we will jump to a different API tool for this. I suggest using Swagger Inspector, since it’s web-based and pretty easy to use, but you can use any API client you like.

In this example I’ve pasted in my request to the iTunes API, clicked Send, and can now see the JSON response. Note that I limited my request to 2 records, since I just want to view the structure.

Quick JSON Overview

If you already know about JSON, you can skip right past this section. JSON is a data format made up of objects and arrays, and we have to identify these structures to create JMESPath expressions.

  • Objects are unordered collections of name/value pairs. They start and end with curly braces.
{"wrapperType": "track",
"kind": "song",
"artistId": 657515}
  • Arrays are ordered lists of values. They start and end with square brackets. Arrays can contain lists of objects, like this:
"results": [
{ "artistName": "Radiohead",
  "collectionName": "In Rainbows",
  "trackName": "Weird Fishes / Arpeggi"},
{ "artistName": "Radiohead",
  "collectionName": "In Rainbows",
  "trackName": "15 Step"}
]

If you aren’t used to JSON yet, all you really need to know for now is if you see [] it’s an array, and if you see {} it’s an object.

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: choosing which fields to print into your spreadsheet.

When we choose those fields, we need to identify the exact path to their location, where paths to values in arrays take the format array_name[].field and paths to values in objects take the format object_name.field.

The general syntax for setting up a JMESPath expression is like this, where we first provide a new key (name) for the field and then provide the path to that field:

array_name[].{new_key1:path1, new_key2:path2}

Examples are right below.

Example 1: Query for Specific Fields

In this example, we’ll request the trackName and collectionName fields from our JSON in part 1. Here’s that JSON response again (abbreviated for convenience).

{
    "resultCount": 2,
    "results": [
        {
            "wrapperType": "track",
            "kind": "song",
            "artistId": 657515,
            "collectionId": 1109714933,
            "trackId": 1109715168,
            "artistName": "Radiohead",
            "collectionName": "In Rainbows",
            "trackName": "Weird Fishes / Arpeggi"
        }
    ]
}

By checking the JSON, we can see that trackName and collectionName are located inside the results array, such that our JMESPath expression looks like this. We’re keeping the key names the same:

results[].{trackName:trackName,collectionName:collectionName}

Enter this into API Connector like this:
jmespath-img2

This will result in the following output:
jmespath-img3

Example 2: Query Nested Fields

In some cases, values may be nested inside other objects and arrays. As mentioned above we need to recognize the data structure to identify the path. In this next 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"
		}
	}]
}

To retrieve the amount and the email address, we’d set our query like this, since fields nested within objects are identified with a dot:

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

Fields nested within arrays are accessed with [], so if you instead had, say, a product ID nested within a “products” array, you’d create an expression like this:

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

Example 3: 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 more convenient 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

  • If you are working with deeply nested or complicated JSON, it might be hard to visually recognize the path to your desired fields. In those cases it usually helps to use a JSON navigator, for example this one: https://jsoneditoronline.org/.
  • Here’s a video tutorial with some advanced tips for using JMESPath. One useful tip I got myself from the video is that JSON Editor Online provides a JMESPath wizard 😮
    jmespath-img4
  • 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 they have an important difference:
    1. 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}
    2. This second expression will evaluate the entire data array, and keep these elements synced between columns. Therefore this is the better option:
      data[].{date:date,timezone:timezone}

Troubleshooting

  • 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.
  • In the case that your JSON keys are all numeric, you’ll need to enclose them in quotes, e.g. "123"[].{order_id:order_id}.
  • Avoid using dashes in your key names.
  • If you enter an invalid JMESPath expression you may see error messages like “Request failed: Server response not in JSON, XML, or CSV format” or “Expected argument of type object, but instead had type X“. To address, make sure your JMESPath is correct and returns a valid JSON object (e.g. if you have a field named ID, add a JMESPath query of {id:id} rather than simply id).

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

16 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
  2. Can i solve following case with JMESPath?

    Input JSON:
    [{"Country":"US","Address":[{"Street":"1212, Jackson Ave","Block":"100","City":"LA"},{"Street":"90 Mary St","Block":"92","City":"San Jose"}]},{"Country":"England","Address":[{"Street":"10, 1st Aven","Block":"87","City":"London"}]}]

    Desired output:

    [{"Street":"1212, Jackson Ave","Block":"100","City":"LA","Country":"US"},{"Street":"90, Mary St","Block":"92","City":"San Jose","Country":"US"},{"Street":"10, 1st Aven","Block":"87","City":"London","Country":"England"}]

    Basically i wanted to inject outer attribute to each slice

    Reply
    • Hey Mahe, not sure if this will help, but I played around a bit on the JMESPath site and got it to work for the 1st value in the array with this JMESPath:
      [].{Country:Country,Street:Address[0].Street,Block:Address[0].Block,City:Address[0].City}
      For better help I suggest checking the docs or the JMESPath Gitter chat.

      Reply
  3. I have:

    {
    “rows”: [
    {
    “keys”: [
    “2021-01-01”
    ],
    “clicks”: 165,
    “impressions”: 2382,
    “ctr”: 0.069269521410579349,
    “position”: 5.8350125944584379
    },
    … Repeat
    ]}

    But I simply can`t have something like: rows[*].{key:keys[0],click:clicks} on your tool, it works on JMESPath website tho.

    Reply
    • Hey Diego, what kind of error are you getting? I just tested it myself and it seemed fine, I got the same results through API Connector as I do on jmespath.org.

      Reply
  4. {
        "status": 1,
        "stats": [
            {
                "partition": {
                    "day": 7,
                    "month": 3,
                    "year": 2020,
                    "client": {
                        "cid": "5fdcaac1cfda9f6a5a2a9410",
                        "title": "abc"
                    },
                    "partner": {
                        "pid": 28,
                        "name": "test"
                    },
                    "product": {
                        "id": 313,
                        "external_id": "1234"
                    },
                    "p3": "abc"
                },
                "supply": {
                    "raw": "45",
                    "uniq": "45"
                },
                "actions": {
                    "total": {
                        "t_amt": 0,
                        "t_count": 0
                    },
                    "hold": {
                        "h_amt": 0,
                        "h_count": 0
                    }
                },
                "imp": 0,
                "fallback": 0
                }
        ]
    }

    Could you please help with the Xpath for the above JSON, I tried but its not working as expected

    Output : day,month,year,cid,pid,id,p3,raw,unique,t_amt,h_amt,imp,fallback

    Reply
    • Can you please try this?
      stats[].[{partition:partition.{day:day,month:month,year:year,client_cid:client.cid,partner_id:partner.pid,product_id:product.id,p3:p3}},{supply:supply},{actions:actions.{t_amt:total.t_amt,h_amt:hold.h_amt}},{imp:imp,fallback:fallback}]

      Reply
  5. I need to validate that the hour Is equal to 11:04 using jmespath in:departureDateTime=2021-03-20T11:04:00
    could you please advise on how i can do it?
    Thanks

    Reply

Leave a Comment