JMESPATH queries
Understand the JMESPATH queries using this lab that explores some common uses.
Introduction
The --query switch is one of the “global” switches, i.e. it is available on every az command, and it enables you to query and filter the output of the command.
The Azure CLI uses the industry standard JMESPATH query format that is used not only by the CLI, but also the AWS CLI and other commands that need to manipulate JSON.
There is some excellent documentation on JMESPATH at the official site, and it covers the full range of what can be accomplished. This guide will give you a shortcut into the commonly used functionality when querying Azure JSON output.
JSON Format
Here is some example JSON output from an az resource list --resource-group <resourceGroup> --output json command. The example resource group below(myAppRG-Staging) contains a single Web App in standard app service plan.
[
{
"id": "/subscriptions/2ca40be1-7680-4f2b-92f7-06b2123a68cc/resourceGroups/myAppRG-Staging/providers/Microsoft.Web/serverFarms/MyAppServicePlan",
"identity": null,
"kind": "app",
"location": "westeurope",
"managedBy": null,
"name": "MyAppServicePlan",
"plan": null,
"properties": null,
"resourceGroup": "myAppRG-Staging",
"sku": null,
"tags": {
"displayName": "myAppServicePlan"
},
"type": "Microsoft.Web/serverFarms"
},
{
"id": "/subscriptions/2ca40be1-7680-4f2b-92f7-06b2123a68cc/resourceGroups/myAppRG-Staging/providers/Microsoft.Web/sites/MyWebApp-richeney-Staging",
"identity": null,
"kind": "app",
"location": "westeurope",
"managedBy": null,
"name": "MyWebApp-richeney-Staging",
"plan": null,
"properties": null,
"resourceGroup": "myAppRG-Staging",
"sku": null,
"tags": {
"displayName": "myWebApp",
"hidden-related:/subscriptions/2ca40be1-7e80-4f2b-92f7-06b2123a68cc/resourceGroups/myAppRG-Staging/providers/Microsoft.Web/serverfarms/MyAppServicePlan": "Resource"
},
"type": "Microsoft.Web/sites"
}
]
Whilst it may initially look complex, there are only keys, values, and structures.
The structures are split into two types:
- Arrays, denoted by square brackets
- Arrays in JSON are ordered lists of value
- Also known as lists or vectors
- In Azure that order depends on the context. It may be based on order, such as with VM NICs, or alphabetically by name, i.e. when listing resource groups
- Objects, denoted by curly brackets
- Objects are collections of name:value pairs
- Also known as keyed lists, dictionaries or hashes
When arrays and objects contain multiple elements then those elements are separated by commas.
Values may be:
- string
- number
- true
- false
- null
- object
- array
JSON also supports nested objects and arrays nicely. The elements of a list may simply be unkeyed values (e.g. [‘red’, ‘white’, ‘blue’]), or it may be another structure, i.e. a nested array or object. You can see this in the example above.
Note that JSON files do not need to be indented to preserve the structure in the way that a YAML file or a Python script do. However if it is likely to be read by humans then it is common practice to indent the output to reflect the nested levels. This is also known as pretty printing.
Selecting Array Elements
Rather than reporting a whole array, it is possible to pull out a subset.
Each of the commands below follow az resource list --resource-group myAppRG-Staging --query '<query>' --output json format. Only the query switch will be shown in the table below for the sake of brevity.
| Query | Output |
|---|---|
--query '[*]' |
Pass through the whole array |
--query '[]' |
Flatten the array |
--query '[0]' |
First entity |
--query '[-1]' |
Last entity |
--query '[a:b]' |
Array slice from a to b-1 |
If you omit either the a or b value from a slice then the slice array goes to the start or end, e.g. '[:2]' gives the first two elements (i.e. 0 and 1), whereas '[2:]' will give all remaining elements from a list, from element 2 onwards.
Compare '[0]' and '[:1]' to see a subtle difference. The former is the object that is the first array element, and therefore starts with curly brackets, whereas the second is an array slice, containing only that same first element, i.e. it still has square brackets surrounding it.
You can also step through an array using the '[a🅱️c]' notation, but there are few valid reasons where that makes sense in Azure. Perhaps in slicing the odd and even NICs for a VM with multiple NICs. One additional use is to reverse an array using '[::-1]'.
You can also slice based on querying the data - see below.
You’ll initially see little difference in the '[*]' and '[]' queries, but array flattening will make more sense when we shift to filtering at deeper levels.
Selecting Object Values
You can also be selective on the name:value objects.
Querying on the name for a name:value pair is trivial and simply state the name. Use the az account show --query '<query>' --output json command to show your active Azure subscription.
| Query | Output |
|---|---|
--query 'name' |
Cosmetic name for the subscription |
--query 'id' |
The ID for the subscription |
--query 'user' |
The user object for the subscription |
--query 'user.name' |
The value for name within the user object |
The last example above shows a nested value, pulling the value of name in the user object.
When pulling out individual values, the tsv output format will omit the braces and quotes, making it simple to read into a variable. For example:
username=$(az account show --query 'user.name' --output tsv)
echo $username
Selective filtering
This is very useful for outputting selected JSON or TSV for scripting purposes, or for being selective on which columns to show in a table. It is easiest to demonstrate this by working through an example.
List the VMs in one of your resource groups, using az vm list --resource-group <resourceGroup> --output table.
The table should show all of the VMs, with the name of each server, the resource group and the location, which is of limited use.
If you run the same command with --output json then you will see significantly more information. If you run az vm list --help then you’ll find there is a --show-details switch. It is a little slow but has some additional information which is even more useful.
Capture the detailed information into a file using az vm list --resource-group <resourceGroup> --show-details --output json > vms.json.
If you have Visual Studio Code installed then you can type code vms.json to open it up in vscode. (Or just code . to open the current directory.)
Examine the JSON output to determine the desired information. In this example we want to pull only the values for the VM name, size, OS, private and public IP addresses, FQDN and current running state. Working through the nesting, the query should become something like this:
az vm list --resource-group <resourceGroup> --show-details --output table --query "[*].[name, hardwareProfile.vmSize, storageProfile.osDisk.osType, privateIps, publicIps, fqdns, powerState]"
This is known as a multi-select, and provides a far more useful table. Output this as JSON and you will see that it is an array of arrays, which is why the column headings are Column1-7.
If the query is tweaked to provide an array of objects then we can control the naming:
az vm list --resource-group <resourceGroup> --show-details --output json --query "[*].{VM:name, Size:hardwareProfile.vmSize, OS:storageProfile.osDisk.osType, IP:privateIps, PIP:publicIps, FQDN:fqdns, State:powerState}"
Note that we have a) changed the second level from square brackets to curly, and b) we have defined our own keys. Rerun the command, outputting to a table and note the column headers. (Also note that in the JSON the keys were sorted alphabetically, whilst the tables preserved our selected order.)
One other thing you will have noticed is that it is very easy to end up with some very long queries. If scripting then it is recommended to use a $query variable for readability, and to support dynamic queries built up from variables. For example:
query='[*].{VM:name, Size:hardwareProfile.vmSize, OS:storageProfile.osDisk.osType, IP:privateIps, PIP:publicIps, FQDN:fqdns, State:powerState}'
az vm list --resource-group <resourceGroup> --show-details --output table --query "$query"
Filter Projections
Selecting all elements in in array, or the first or last, is useful. But often you will want to select based on other criteria. Rather than using [*], [0], or [-1], we can use a filter projection base on testing the values, using [?name == 'value']. These will always produce array slices.
Here are some examples. I have assumed that you are using the az configure defaults to limit to a specific resource group just to shorten the commands:
az vm list --output json --query "[?location == 'westeurope']"
az vm list --output json --query "[?storageProfile.osDisk.osType == 'Linux']"
az vm list --output tsv --query "[?name == 'vmName'].id"
Let’s review each of those in order:
- Array slice, with all entities where the region is West Europe
- Another array slice, delving a little further down into the JSON to pull out (at the top level), those entities that have a Linux OS
- Testing on one value (the VM’s name) to output only the ID
The last version is commonly seen when pulling information to be used as variables in scripts. More on that in the next post.
Compound tests can also be created, using && for a logical AND, and || for a logical OR. For example --query "[?tags.env == 'test' || tags.env == 'dev'].
Filtering on boolean values is simple. All of the filtering examples above resolve to either boolean true or false, and the same is true when using the boolean values themselves.
For example, here is the command to generate a table listing out the objectIds in the Azure AD directory which have the securityEnabled: true boolean set:
az ad group list --output table --query "[?securityEnabled].{name:displayName, description:description, objectId:objectId}"
Note the [?securityEnabled]. (You could also convert booleans to strings, e.g. [? to_string(securityEnabled) == 'true'].)
Resulting table:
Name Description ObjectId
---------------------- ----------------------------------------------- ------------------------------------
Databricks Admins Enable Databricks workspace via portal as admin 3ba57833-991b-40a8-8ce8-895a34464ebf
RBAC Admins Allowed to create and assign roles 3defc448-c5f8-4dd0-addd-c94ea54341d3
Network Admins Admins for the shared services 4a1451a1-de76-45e5-ac80-e9276541c96b
Key Vault Secrets Those with access to update Key Vault secrets 74fa1c03-aeeb-422e-bab3-796575407e9c
Virtual Machine Admins Admins for the Virtual Machines 88515d1f-e386-4a23-afcc-79b0124805f9
If you wanted to invert the result then you would use [?!(securityEnabled)] rather than [?securityEnabled] within the query.
Pipes
As in Bash, we can use pipes in our JMESPATH queries to get to the desired point. As a simple example, compare the following:
az vm list --output tsv --query "[?name == 'vmName']"
az vm list --output tsv --query "[?name == 'vmName']|[0]"
The VM name should be unique within that subscription and resource group, so the first will provide the array slice containing only one element. In the second command we pull out just that first element, so the JSON output from that command will have stripped out the surrounding square braces.
Pipes are very useful when combining filters with multi-selects, and also the functions shown below.
Sorting
The syntax for sorting output can be a little counterintuitive. This section will use a couple of commands I used when generating custom RBAC roles.
Let’s take the simple form first, piping a filtered array with just one set of values through to a straight sort:
az provider operation show --namespace Microsoft.HybridCompute --query "resourceTypes[].operations[?!(isDataAction)][].name | sort(@)" --output tsv
Note the | sort(@) at the end of the query.
Example output:
Microsoft.HybridCompute/locations/operationresults/read
Microsoft.HybridCompute/locations/operationstatus/read
Microsoft.HybridCompute/locations/privateLinkScopes/read
Microsoft.HybridCompute/locations/updateCenterOperationResults/read
Microsoft.HybridCompute/machines/UpgradeExtensions/action
Microsoft.HybridCompute/machines/assessPatches/action
Microsoft.HybridCompute/machines/delete
Microsoft.HybridCompute/machines/extensions/delete
Microsoft.HybridCompute/machines/extensions/read
Microsoft.HybridCompute/machines/extensions/write
Microsoft.HybridCompute/machines/hybridIdentityMetadata/read
Microsoft.HybridCompute/machines/installPatches/action
Microsoft.HybridCompute/machines/patchAssessmentResults/read
Microsoft.HybridCompute/machines/patchAssessmentResults/softwarePatches/read
Microsoft.HybridCompute/machines/patchInstallationResults/read
Microsoft.HybridCompute/machines/patchInstallationResults/softwarePatches/read
Microsoft.HybridCompute/machines/read
Microsoft.HybridCompute/machines/write
Microsoft.HybridCompute/operations/read
Microsoft.HybridCompute/osType/agentVersions/latest/read
Microsoft.HybridCompute/osType/agentVersions/read
Microsoft.HybridCompute/privateLinkScopes/delete
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnectionProxies/delete
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnectionProxies/read
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnectionProxies/updatePrivateEndpointProperties/action
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnectionProxies/validate/action
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnectionProxies/write
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnections/delete
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnections/read
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnections/write
Microsoft.HybridCompute/privateLinkScopes/read
Microsoft.HybridCompute/privateLinkScopes/write
What if you are using objects? Then you use sort_by instead. Here is an example, sorting an object with two fields, name and dataAction:
az provider operation show --namespace Microsoft.HybridCompute --query "sort_by(resourceTypes[].operations[].{action:name, dataAction:isDataAction}, &action) --output table
The sort_by takes two functions. The first is the JSON to be sorted. The second is the expression for the field used for the sorting. The is prepended with an ampersand so that it is evaluated later.
You can also use it with pipes. For instance, if you wanted to take the previous command and also sort by the dataAction (which is a boolean) then extend the query with | sort_by(@, &to_string(dataAction)). The first field is @, which is a placeholder for the whole JSON passed through the pipe. The second field is again prepended with the ampersand, and then sorts by dataAction. (You can only sort by strings and numbers, so this additional converts the type from boolean to string before sorting.) Full command:
az provider operation show --namespace Microsoft.HybridCompute --query "sort_by(resourceTypes[].operations[].{action:name, dataAction:isDataAction}, &action)|sort_by(@, &to_string(dataAction))" --output table
Example output:
Action DataAction
----------------------------------------------------------------------------------------------------------------- ------------
Microsoft.HybridCompute/locations/operationresults/read False
Microsoft.HybridCompute/locations/operationstatus/read False
Microsoft.HybridCompute/locations/privateLinkScopes/read False
Microsoft.HybridCompute/locations/updateCenterOperationResults/read False
Microsoft.HybridCompute/machines/UpgradeExtensions/action False
Microsoft.HybridCompute/machines/assessPatches/action False
Microsoft.HybridCompute/machines/delete False
Microsoft.HybridCompute/machines/extensions/delete False
Microsoft.HybridCompute/machines/extensions/read False
Microsoft.HybridCompute/machines/extensions/write False
Microsoft.HybridCompute/machines/hybridIdentityMetadata/read False
Microsoft.HybridCompute/machines/installPatches/action False
Microsoft.HybridCompute/machines/patchAssessmentResults/read False
Microsoft.HybridCompute/machines/patchAssessmentResults/softwarePatches/read False
Microsoft.HybridCompute/machines/patchInstallationResults/read False
Microsoft.HybridCompute/machines/patchInstallationResults/softwarePatches/read False
Microsoft.HybridCompute/machines/read False
Microsoft.HybridCompute/machines/write False
Microsoft.HybridCompute/operations/read False
Microsoft.HybridCompute/osType/agentVersions/latest/read False
Microsoft.HybridCompute/osType/agentVersions/read False
Microsoft.HybridCompute/privateLinkScopes/delete False
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnectionProxies/delete False
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnectionProxies/read False
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnectionProxies/updatePrivateEndpointProperties/action False
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnectionProxies/validate/action False
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnectionProxies/write False
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnections/delete False
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnections/read False
Microsoft.HybridCompute/privateLinkScopes/privateEndpointConnections/write False
Microsoft.HybridCompute/privateLinkScopes/read False
Microsoft.HybridCompute/privateLinkScopes/write False
Microsoft.HybridCompute/locations/publishers/extensionTypes/versions/read True
Microsoft.HybridCompute/machines/WACloginAsAdmin/action True
Microsoft.HybridCompute/machines/login/action True
Microsoft.HybridCompute/machines/loginAsAdmin/action True
Additional Functions
There are a whole host of functions that can be very powerful. Here are a few examples:
-
length
Returns the number of elements in an array or array slice.
Number of Linux VMs in the resource group:
query="length([?storageProfile.osDisk.osType == 'Linux'])" az vm list --output tsv --query "$query" -
contains, starts_with, ends_with
Useful for filtering arrays when matching portions of a string value.
Array of VMs that have LRS storage for the OS disk:
query="[?ends_with(storageProfile.osDisk.managedDisk.storageAccountType, 'LRS')]" az vm list --show-details --output json --query "$query"The
containfunction will also returns true if tested against an array if the search value matches on of the elements. -
min, max, min_by, max_by, sort_by, sort, reverse
Array of VMs sorted by the OS disk size, largest first:
query="reverse(sort_by([], &storageProfile.osDisk.diskSizeGb))" az vm list --output json --query "$query" -
to_array, to_string, to_number
Use these to force the output of an expression to fit a certain data type.
List of VM SKUs that support Encryption at Host:
query="[?to_string(capabilities[?name == 'EncryptionAtHostSupported'].value) == '[\"True\"]'].name" az vm list-skus --location uksouth --resource-type virtualMachines --query "$query" --output jsoncNote that certain conversion functions such as to_boolean() are not currently available in the version of JMESPATH used by the Azure CLI. This is a workaround.
In the next section we will have some example integrations with Bash scripting.
Help us improve
Azure Citadel is a community site built on GitHub, please contribute and send a pull request
Make a change