API Connector Documentation

Print

JMESPath Filtering

Last Updated OnMarch 16, 2024
byAna
< Knowledge Base

API Connector enables the use of JMESPath, which is a powerful query language for JSON with many useful applications:

  • filter for certain fields in the response data
  • filter for records that meet specified conditions
  • change the report structure, for example to convert columns to rows or normalize JSON

If you just want to filter out fields from your report, you can use API Connector's visual field editor. However, if you're interested in more powerful filtering capabilities, read ahead for information on using JMESPath expressions in your requests.

Test any of the examples below with the Mixed Analytics JMESPath Expression Testing Tool

Contents

Before You Begin

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

How to Use JMESPath in API Connector

In API Connector, navigate to Output options > More options > JMESPath, and enter your expression into the JMESPath input field.

jmespath-how

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 ("ordered" means you can specify that you want, say, the second value in the list). Arrays start and end with square brackets, like this: [1,2,3]. They can (and often do) contain lists of objects.
"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.

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 > More options > JMESPath > View API response. This will display the JSON response in a modal where you can view, copy, or download it.

This article contains more information and another method for viewing raw API data.

Example 1: Query for Specific Fields

When setting up a JMESPath expression, the name of your new field goes to the left of the colon, while the location or path to the value goes on the right.

Example:

results[].{tracks:trackName,collections:collectionName}
jmespath-query-for-fields

The above example retrieves the trackName and collectionName fields from the results array of an example block of JSON, and renames those fields to "tracks" and "collections".

Since all the data points are nested inside the results array, the expression starts with results[]. More on that in the next example.

Example 2: Query Nested Fields

In most cases, values will be nested inside other objects and arrays, and there may be multiple levels of nested values. The underlying data structure will identify how we access those nested data points.

  • arrays are accessed with the syntax array_name[]. (square brackets followed by a period)
  • objects are accessed with the syntax object_name. (just a period, no square brackets)

In this example, all data is nested inside the data array, and the email and location fields are further nested inside the billing_details object.

Example:

data[].{amount:amount,billing_email:billing_details.{email:email,location:location}}
jmespath-query-nested

As mentioned above, fields nested within arrays are accessed with [], so if you instead had, say, a product ID nested within a products array, all nested within an orders array, the expression would look like this: orders[].{order_id:order_id, product_id:products[].{name:name,id:id}}

Example 3: Query Two or More Top-Level Elements

You may come across JSON in which you'd like to retrieve elements from two different top-level objects or arrays. This next example shows how you'd get the number of records from the main array as well as track data from within a results array.

Example:

{results_count:resultCount,results:results[].{artistName:artistName,trackName:trackName}}
jmespath-toplevel

As an alternative to the above, you can use the following JMESPath structure. You may find that one or the other provides a more convenient output for your data set.

[{result_count:resultCount},results[].{artistName:artistName,trackName:trackName}]

Example 4: Retrieve Data Matching a Condition

Conditions begin with the ? mark, and common operators include == for equals, > for greater than, < for less than, || for OR, and && for AND.

In the following examples, [] encloses each condition because the condition is applied to an array. This is the most common data structure, but check the next example if your data is instead nested inside an object.

OperatorExample JMESPath ExpressionIn Words...
==locations[?state == 'WA']in the locations array, get records where the state is WA
>people[?age > `20`]in the people array, get records where the age is greater than 20
<people[?age < `21`]in the people array, get records where the age is less than 21
||people[?age =='18' || age =='40' || age =='62']in the people array, get records where the age is 18, 40, or 62
&&machines[?name == '9S'&&state=='missing']in the machines array, get records with a name of 9S and state of missing

These conditions can be combined with any of the expressions shown above, e.g. people[?age > `20`].{name:name,address:address} would return the name and address fields for people older than 20.

Example:

locations[?state == 'NY']
jmespath-conditions

Example 5: Retrieve Data Matching a Condition in an Object

This is a variation on the prior example. In most cases, data will be located inside an array so you can directly use the filter syntax shown above. However, if the data you'd like to match on is nested within an object rather than an array, you'll need to first pull the fields you want, then add | (a pipe) to stop the projection, and then add the JMESPath filter expression.

Example:

data.*.{id:id,name:name,cmc_rank:cmc_rank}|[?cmc_rank<='5']
jmespath-objectfilter

Example 6: 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 still presented for reference.

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

{
  "data": [
    {
      "actions": [
        {
          "action_type": "landing_page_view",
          "value": "13"
        },
        {
          "action_type": "video_view",
          "value": "18"
        },
        {
          "action_type": "post_reaction",
          "value": "20"
        }
       ...
  ]}]}

Which results in an output like this:
filter-fields-unflattened

In this example, all the actions are grouped together in the data.actions.action_type field. We can use JMESPath to restructure this such that each key gets its own column, by querying for conditions as described in Example #4. To do so use this pattern to extract the keys:

data[].{
video_view:actions[?action_type=='video_view']|[0].value,
link_click:actions[?action_type=='link_click']|[0].value,
post_reaction:actions[?action_type=='post_reaction']|[0].value,
landing_page_view:actions[?action_type=='landing_page_view']|[0].value
}

In words, each line is saying something like this: if the action_type equals 'video_view', take the value and give it a field header of video_view.

This will result in a flattened output like this:
filter-fields-flattened

Example 7: Convert Simple Arrays into Rows & Columns

Another inconvenient pattern occurs when all data is returned in primitive arrays, i.e. lists of data like {"rows":[4PSXPeh9i9E,626,3211,3,21]}. When this occurs, API Connector is unable to determine which values should be associated with which field name. The exact output will vary depending on your report style, but here's an example from YouTube Analytics, showing all the data metrics grouped together, while names of the metrics and other metadata are provided separately.
jmespath-simplearray

Here we can use JMESPath to restructure this such that each data point gets its own column based on its position in the array. To do so, use a JMESPath expression like this:

rows[*].{video:[0], estimatedMinutesWatched:[1], views:[2], likes:[3],subscribersGained:[4]}

In words, it's saying something like this: for each array, extract the first value and give it a header of "video", extract the second value and give it a header of "estimatedMinutesWatched", and so on.

This will result in a structured output like this:
jmespath-simplearray-result-sheets

Example:

rows[*].{video:[0], estimatedMinutesWatched:[1], views:[2], likes:[3],subscribersGained:[4]}
jmespath-simplearray-result

Example 8: Get the First X Records

This example shows how you'd get the first 3 image URLs for each product in a list. Use the syntax [X:Y] to set a start and end for your slice of data, where X is the start value and Y is the end value.

Example:

products[].{id:id,product_handle:product_handle,images:images[0:3].{src:src} }
jmespath-example6

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. I like this one: https://jsoneditoronline.org/.
  • 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.

Troubleshooting

  • 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}.
  • Consider the following 2 expressions:

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

    Both expressions produce a column of dates and a column of time zones. However, the first expression contains data[] twice, while the second expression includes data[] just once. This processes all child elements together and keeps them synced, and is almost always the better option. In general, try not to repeat yourself when creating queries.
  • [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. API Connector's "force rows" option can also be used to force columns to display as rows.

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 use our JMESPath expression testing tool. To see results, just enter your own JMESPath expression and JSON, like this:

jmespath-tester

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

      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

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

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

  5. I have:

    {"rows":[{"keys":["2021-01-01"],"clicks":165,"impressions":2382,"ctr":0.06926952141057935,"position":5.835012594458438},
    ... 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

  6. {"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

    • 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

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

  8. 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:
    data[].{id:id,type:type,attributes:attributes.line_score}
    AND
    included[?type=='new_player'].{name:attributes.name,POS:attributes.position,TEAM:attributes.team}

    Sorry if I'm unclear

    Reply

  9. 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?

    Reply

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

      Reply

      • 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:
        orders[].{name:name,id:id,line_items_name:line_items.name}

        with this filter, line_items.name 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?
        orders[].{name:name,id:id,line_items:line_items[].{name:name}}

  11. 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,
    Laurent

    Reply

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

    I would love to ask about this:
    "nationality":["Argentinean","Spanish"]

    How Can I filter only for the first value?

    Thank you!

    Reply

  13. 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}
    or
    card[?P! == ([1,2,6])].{id:id,P1 : P1}
    thanks for your help
    regards

    Reply

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

      Reply

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

  14. 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)

    Reply

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

      Reply

    • Thanks for the link, based on their documentation you can use the following query:
      data[?timeline[?status=='COMPLETED']]

      Also, you can get more than 100 records by pagination like this:
      API URL: https://api.commerce.coinbase.com/charges?limit=100
      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.

      Reply

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

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

    Reply

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

        Thanks!

  18. Hi ,

    Can you just get the raw value?

    "data": [
    {
    "id": 1,
    "name": "tester",
    "location": "Ottawa",
    "matesite": null
    ]
    }
    jmespath.search("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 jmespath.search in python?

    Thanks!

    Reply

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

    Reply

    • 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 works because when API Connector processes an XML response, it first converts it to JSON.
      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.

      Reply

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

    Reply

  21. 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
    type[?type=='fire'].config.stats.type

    My JMESPath:
    data[].{id:id,tokenId:tokenId,Price:startingPrice,Available:status,type[?type=='fire'].config.stats.type,LE:config.farm.le,Hours:config.farm.hours}

    from JSON :
    data
    {"id":1000713156,"tokenId":224271,"startingPrice":67,"status":1,"config":{"visual":{},"stats":{"type":"fire"},"farm":{"le":431,"hours":48}}}

    Thanks!

    Reply

    • 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

      {"status":0,"data":[{"id":1011910930,"tokenId":534479,"ownerId":"0x9cf94145340f8e48ea591224466420f5f61086ba","plantTokenId":534479,"timeSell":1632271324,"startingPrice":47,"endingPrice":47,"duration":1718671316653,"status":1,"updatedAt":1632271324,"iconUrl":"https://pvuresources.s3.ap-southeast-2.amazonaws.com/icon/plant/19_1.png","config":{"visual":{},"stats":{"type":"light","hp":0,"defPhysics":0,"defMagic":0,"damagePhysics":0,"damageMagic":159,"damagePure":0,"damageHpLoss":0,"damageHpRemove":0},"farm":{"le":1209,"hours":240}}}]}

      Reply

    • Your filter doesn't look quite right, can you please try this instead?
      data[?config.stats.type=='fire'].{id:id,tokenId:tokenId,Price:startingPrice,Available:status,type:config.stats.type,LE:config.farm.le,Hours:config.farm.hours}

      Reply

  22. Hi

    Here's a sample of my JSON :
    [{"_id":103,"ankamaId":103,"name":"Anneau de Sagesse","level":9,"type":"Anneau","imgUrl":"https://s.ankama.com/www/static.ankama.com/dofus/www/game/items/200/9007.png","url":"https://www.dofus.com/fr/mmorpg/encyclopedie/equipements/103-anneau-sagesse","description":"Cet anneau renforce la sagesse de son porteur.","statistics":[{"Sagesse":{"min":7,"max":10}}],"recipe":[{"Os de Chafer":{"ankamaId":310,"url":"https://www.dofus-touch.com/fr/mmorpg/encyclopedie/ressources/310-os-chafer","imgUrl":"https://s.ankama.com/www/static.ankama.com/dofus/www/game/items/52/47015.w48h48.png","type":"Os","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

    Reply

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

      Reply

  24. 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"
    }
    }]

    Reply

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

    Reply

  26. 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'.....}}}

    Reply

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

      search_string = 'data'
      print(jmespath.search(search_string, data))

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

      Reply

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

      Reply

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

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

    Reply

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

      Reply

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

    Reply

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

      Reply

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

    Reply

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

      Reply

  31. Hello Ana!
    I would like to ask you a question, but I can't get the information I need.
    I am trying to get all the data of the members of my mailchimp that have a certain tag.

    My API response is this:
    {"members":[{"id":"c82718062df218aa996a236b24e71c53","email_address":"[email protected]","status":"subscribed","merge_fields":{"NOMBRE":"Name","APELLIDO":"Surname","DNI":""},"tags_count":2,"tags":[{"id":6456208,"name":"Confirmada"},{"id":6452736,"name":"Primer evento"}]}]}

    I want to obtain the name, surname and email of those people who have the tag name "CONFIRMADA".
    I'm trying this command, but it tells me that there are no results (yes there are)

    members.*.{"DNI":merge_fields.DNI, "EMAIL":email_address, "NOMBRE":merge_fields.NOMBRE, "APELLIDO":merge_fields.APELLIDO}|tags[?name == 'Confirmada']

    Can you help me figure out what I'm doing wrong?

    Thank you so much

    Reply

    • Hey Aida, I think the issue is that both members and tags are arrays, not objects. Please try this instead and let me know if it gets you what you're looking for: members[?tags[?name=='Confirmada']].{merge_fields:merge_fields.{DNI:DNI,NOMBRE:NOMBRE,APELLIDO:APELLIDO},EMAIL:email_address}

      Reply

      • It worked! Thank you so much Ana.
        Is there a possibility to count records? That is, I would like to make a table with the tags and tell me how many contacts have that tag.
        It's possible?
        For example:
        Confirmed 12

      • I don't believe JMESPath has this functionality, but you can do this with Sheets functions, e.g. =COUNTIF(A1:A,"your_tag") would count all the records containing your tag name.

  32. Hey Ana, maybe you can give me helping hand also. I have got the filter query working on the query tester, but it won't do in the api, returning that no records have been found. Same JSON at the (working) query tester. So, it absolutely has true records.

    Here's the JSON structure:
    {"rows":[{"keys":["some date","some keyword"],"clicks":9,"impressions":100,"ctr":1,"position":1},{"keys":["some date","some keyword"],"clicks":6,"impressions":100,"ctr":1,"position":1},{"keys":["some date","some keyword"],"clicks":6,"impressions":100,"ctr":1,"position":1},{"keys":["some date","some keyword"],"clicks":5,"impressions":100,"ctr":1,"position":1},{"keys":["some date","some keyword"],"clicks":5,"impressions":100,"ctr":1,"position":1}],"responseAggregationType":"byPage"}

    And the filter query very simple is: rows[?clicks > `8`] to return only rows (just for testing purposes here) with more than 8 clicks.

    In query tester, I'm retrieving the awaited row having 9 clicks. But in google sheets' api connector, the filter query does not work as it returns to not have found any matching records.

    Connection to search console is all fine. Any other request w/o JMESPAth also works great. So, it's about the filter query itself. Did I miss something?

    Cheers, Tom

    Reply

      • Hi Ana, that made the trick. You suspected totally right. Thank you 🙂

        btw. is there any chance to sort the returned records, say by date? Per default, it's sorted by number of impressions. If not, I'm gonna pull the records into an other sheet and do sorting over there.

        Thanks a million, Tom

      • Is this the Google Search Console API? (The results look like it but I'm not sure). If so, I don't believe that API provides a parameter for sorting natively. However I think we can again use JMESPath for this. Please try adjusting your expression to sort_by(rows, &keys[0])[?clicks > `8`] Let me know if that works 🙂

  33. Hi Ana,

    Using the quotes report I'd like to pull all elements in from 01/01/23 onwards. Can you please advise a JMESPath for pulling data in from the "Issue Date" field and setting from the start of 2023?

    Reply

    • I think it's better to filter at the source for this, since Xero provides filtering parameters. JMESPath filters after we get the data back, so it's a bit less efficient. I posted an example request URL here, please check.

      Reply

  34. Hi, I'm trying to get just two columns Date and LME-XCU from this response, is it possible? Thank you!

    {"success":true,"timeseries":true,"base":"USD","rates":{"2022-05-02":{"USD":1,"LME-XCU":3.2765526277684},"2022-05-03":{"USD":1,"LME-XCU":3.2763234592943},"2022-05-04":{"USD":1,"LME-XCU":3.3819250680265},"2022-05-05":{"USD":1,"LME-XCU":3.3939112381254}}}

    Reply

    • Great question, but unfortunately I don't think this is possible with JMESPath. The problem is that JSON keys become columns, while the values become rows. This JSON is structured such that each date is its own unique key, which produces a new column for each date. You could strip out that date key entirely with a JMESPath of rates.values(@), which would partially solve your issue by printing the output into columns, but then you'd lose the associated date information.
      In the future, we'll add an option to API Connector to automatically restructure the data such that the outer key gets pushed into the object like this: {"date":"2022-05-02","USD":1,"LME-XCU":3.2765526277684},{"date":"2022-05-03","USD":1,"LME-XCU":3.2763234592943}.... That should resolve this kind of issue, but for now I'm not sure of any immediate solution.

      Reply

  35. Hi Ana,
    First of all thank you this is amazing and super helpful.

    Secondly I have a question, I am trying to write a filter expression where the value used by the filter is the result of another expression.

    sample data
    {"container":{"objects":[{"id":"567","metadata":{"informationToBeRetrieved":"Bingo"}},{"id":"78","metadata":{}}]},"idToBeFiltered":"567"}

    this expression works like a charm
    container.objects[?id=='567'].metadata.informationToBeRetrieved
    but what I am looking for is to have the 567 dynamic based on the value corresponding to the key idToBeFiltered

    something like
    container.objects[?id==idToBeFiltered].metadata.informationToBeRetrieved
    which unfortunately does not work, I tried variations like

    container.objects[?id==join('',['`',idToBeFiltered,'`'])].metadata.informationToBeRetrieved

    but no luck, do you know if something like this is possible within JMESPath?

    Reply

    • Hey Mauro, thanks for the nice comment. Unfortunately, though, I don't think JMESPath allows variables in this way, instead you'd need to use a programming language that lets you define a variable and pass it into your JMESPath expression (you can see a similar question here, with an example solution provided using JS).

      Reply

  36. Hi, I'm trying to figure out how to get all data/fields in Freshdesk that match a condition in nested object, but the main condition is an array. Condition: All fields with Type: Event Planning if cf_done == 'true'.

    I'm able to get the data for the below code using [?type== 'Event Planning'] but the hyphens in the top level and nested objects are stumping me in setting up the query. Thanks in advance!

    [
    {
    "cc_emails": [],
    "fwd_emails": [],
    "reply_cc_emails": [],
    "ticket_cc_emails": [],
    "type": "Event Planning”,
    "custom_fields": {
    "cf_done": true,
    "cf_location": “NY”,
    "cf_point_of_contact": null
    },
    }
    ]

    Reply

  37. First of all you are amazing, and this tool is helping a lot of people saving time and work, more time to spend with the family and that's awesome!
    Congrats"
    And I have a simple question: I am downloading info from Ebay Api, but when i Get the tracking numbers they show as cientific notation, so that they are usless even if I try to convert them on google sheets, is there a way to avoid getting those numbers without the cientific notation?
    Let me know, thank you.

    Reply

    • Thank you, your comment made my day!
      Unfortunately, though, I'm not sure how to resolve the issue you're facing. Google Sheets automatically converts numbers longer than 15 digits to scientific notation. To get the actual tracking numbers you could download the original raw data, but that largely defeats the purpose of doing this in Sheets.

      Reply

  38. Please help, here is the data of getting from Xero:
    {"Id":"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx","Status":"OK","ProviderName":"API Client for Sheets by Mixed Analytics","DateTimeUTC":"/Date(1702428918992)/","Items":[{"ItemID":"de0b78aa-c9db-4db6-8d41-6158f79d017f","Code":"ALP-CN-B-10lbs","Description":"Alpha- Breaded Chick'n Nugget 10lbs-case","PurchaseDescription":"Alpha- Breaded Chick'n Nugget 10lbs-case","UpdatedDateUTC":"/Date(1672877322977+0000)/","PurchaseDetails":{"UnitPrice":48.1,"COGSAccountCode":"50005","TaxType":""},"SalesDetails":{"UnitPrice":60.15,"AccountCode":"40005","TaxType":"NONE"},"Name":"Alpha- Breaded Chick'n Nugget 10lbs","IsTrackedAsInventory":true,"InventoryAssetAccountCode":"14000","TotalCostPool":2087.93,"QuantityOnHand":44.5,"IsSold":true,"IsPurchased":true}]}

    But when i tried to do :
    rows[*].Items[].[?QuantityOnHand > 0]

    Tried to run, get error:
    1) ItemsAPI: Request failed: Data response not in JSON, XML, or CSV format

    Reply

    • The condition requires matching values within an object so the syntax should be like this: Items[].{ItemId:ItemId,Code:Code, Description:Description,PurchaseDescription:PurchaseDescription,UpdatedDateUTC:UpdatedDateUTC,PurchaseDetails:PurchaseDetails,SalesDetails:SalesDetails,Name:Name,IsTrackedAsInventory:IsTrackedAsInventory,InventoryAssetAccountCode:InventoryAssetAccountCode,TotalCostPool:TotalCostPool,QuantityOnHand:QuantityOnHand,IsSold:IsSold,IsPurchased:IsPurchased}|[?QuantityOnHand>'0']. However I suspect you can filter this directly at the source from Xero rather than using JMESPath, as most of their endpoints allow for adding a filter.

      Reply

    • If the field editor is showing something different from the report, the most likely explanation is you're running multiple requests and the first request in the list doesn't return any data, since the field editor only displays values from the first request. Can you please try removing the first request (or first account ID, if this is through the preset integration) from your list to see if that resolves the issue?

      Reply

  40. Hi,

    I want to only get results matching activiy ID "473257". What do I put in the JMSE Path to achived that?

    Preview:
    {
    "fullResultSize": 1063,
    "from": 0,
    "count": 1063,
    "versionDigest": "'If-None-Match' header not specified",
    "values": [
    {
    "id": 109159358,
    "version": 1,
    "url": "tripletex.no/v2/timesheet/entry/109159358",
    "project": {
    "id": 135116240,
    "url": "tripletex.no/v2/project/135116240"
    },
    "activity": {
    "id": 473257,
    "url": "tripletex.no/v2/activity/473257"
    },

    Thanks 🙂

    Reply

  41. Hello,
    Is there away to assign a default value? For example, if no result returned for x field, populate it with 0?
    Thanks
    Will

    Reply

    • There is not currently a way to assign a default value using only JMESPath filtering. You might be able to achieve the desired output by adding a column directly in your Google Sheet with a default value if the "x field" output column is blank.

      Reply

  42. Hello,
    I'm having a bit of difficulty splitting a string. I know I can do this using a formula in the resulting spreadsheet but I was hoping to be able to automate it.

    Here's an example extract:
    {
    "data": [
    {
    "name": "Chelsea vs Crystal Palace",
    "starting_at": "2024-09-01 13:30:00",
    "venue_id": 321614,
    "id": 19134475,
    "sport_id": 1,
    }

    I'm looking to split the name into two new columns. For example:

    Home: Chelsea
    Away: Crystal Palace

    Can anyone help me out with this or let me know if it's possible?

    Thanks

    Reply

      • Thanks Ian, that's a good suggestion.
        I have actually just found a way to do this in the end with a combination of JMES and saving formulas in Edit Fields mode. In case it helps anyone else, this is what I did.

        JSME:

        data[].{
        Fixture: name,
        Home: name,
        Away: name
        Time: starting_at
        }

        In Edit fields, I added the below formulas:
        Home: =LEFT("$1", FIND(" vs ", "$1") - 1)
        Away: =RIGHT("$1", LEN("$1") - FIND(" vs ", "$1") - 3)

        When this is run, it results in the below without the need of any further editing.

        Fixture: Chelsea vs Crystal Palace
        Home: Chelsea
        Away: Crystal Palace

  43. Hello,
    I have two arrays and want to join them via the User Id, so that i get a league table with User Names instead of just User Ids. How would a do the right JSME? Thanks a lot.

    It should be "userName":Bob, "dayEarnings":416000, ......

    {
    "currentDay": 3,
    "matchDays": [
    {
    "day": 3,
    "users": [
    {
    "userId": "1",
    "dayEarnings": 416000,
    "dayPoints": 416,
    "dayPlacement": 14,
    "dayTendency": 0,
    "teamValue": 147792417,
    "points": 1193,
    "placement": 13,
    "tendency": 2,
    "flags": 1
    }
    ]
    }
    ],
    "users": [
    {
    "id": "1",
    "name": "Bob"
    }
    ]
    }

    Reply

  44. Hi Toralf,

    Unfortunately this isn't possible with JMESpath. If you run two separate requests, one with "matchDays" as the JMES expression and the other with "users" as the JMES expression, you should be able to join the user name in Google Sheets with cell formulas. (You might be able to do it in a single request as well, but two separate requests in different tabs will make the output cleaner).

    Reply

Leave a Comment

Jump To