将含独立主机名的Windows VM软件包JSON转换为CSV

需求说明 #

给定非标准的主机-软件包数据（主机名与对应包对象交替呈现），需转换为指定格式的CSV：

输入示例 #

"host1"
{
  "PuTTY release 0.77 (64-bit)": {
    "version": "0.77.0.0",
    "publisher": "Simon Tatham",
    "installdate": "20220916"
  },
  "Microsoft Visual C++ 2013 x86 Additional Runtime - 12.0.21005": {
    "version": "12.0.21005",
    "publisher": "Microsoft Corporation",
    "installdate": "20210816"
  }
}
"host2"
{
  "Notepad++ (64-bit x64)": {
    "version": "8.4.2",
    "publisher": "Notepad++",
    "installdate": "20230517"
  },
  "Oracle Windows VirtIO Drivers": {
    "version": "1.1.3",
    "publisher": "Oracle Corporation",
    "installdate": "20200316"
  },
  "ManageEngine UEMS - Agent": {
    "version": "11.2.2315.5.W",
    "publisher": "ZohoCorp",
    "installdate": "20230601"
  }
}

预期输出 #

host1, PuTTY release 0.77 (64-bit), 0.77.0.0, Simon Tatham, 20220916
host1, Microsoft Visual C++ 2013 x86 Additional Runtime - 12.0.21005, 12.0.21005, Microsoft Corporation, 20210816
host2, Notepad++ (64-bit x64), 8.4.2, Notepad++, 20230517
host2, Oracle Windows VirtIO Drivers, 1.1.3, Oracle Corporation, 20200316
host2, ManageEngine UEMS - Agent, 11.2.2315.5.W, ZohoCorp,20230601

解决方案 #

使用jq工具处理输入，执行以下命令（假设输入文件名为input.txt）：

jq -R 'fromjson? // .' input.txt | jq -n '
  reduce inputs as $item ({};
    if type($item) == "string" then .host = $item
    else
      .host as $host
      | $item | to_entries[]
      | [
          $host,
          .key,
          .value.version,
          .value.publisher,
          .value.installdate
        ]
      | @csv
    end
  )' | sed 's/"//g'

命令解释 #

1. 格式预处理：jq -R 'fromjson? // .' input.txt 将每行内容尝试解析为JSON，无法解析的主机名字符串保留原样，生成可后续处理的数据流。
2. 生成CSV行：遍历输入流中的每个项，遇到字符串时暂存为主机名；遇到软件包对象时，将对象转为键值对，为每个软件包生成包含主机名、包名、版本、发布商、安装日期的数组，再转为CSV格式。
3. 去除引号：sed 's/"//g' 去掉CSV默认添加的引号，匹配预期输出格式。

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