By default, Two Minute Report’s API Bridge displays the entire response from the API request. But, the returned data may contain unnecessary fields or the formatting may not be in a way that you’d expect. You can control what Data is returned into Google Sheets by using the JMESPath functionality in API Bridge.
JMESPath
JMESPath is a query language for transforming JSON data into the required format. By specifying selective fields through JMESPath you can
- overcome Google Sheets limits (5 million cells per Spreadsheet)
- keep reporting clean by formatting data in a defined format
JMESPath can be quickly learnt, and one can use its power to easily select the required fields from any JSON.
It follows a certain syntax, some of which are explained below
JSON Objects
The Basic JMESPath expression is an identifier, which is a key in a JSON object. In the JSON world, an object is something that is enclosed inside curly braces {}. The Basic JMESPath expression can be used to select elements from a JSON object.
Example 1
Original JSON Object:
{
"name": "Roberts",
"age": 25,
"profession": "teaching"
}
JMESPath:
name
Result:
"Roberts"
Here “name” is a key in the JSON object.
Example 2
In the same way, we can use an expression to extract objects inside a JSON
JSON Object:
{
"name": "Roberts",
"age": 25,
"profession": "teaching",
"skills": {
"primary-skill": "Cyber Security",
"secondary-skill": "Web Development"
}
}
JMESPath:
skills
Results:
{
"primary-skill": "Cyber Security",
"secondary-skill": "Web Development"
}
You can also use sub-expressions to retrieve nested data from objects. For the same above example, you can get the primary-skill
by giving skills.primary-skill
JMESPath:
skills.primary-skill
Results:
"Cyber Security"
JSON Lists/Arrays
You can select specific data from a list/array of objects by using a wildcard expression. In the JSON world, a list/array is expressed within square brackets [].
A wildcard expression is an expression of either * or [*]. It can return multiple elements.
Example 1
JSON Object:
{
"people": [
{"firstName": "James", "lastName": "d"},
{"firstName": "Jacob", "lastName": "e"},
{"firstName": "Jayden", "lastName": "f"},
{"missing": "different"}
],
"foo": {"bar": "baz"}
}
JMESPath:
people[*].firstName
Results:
[
"James",
"Jacob",
"Jayden"
]
In the above example, the people
is just an identifier. The wildcard expression [*] takes all the objects of the array people
and returns all the values of the property first
in those objects.
To learn more about the JMESPath, check the official documentation here.