18

I want to filter the output from the Azure CloudShell command az ad sp list which outputs a JSON array, eg by filtering to Publisher Name = "ACME". All az commands support a --query argument which accepts a JMESPath expression.

I have a JMESPath filter:

az ad sp list --query "[?contains(publisherName,'ACME')]" --all

that fails with error:

In function contains(), invalid type for value: None, expected one of: ['array', 'string'], received: "null"

I'm confident of my JMESPath syntax since a very similar expression works correctly:

az ad sp list --query "[?contains(displayName,'ACME')]" --all

I have a null filter that works fine:

az ad sp list --query "[?publisherName!='null']" --all

But when I combine the null filter with the contains filter I still get the error:

az ad sp list --query "[?publisherName!='null' && contains(publisherName,'ACME')]" --all

I'm guessing that JMESPath filter doesn't support boolean operations short circuit. However I didn't find any statement about that on http://jmespath.org/ or by googling.

I don't know how to daisy chain or pipe with the Azure az command --query clause to apply the two filters separately.

Any suggestion how to achieve my filtered output?

dreftymac
  • 31,404
  • 26
  • 119
  • 182
JohnC
  • 1,797
  • 1
  • 18
  • 26
  • The error indicates you have a problem with your jmespath query syntax. It says "invalid type for value" in the contains() function. – LHWizard Mar 26 '19 at 15:31

3 Answers3

15

Context

  • MSFT azure cloud shell console (as of 2019-11-17)
  • azure cloud shell az commands with jmespath query
  • jmespath handling of potentially-null values in a filter-expression

Use-case

  • UserJohnCDeveloper wants to run a JMESPath filter on attributes that may be null
  • how to daisy chain or pipe with the Azure az command --query clause

Solution

  • Jmespath support for pipe expressions
  • Jmespath supports passing the result of one expression to another, though use of pipe expressions. This enables queries of arbitrary complexity through chaining together of multiple sub-expressions and filters.

Example

 --query "[? publisherName!=null]|[? contains(publisherName,'ACME')]" --all
dreftymac
  • 31,404
  • 26
  • 119
  • 182
  • 2
    There is a syntax error above - the `null` value must be without the quotes like this: `--query "[? publisherName!=null]|[? contains(publisherName,'ACME')][].appDisplayName" --all` – Emil Apr 06 '22 at 12:22
11

The not_null function comes handy. It keeps the intent of the query more readable.

az ad sp list --query "[?contains(not_null(publisherName,''),'ACME')]" --all

The function returns the first non-null argument. This is often called "coalesce" elsewhere.

Palec
  • 12,743
  • 8
  • 69
  • 138
-2

For those looking for a more general approach, use || to create an or-expression:

expression || expression

Ref jmespath.org

t.h3ads
  • 1,858
  • 9
  • 17