How to use JMESPath?

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.

Tags:

Was this helpful?