将含多版本包信息的VM JSON转换为CSV，取最新安装日期版本

需求说明 #

• 若包存在多版本，依据installdate字段选取数值最大的最新版本
• 部分包的多版本信息存储在嵌套的versions数组中，主节点信息未必对应最新版本
• 无多版本的包直接保留原信息
• 输出CSV需保留两级父级：主机名（如host1）、包标识（如packages.kernel）

示例输入JSON #

{
  "host1": {
    "packages.kernel": {
      "epoch": "0",
      "version": "3.10.0",
      "release": "1160.102.1.0.1.el7",
      "installdate": "1698822019",
      "arch": "x86_64",
      "versions": [
        {
          "epoch": "0",
          "version": "3.10.0",
          "release": "1160.66.1.el7",
          "installdate": "1654792248",
          "arch": "x86_64"
        },
        {
          "epoch": "0",
          "version": "3.10.0",
          "release": "1160.49.1.el7",
          "installdate": "1640181036",
          "arch": "x86_64"
        },
        {
          "epoch": "0",
          "version": "3.10.0",
          "release": "1160.102.1.0.1.el7",
          "installdate": "1698822019",
          "arch": "x86_64"
        }
      ]
    },
    "packages.yum": {
      "epoch": "0",
      "version": "3.4.3",
      "release": "168.0.5.el7",
      "installdate": "1698822027",
      "arch": "noarch"
    }
  }
}
{
  "host2": {
    "packages.kernel": {
      "epoch": "0",
      "version": "3.10.0",
      "release": "1160.49.1.el7",
      "installdate": "1640181036",
      "arch": "x86_64",
      "versions": [
        {
          "epoch": "0",
          "version": "3.10.0",
          "release": "1160.102.1.0.1.el7",
          "installdate": "1698820106",
          "arch": "x86_64"
        },
        {
          "epoch": "0",
          "version": "3.10.0",
          "release": "1160.66.1.el7",
          "installdate": "1654792238",
          "arch": "x86_64"
        },
        {
          "epoch": "0",
          "version": "3.10.0",
          "release": "1160.49.1.el7",
          "installdate": "1640181036",
          "arch": "x86_64"
        }
      ]
    },
    "packages.yum": {
      "epoch": "0",
      "version": "3.4.3",
      "release": "168.0.5.el7",
      "installdate": "1698820115",
      "arch": "noarch"
    }
  }
}

预期CSV输出 #

host1, packages.kernel, x86_64, 0, 1698822019, 3.10.0, 1160.102.1.0.1.el7
host1, packages.yum, noarch, 0, 1698822027, 3.4.3, 168.0.5.el7
host2, packages.kernel, x86_64, 0, 1698820106, 3.10.0, 1160.102.1.0.1.el7
host2, packages.yum, noarch, 0, 1698820115, 3.4.3, 168.0.5.el7

实现脚本（基于jq工具） #

jq -s '
  .[] | to_entries[] | 
  .key as $host | 
  .value | to_entries[] | 
  .key as $pkg | 
  .value | 
  (if .versions then .versions + [.] else [.] end) | 
  sort_by(.installdate | tonumber) | last | 
  [ $host, $pkg, .arch, .epoch, .installdate, .version, .release ] | 
  @csv
' input.json | sed 's/"//g'

脚本逻辑说明 #

1. -s选项：将多个独立的JSON对象合并为数组，实现批量处理
2. 遍历所有主机与包：通过to_entries展开键值对，分别保存主机名与包标识到变量
3. 整合版本信息：若包存在versions数组，将主节点信息加入数组；无多版本则直接使用主节点信息
4. 筛选最新版本：按installdate数值升序排序后取最后一项，即为最新版本
5. 生成CSV：按指定顺序提取字段，转为CSV格式后移除默认双引号，匹配示例输出格式

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