In this short tutorial we’ll go over the basics of filtering the results.
Let’s continue the How-To Demo and use example from there. After a simple request from http://api.open-notify.org/iss-now.json we had some data imported into our spreadsheet that looked like this:
If we only need the “latitude” and “longitude”, the rest is redundant data. In many cases the API response may not even be properly formatted into nice looking table because of the nesting format of the different fields in the response. Filtering will definitely help to bring only the data we require.
The Path Filter
The most basic filtering we can apply is the “Path filter” from the Advanced section of the Add-On Dashboard. Let’s apply this filter by creating again a new request, with the url http://api.open-notify.org/iss-now.json and filtering specifically by the latitude and longitude.
To get the actual header names, first check the “Raw Headers” checkbox and test your request. This will pull the following data into your spreadsheet.
We can now see the actual header names, with their full path(not the pretty version). To filter for only the fields that we want, we have to write their full path, comma separated into the “Path Filter”. Try this out with the following value: /iss_position/latitude,/iss_position/longitude
Click Test again. The new results should contain only the filtered headers.
The JMESPath Filter
The JMESPath filter is the second way of filtering our results, much more powerful, but also a bit more hard to use. A full tutorial and examples for the JMESPath query syntax can be found at https://jmespath.org/tutorial.html.
The general syntax for JMESPath queries is like this:
For Objects:
rootOject.{desired_field_name1: path_to_value, desired_field_name2: path_to_value}
For Arrays:
rootArray[*].{desired_field_name1: path_to_value, desired_field_name2: path_to_value}
For any sub-array / sub-object, we would again use sub_array_name[*].field / sub_object_name.field.
The same filtering as above can be achieved with the following JMESPath syntax: iss_position.{Latitude: latitude, Longitude: longitude}
The JMESPath syntax is quite complex and allows a lot more that simple path filtering. Feel free to check the tutorials on https://jmespath.org/tutorial.html and test your own API response with the JMESPath expression to get the best results.