JMESPath query on combining search with ? and contains keyword in azure cloudshell bash cli

Question

I am trying to write an azure cli JMESPath query to output all names which contain the word db and is of osType windows.
For this I have written the following query which works by calling the external bash utility called grep.
But I am unable to get it done with filtering in JMESPath language built in function contains.
Here is a query that works

az vm list --query "[?storageProfile.osDisk.osType=='Windows'].[name]" -o tsv | grep db

Here is a query that I tried and fails to get results:

az vm list --query "[?storageProfile.osDisk.osType=='Windows'].[?contains(name,'db')]" -o tsv

Answer 1

You just have to use an and expression:

Given the query:

[?storageProfile.osDisk.osType=='Windows' && contains(name,'db')].name

On the JSON

[
  {
    "name": "db-of-windows-application",
    "storageProfile": {
      "osDisk": {
        "osType": "Windows"
      }
    }
  },
  {
    "name": "db-of-linux-application",
    "storageProfile": {
      "osDisk": {
        "osType": "Linux"
      }
    }
  },
  {
    "name": "I-am-not-the-database-you-are-looking-for",
    "storageProfile": {
      "osDisk": {
        "osType": "Windows"
      }
    }
  }
]

This would give:

[
  "db-of-windows-application"
]