When json value has null, knack in Azure CLI changes column order unintentionally.

[chekuchose]

Problem

When json value has "null", knack in Azure CLI unintentionally changes column order regardless of specifying order of the columns by JMESPath.

Reproduce steps

1. Prepare some json data including null value in member like this

$ az graph query -q 'Resources | where type == "microsoft.compute/virtualmachines" | project name, properties.storageProfile.dataDisks[0].name'
{
  "count": 3,
  "data": [
    {
      "name": "kvm-ubuntu1804Gen2-NoneDisk",
      "properties_storageProfile_dataDisks_0_name": null
    },
    {
      "name": "tkdsvm",
      "properties_storageProfile_dataDisks_0_name": null
    },
    {
      "name": "tkvm-ubuntu1804Gen2-AddDisk",
      "properties_storageProfile_dataDisks_0_name": "tkvm-ubuntu1804Gen2-AddDisk_DataDisk_0"
    }
  ],
  "skip_token": null,
  "total_records": 3
}

2. Run JMESPath query using --query option:

$ az graph query -q 'Resources | where type == "microsoft.compute/virtualmachines"'  --query 'data[].{Name: name, Location: location, Disk: properties.storageProfile.dataDisks[0].name, ResouceGroup: resourceGroup}' -o table
Name                         Location    ResouceGroup    Disk
---------------------------  ----------  --------------  --------------------------------------
kvm-ubuntu1804Gen2-NoneDisk  japaneast   vm-rg
tkvm-ubuntu1804Gen2-AddDisk  japaneast   vm-rg           tkvm-ubuntu1804Gen2-AddDisk_DataDisk_0
tkdsvm                       japaneast   dsvmrg

As the above --query result shows different order of columns regardless the query specifies "Name, Location, Disk, ResourceGroup" orders.

In my view

The behavior seems to change the result when the first record includes "null" value. If the first record has no "null" value, the result expectly shows according to --query order:

$ az graph query -q 'Resources | where type == "microsoft.compute/virtualmachines"'  --query 'data[].{Name: name, Location: location, Disk: properties.storageProfile.dataDisks[0].name, ResouceGroup: resourceGroup}' -o table --skip 1
Name                         Location    Disk                                    ResouceGroup
---------------------------  ----------  --------------------------------------  --------------
tkvm-ubuntu1804Gen2-AddDisk  japaneast   tkvm-ubuntu1804Gen2-AddDisk_DataDisk_0  vm-rg
tkdsvm                       japaneast                                           dsvmrg

This null value behavior seems to be caused by the following line:
knack/output.py at d407b81d451d36c6ca1baeb3cd067a3f1275cb85 · microsoft/knack

Request

Could you enhance/fix this behavior? We want you to show the same result whether the first record includes null value or not.

[jiasli]

This is a sharp observation. Those lines try to remove columns that contains None value, but the side effect is that it may unintentionally change column order.

knack/knack/output.py

Lines 187 to 188 in d407b81

	if item[k] is not None and not isinstance(item[k], (list, dict, set)):
	new_entry[_TableOutput._capitalize_first_char(k)] = item[k]

For kvm-ubuntu1804Gen2-NoneDisk, only Name, Location, ResouceGroup are preserved in new_entry, causing Disk to be moved to the last column.

There doesn't seem to be a simple fix. I will revisit this topic when time permits.