Search API Connector Documentation


JMESPath Filtering


In most cases, you can use API Connector’s visual field editor to easily select the fields you’d like to see in your sheet. However, if you’re interested in more powerful filtering capabilities, read ahead for information on using JMESPath expressions in your requests.


Why Filter Responses?

  • change the report structure, for example to convert columns to rows
  • filter for certain fields or values in the response data
  • simplify reporting by requesting only the data you need

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.

To view the raw JSON, set up and save your request, and then click Output options > JMESPath > Preview API response. This will display the JSON response in a modal where you can copy or download it.

This article contains more information on previewing API responses:

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 collectionName and trackName fields from an example JSON response.

    "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’ll give them new names to demonstrate how that works:

results[].{"Track names":trackName,"Collection names":collectionName}

We need quotation marks around the new names because they contain a space (otherwise just enter them directly). Enter this into API Connector like this:

This will result in the following output:

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": "[email protected]",
		"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:


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.


Example 4: Convert Column Keys into Field Headers

API Connector now has a native option to flatten column keys into field headers without requiring JMESPath: Flatten Fields to Columns. The JMESPath below is just presented for reference.

You may encounter an inconvenient pattern where key-value pairs are listed within a parent header, like this:

In this example, we have multiple keys like “Store name” and “Order number” grouped together under, which makes the data hard to work with.

We can use JMESPath to restructure this such that each key gets its own column. To do so use this pattern to extract the keys:

{store_name:attributes[?name=='Store name']|[0].value,
order_number:attributes[?name=='Order number']|[0].value,
requested_ship_date:attributes[?name=='Requested ship date']|[0].value,

In words, each line is saying something like this: take the value of the attribute named ‘Store name’, and give it a field header of store_name.

This will result in a flattened output like this:


  • 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. I like this one:
  • You can reference cell values in your JMESPath expression. One example use case would be using a Sheets function like =MAX to find the maximum date in a column, then using that cell value as part of a JMESPath filter to filter for new data only.


  • In the case that your JSON keys start with a number or contain dashes or spaces, you’ll need to enclose them in quotes, e.g. "123"[].{order_id:order_id}.
  • 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).
  • [Advanced] 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:
    • 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}
    • This second expression will evaluate the entire data array, and keep these elements synced between columns. Therefore this is almost always the better option: data[].{date:date,timezone:timezone}
  • [Advanced] In some cases, you’ll find that an expression like properties[].{field1:field1,field2:field2} doesn’t always produce new fields for field1 and field2, resulting in mismatched columns. This can happen when the array (“properties” in this example) is not consistently populated, since the expression doesn’t work on a completely empty array. To resolve, modify the expression like this: properties[].{field1:field1,field2:field2} || {field1:"",field2:""}.
  • [Advanced] 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.

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:

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:

Previous ImportAPI Custom Function
Next Multi-Query Requests

81 thoughts on “JMESPath Filtering”

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

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

  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:

    Type: Sack

    Type: Touchdown


  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

    • 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:
      For better help I suggest checking the docs or the JMESPath Gitter chat.

  3. I have:

      "rows": [
          "keys": [
          "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.

    • 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

  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

    • Try this:

  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?

  6. Hi i was wondering if there was a way to get information from two arrays in the json? Ex
    data: [...],
    included: [...],

    I need information from both, but I’m unsure how to use both simultaneously. I currently have:

    Sorry if I’m unclear

  7. Hey Ana,
    I’m using api connector to pull shopify orders.
    Currently trying to apply jmespath filtering on a grid style report.

    The filtering works, but the output is no longer in ‘grid’ format.

    An order that used to span multiple rows prior to the filtering, now only takes up a single row. There’s data missing.

    Is there something i’m doing wrong?

    • When you apply JMESPath, it not only filters the data but changes the structure of the JSON, so it’s possible that the new shape no longer works as a grid. I can’t really say anything specific without knowing how you’ve filtered it, would you be able to share your JMESPath so I can check it?

      • thanks for the reply Ana,
        i think i understand.
        but just in case there’s a chance to salvage this method. here is the filter i’m using:

        with this filter, returns blanks.

      • Hey there, line_items is an array, but you’ve included it with a dot like an object.
        Can you please try this instead?

  8. Hello,
    I’d like to sort the column K in the Google sheet (direct from import) when data are comming from API conector.
    Is there a meaning to specify the sort column or A->Z filter on a specfic data ?
    Thank you very much,

  9. Hello!
    First of all congrats for this great tool! We are so so happy working with!

    I would love to ask about this:

    How Can I filter only for the first value?

    Thank you!

  10. hi,
    very interesting application I am testing it!

    Q: it is possible to carry out a search like the following
    data = {"card":[{"id":1,"P1":2},{"id":1,"P1":1},{"id":1,"P1":4}]}
    find = [1,4,6]

    card[?P1 =='1' || P1 =='4' || P1 =='6'].{id:id,P1 : P1}
    I m currently looking for it this way, is there another way where I can place a list of the matches?
    for sample
    card[?P! == find].{id:id,P1 : P1}
    card[?P! == ([1,2,6])].{id:id,P1 : P1}
    thanks for your help

    • In addition to the first way you suggested, you can do it this way:
      card[?contains(`["1", "4","6"]`, P1)].{id:id,P1:P1}

      There may be other ways, too, but those are the methods I’m familiar with.

      • Excellent, you have clarified the doubts … this response was quite quick and function correctly Thx…

  11. I need to connect with a Coinbase account, only getting the CONFIRMED/COMPLETED payment requests to a google sheet (expired ones are not useful). Does someone know how to write the path? Or either how to extend the limit beyond 100 lines (i have more than 100 payment requests)

    • Can’t really say much without knowing the structure of the data, can you please share some sample JSON or the URL you’re calling?

  12. The output structure is quite simple, just a grid where each line is a payment request and on the right there is a column which says “COMPLETED” or is in blanks. I need to have only the Completed ones.

    The standard API Response shows something like this:

    "time": "2017-01-31T20:50:02Z",
    "status": "COMPLETED"

    Data taken from:

    • Thanks for the link, based on their documentation you can use the following query:

      Also, you can get more than 100 records by pagination like this:
      API URL:
      Pagination: next page URL
      Field name: next_uri
      Number of pages: 5
      That would get you 500 records. The JMESPath filter gets applied after so you’d see fewer records than that in your sheet if you’re using both together.

      • The “COMPLETED” filter is working 100%, thank you so much!

        Regarding the amount of records, i’m still getting 100 records even with the parameters you’ve provided. The “Pagination Total” column tells me there are 126 total records so i’m still missing 26. I believe maybe Coinbase sets a 100 lines limit which cannot be changed?

      • Cool, I’m glad that worked. Their record limit can’t be changed, but API Connector’s pagination function will automatically cycle through and grab 100 records at a time.
        For field name, can you please try pagination.next_uri? (instead of next_uri). I didn’t notice the ‘pagination’ parent node before.

  13. Excellent! The filter and the records’ extension have now both worked. I spent days trying to make it work. So grateful. Thank you!

      • Hey Ana, i’ve been reviewing the work done with the filters you provided and i realized i need to filter not only “COMPLETED” lines but also “RESOLVED” ones (this means, lines that are either COMPLETED or otherwise RESOLVED). Is there a way to add one more requirement to the “data[?timeline[?status==’COMPLETED’]]” you provided last week?


  14. Hi ,

    Can you just get the raw value?

    "data": [
    "id": 1,
    "name": "tester",
    "location": "Ottawa",
    "matesite": null
    }“data[].location”, json)

    It returns [u’Ottawa’], but I just want to a raw value, which is Ottawa.

    I can achieve this with jq -r, but how to do with in python?


  15. Hi Ana,
    The preview is in XML. Does it change anything for the filtering ?
    How can I convert XML to JSON ?
    In my example below, what would be the structure
    to filter only on ID ?

    From a URL : /proposal_sheets.xml

    • JMESPath is a filtering language for JSON only, so generally it would not work with XML. If possible you should look for a JSON endpoint.

      With that said, if they don’t provide a JSON endpoint, I have a potential workaround for you. It only works because when API Connector processes an XML response, it first converts it to JSON (so please don’t try using JMESPath on XML outside of API Connector as it probably wouldn’t work).
      What you can do is use dot notation to access any nested element. The only time you’ll need to use array syntax is if you see type="array" within your XML.

  16. Hi
    I have a rest body similar to this example:

    "machines": [
    {​​​​​"name": "a", "type": "1","state": "running"}​​​​​,
    {​​​​​"name": "b","type": "2", "state": "stopped"}​​​​​,
    {​​​​​"name": "b","type": "1", "state": "running"}​​​​​

    how can i get the element from machines where name=a and state=running, in other words, filtering on multiple items of an element .

    Thanks a lot

  17. Hi, I’m trying to filter out results by ‘type’ from a website API but I keep getting an error ‘Request failed: Server response not in JSON, XML, or CSV format’ on API connector. Not sure if the filtering fits correctly here

    My JMESPath:

    from JSON :


    • This is the one of the entries in JSON i’d like to filter out of.
      The only difference here is ‘light’. But i’d like to filter out by specific type e.g fire,ice


    • Your filter doesn’t look quite right, can you please try this instead?

  18. Hi

    Here’s a sample of my JSON :
    [{"_id":103,"ankamaId":103,"name":"Anneau de Sagesse","level":9,"type":"Anneau","imgUrl":"","url":"","description":"Cet anneau renforce la sagesse de son porteur.","statistics":[{"Sagesse":{"min":7,"max":10}}],"recipe":[{"Os de Chafer":{"ankamaId":310,"url":"","imgUrl":"","type":"Os","level":5,"quantity":3}},{"Peau de Gloot":{"ankamaId":16522,"url":"","imgUrl":"","type":"Peau","level":2,"quantity":3}}],"setId":0},{"_id":112,"ankamaId":112,"name":"Anneau de Chance","level":9,"type":"Anneau","imgUrl":"","url":"","description":"Cet anneau renforce la chance de son porteur.","statistics":[{"Chance":{"min":7,"max":10}}],"recipe":[{"Bois de FrΓͺne":{"ankamaId":303,"url":"","imgUrl":"","type":"Bois","level":1,"quantity":3}},{"Volve d'Aminite":{"ankamaId":16523,"url":"","imgUrl":"","type":"Champignon","level":5,"quantity":3}}],"setId":0}]

    For each “_id”, i’d like to select only name, type, and the “ankamaId” and “quantity” fields of “recipe”.

    Would you know which JMESPath I need to use ?
    Thanks and happy new year

    • Possibly, if the first row has some JSON element that you can filter on, but there’s no generic “exclude first row” option if that’s what you mean (JSON isn’t in rows to begin with). If you just mean you want to remove headers, you can do that in the field editor.

  19. Hi, how can i filter the array by “image.os” field value and get all records? thanks in advance.

    "config": {
    "image.architecture": "amd64",
    "image.description": "ubuntu 20.04 LTS amd64 (release) (20220411.2)",
    "image.label": "release",
    "image.os": "ubuntu",
    "image.release": "focal",
    "image.serial": "20220411.2",
    "image.type": "squashfs"

  20. How can I extract unique values or remove duplicates from list of list or list of dictionaries using jamespath.

  21. Hello Ana. Great info. I am playing with jmespath now and struggling with the format of the json file request from an API. I am requesting info for 2 x crypto coins. The Json is different, seems to be nested??

    Stripped down version:
    {'1': {'id': 1, 'name': 'Bitcoin'.....}}}, '2': {'id': 2, 'name': 'Litecoin'.....}}}

    • no matter what I put in the search string, I get 0 result.

      search_string = ‘data’
      print(, data))

      using data in the search string returns the json
      {'1': {'id': 1, 'name': 'Bitcoin'…..}}}, '2': {'id': 2, 'name': 'Litecoin'…..}}}

      • Sorry I’m not really sure what problem you’re trying to solve. I see that all the data is nested into objects, which can cause data to appear on one line, is that the issue? If so you could put them onto separate lines by simply entering an asterisk (*) into the JMESPath field, since that would strip out the leading “1” and “2”. Let me know if that’s what you were looking for, or if I’ve misunderstood your question.

    • I don’t see anything about rounding to 2 digits in their documentation, they only have floor and ceil functions that round to integers. So I don’t think this is possible with JMESPath.

      • I didn’t find it either. πŸ™‚
        Does the API Connector itself know how to round the output?

      • API Connector just returns data from the API, it doesn’t round it or change it. Can’t you round data in Sheets itself?

      • Yeah, API Connector doesn’t change the data it retrieves. Not sure what you mean by can’t round in table, feel free to message support if you want to discuss.

  22. Hi Ana, thanks so much for your continued support on here.
    What is the syntax for grabbing 2 XML elements?
    EG: I require
    – boardgame.statistics.ratings.average
    – boardgame.statistics.ratings.averageweight

    • Thanks, Ryan πŸ™‚ JMESPath is only for JSON, not XML, though it could work if you’re using API Connector as we convert XML to JSON during our conversion process to Sheets. In that case the syntax would be exactly the same, since the JMESPath would be applied to the XML-converted JSON. If you search online you’ll find a lot of XML to JSON converters, so you can run your response data through one of those first to see the JSON structure, then create your JMESPath expression based on that.

  23. Hi, Ana!
    my json data looks like
    "key1": "some",
    "key2": "value"

    what a want to get is just raw data from key2 (value) without headers. When i am trying to type something like key2 or anything else i am getting key1 key2 (headers) output in a separate lines. Can you please help me? Can’t figure it out

    • I just uploaded your exact JSON, entered “key2” into the JMESPath line and clicked Run, and the response was just the word “value” without any headers. So I’m not sure why you’re seeing something else. Maybe you can try re-creating your request in case you have some setting interfering with the normal output, otherwise feel free to message support with a link to your sheet if you’d like me to take a look there.

  24. Hi Ana,
    I’m trying to filter Zendesk tickets with JMES Path for all records created in the last 30 days. Without a filter, the records exceed 4,500 and it times out most of the time.
    Any help is appreciated

    • Instead of using JMESPath, I’d use Zendesk’s native filtering, that way you request less data from the start.
      You can choose the /search.json endpoint from our Zendesk integration, then enter a query of created>2022-05-23.
      If it still times out after that, reduce the date range and click Edit Fields to reduce the number of returned columns.
      I don’t think you need JMESPath for any of this.


Leave a Comment

Table of Contents