JMESPath查询：获取最早部署的已解除分配Azure VMSS实例ID

需求概述 #

筛选出状态为PowerState/deallocated的Azure VMSS实例，再按ProvisioningState/succeeded对应的时间升序排序，取最早完成部署的实例ID。

示例数据 #

[
  {
    "instanceId": "5",
    "instanceView": {      
      "statuses": [
        {
          "code": "ProvisioningState/succeeded",
          "time": "2023-10-24T14:18:08.8438814+00:00"
        },
        {
          "code": "PowerState/deallocated"
        }
      ]
    }
  },
  {
    "instanceId": "13",
    "instanceView": {
      "statuses": [
        {
          "code": "ProvisioningState/succeeded",
          "time": "2023-10-24T15:53:59.6296842+00:00"
        },
        {
          "code": "PowerState/running"
        }
      ]
    }
  }
]

正确JMESPath查询 #

sort_by(
  [?instanceView.statuses[?code=='PowerState/deallocated']],
  &instanceView.statuses[?code=='ProvisioningState/succeeded'].time[]
)[0].instanceId

查询拆解 #

1. 筛选已解除分配的实例：[?instanceView.statuses[?code=='PowerState/deallocated']] —— 遍历所有实例，只保留包含PowerState/deallocated状态的实例
2. 按部署时间排序：sort_by(..., &instanceView.statuses[?code=='ProvisioningState/succeeded'].time[]) —— 对筛选后的实例，按ProvisioningState/succeeded对应的时间升序排列
3. 取最早实例的ID：[0].instanceId —— 排序后取第一个元素的instanceId

失败尝试分析 #

• 尝试1：sort_by([].{InstanceID: instanceId, Time: instanceView.statuses[0].time}, &Time)[0].InstanceID
  问题：未做状态筛选，会包含所有实例，不符合需求
• 尝试2：[*].instanceView.statuses[?code=='PowerState/deallocated']
  问题：只提取了状态数据，未关联对应的实例ID，无法得到目标结果
• 尝试3：[].instanceView.statuses[?code=='PowerState/deallocated'][].{instanceId: instanceId ,status:code}
  问题：在嵌套的statuses数组上下文中，无法直接访问上层的instanceId，导致投影结果为null

内容的提问来源于stack exchange，提问作者Yeo