We continue with our post on Azure CLI and JMESPath. In our previous post, we looked at basic expressions, slicing, list projections, slice projections and object projections.

More than one projection can be used in a JMESPath expression.

Consider

{
  "reservations": [
    {
      "instances": [
        {"state": "running"},
        {"state": "stopped"}
      ]
    },
    {
      "instances": [
        {"state": "terminated"},
        {"state": "running"}
      ]
    }
  ]
}

We can do

reservations[*].instances[*].state => [
  [
    "running",
    "stopped"
  ],
  [
    "terminated",
    "running"
  ]
]

//the same result as reservations[:2].instances[*]

This expression is saying that the top level key reservations has an array as a value. 

reservations[*] =>[
  {
    "instances": [
      {
        "state": "running"
      },
      {
        "state": "stopped"
      }
    ]
  },
  {
    "instances": [
      {
        "state": "terminated"
      },
      {
        "state": "runnning"
      }
    ]
  }
]

For each of those array elements, project the instances[*].state expression. Within each list element, there’s an instances key which itself is a value, and we create a sub projection for each each list element in the list.

reservations[*].instances[*] => [
  [
    {
      "state": "running"
    },
    {
      "state": "stopped"
    }
  ],
  [
    {
      "state": "terminated"
    },
    {
      "state": "runnning"
    }
  ]
]

We then project .state over the each sub list

What if we just want a list of all the states of our instances? We’d ideally like a result [“running”, “stopped”, “terminated”, “running”]. This is where flatten projections become useful.

Flatten Projections

The Flatten projection uses the flatten operator, [], to flatten sublists into the parent list (not recursively, just one level).

Take

[
  [0, 1],
  2,
  [3],
  4,
  [5, [6, 7]]
]

The expression [ ] will result in

[  0,   1,   2,   3,   4,   5,   [ 6,  7 ] ]

The sublists have been flatten into the parent list. This creates a projection, so anything on the RHS of the flatten projection can be projected onto the newly created flattened list.

[].[] => [  0,   1,   2,   3,   4,   5,  6,  7 ]

The expression reservations[ ] will not change the reservation JSON document much (since it does not have direct sublist), but

reservations[*].instances[] => [
  {
    "state": "running"
  },
  {
    "state": "stopped"
  },
  {
    "state": "terminated"
  },
  {
    "state": "running"
  }
]

Which returns a projection list, and with the expression .state, we get

[
  "running",
  "stopped",
  "terminated",
  "running"
]

Filter Expressions

A filter expression allows you to filter the LHS of the projection before evaluating the RHS of a projection.

This is comparable to the pseudocode, finding all machines that has a state of running

result = []
foreach machine in inputData['machines']
  if machine['state'] == 'running'
    result.insert_at_end(machine['name'])
return result

The JMESPath equivalent is

machines[?state=='running'].name

Given a JSON document

{
  "machines": [
    {"name": "a", "state": "running"},
    {"name": "b", "state": "stopped"},
    {"name": "b", "state": "running"}
  ]
}

the result is

[
  "a",
  "b"
]

JMESPath offers the standard comparison and logical operators. These include <, <=, >, >=, ==, and !=. JMESPath also supports logical and (&&), or (||), and not (!). 

The following Azure CLI command

az vm list -g demo --query "[?storageProfile.osDisk.osType=='Linux'].{Name:name,  admin:osProfile.adminUsername}" --output table

will produce

Name    Admin
------  ---------
Test-2  sttramer
TestVM  azureuser

Pipe Expressions

Projections always return an array. The Pipe expression is useful when you want to operate on the result of a projection rather than projecting an expression onto each element in the array.

From our earlier people JSON document example,

{
  "people": [
    {"name": "James", "age": 30,"state": {"name": "up"}},
    {"name": "Jacob", "age": 40,"state": {"name": "down"}},
    {"name": "Jayden", "age": 50,"state": {"name": "down"}},
    {"missing": "different"}
  ],
  "foo": {"bar": "baz"}
}

we can use the pipe expression to get the name of the person at index 0 of our projected list

people[*].name | [0] => "James"

MultiSelect List & MultSelect Hashes

Up to this point, we have been able to select a property from our projected list using the RHS expression.

MultiSelect allows us to create JSON elements that don’t exist in a JSON document. A multiselect list creates a list and a multiselect hash creates a JSON object.

MultiSelect List

Up to this point, we have been able to select a property from our projected list using the RHS expression.

To get more than one property, we put the RHS expressions in square brackets [ ] (a multiselect list) as a comma-separated list. 

The expression people[].[name,state.name] will produce

[
  [
    "James",
    "up"
  ],
  [
    "Jacob",
    "down"
  ],
  [
    "Jayden",
    "up"
  ]
]

With our Azure CLI comand, we can get the VM name, admin user, and SSH key all at once using the command: 

az vm show -g demo -n text-vm --query '[name, osProfile.adminUsername, osProfile.linuxConfiguration.ssh.publicKeys[0].keyData]' -o json
[
  "test-vm",
  "azureuser",
  "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDMobZNJTqgjWn/IB5xlilvE4Y+BMYpqkDnGRUcA0g9BYPgrGSQquCES37v2e3JmpfDPHFsaR+CPKlVr2GoVJMMHeRcMJhj50ZWq0hAnkJBhlZVWy8S7dwdGAqPyPmWM2iJDCVMVrLITAJCno47O4Ees7RCH6ku7kU86b1NOanvrNwqTHr14wtnLhgZ0gQ5GV1oLWvMEVg1YFMIgPRkTsSQKWCG5lLqQ45aU/4NMJoUxGyJTL9i8YxMavaB1Z2npfTQDQo9+womZ7SXzHaIWC858gWNl9e5UFyHDnTEDc14hKkf1CqnGJVcCJkmSfmrrHk/CkmF0ZT3whTHO1DhJTtV stramer@contoso"
]

MultiSelect Hashes

To get a dictionary instead of an array when querying for multiple values, use the { } (multiselect hash) operator. 

From our previous example,

people[].{Name: name, State: state.name}

will produce

[
  {
    "Name": "James",
    "State": "up"
  },
  {
    "Name": "Jacob",
    "State": "down"
  },
  {
    "Name": "Jayden",
    "State": "up"
  }
]

With our Azure JSON document, the following command

az vm list -g demo --query "[].{Name:name, Storage:storageProfile.osDisk.managedDisk.storageAccountType}" -o json

will give us

[
  {
    "Name": "test-vm",
    "Storage": "StandardSSD_LRS"
  },
  {
    "Name": "WinTest",
    "Storage": "StandardSSD_LRS"
  }
]

Because a multselect hash produces a a list of JSON object element, we can use a filter expression on the results.

az vm list -g demo --query "[].{Name:name, Storage:storageProfile.osDisk.managedDisk.storageAccountType}[?Name =='test-vm']" -o json
[
  {
    "Name": "test-vm",
    "Storage": "StandardSSD_LRS"
  }
]

Functions

JMESPath supports function expressions, for example:

length(people) => 3

Functions can be used to;

Transform Data

Functions can be used to transform data on the LHS. The following example prints the name of the oldest person in the people array: 

max_by(people, &age).name => "a"

The following Azure CLI command

az vm list -g demo --query "sort_by([].{Name:name, Size:storageProfile.osDisk.diskSizeGb}, &Size)" --output table

will result

Name     Size
-------  ------
TestVM   30
Test-2   32
WinTest  127

Filter expressions

Functions can also be combined with filter expressions.  

myarray[?contains(@, 'foo')] //finds all elements in myarray that contains the string foo.

myarray[?!contains(@, 'foo')] //finds all elements in myarray that does not contains the string foo.

The @ symbol, called current-node token can be used to represent the current node being evaluated

The following Azure CLI command

az vm list -g demo --query "[?contains(storageProfile.osDisk.managedDisk.storageAccountType,'SSD')].{Name:name, Storage:storageProfile.osDisk.managedDisk.storageAccountType}" -o json

outputs

[
  {
    "Name": "TestVM",
    "Storage": "StandardSSD_LRS"
  },
  {
    "Name": "WinTest",
    "Storage": "StandardSSD_LRS"
  }
]

In MultiSelect

people[].[length(@), name]
//equivalent to
people[].[length(@), @.name]

The expression creates an array containing the total number of elements in each people object followed by the value of name.

[
  [
    3,
    "James"
  ],
  [
    3,
    "Jacob"
  ],
  [
    3,
    "Jayden"
  ],
  [
    1,
    null
  ]
]

Other posts in this series

References:

http://jmespath.org/tutorial.html

https://docs.microsoft.com/en-us/cli/azure/query-azure-cli?view=azure-cli-latest