☰

如何将此 JSON 转换为 CSV？

tag-icon • tag-icon csv json jq
如何将此 JSON 转换为 CSV？

我有下面的 JSON 文档要转换为 CSV。我需要在一次迭代的同时迭代第一列和第二列和第三列（它们可以是字符串或数组）。

示例文档：

[
  {
    "results": [
       [
        "abc025",
        "true",
        "test.lun"
      ],
       [
        "xyz025",
        [
          "true",
          "false",
          "true"
        ],
        [
          "product.lun",
          "app.lun",
          "ora.lun"
        ]
      ]
    ]
  }
]

预期的 CSV：

"abc025","true","test.lun"
"xyz025","true","product.lun"
"xyz025","false","app.lun"
"xyz025","true","ora.lun"

答案1

使用jq表达式

.[].results[] | .[0] as $name | .[1:] | map([.[]]? // [.]) |
(.[0]|keys[]) as $i | [ $name, .[][$i] ] | @csv

这里的第一行从每个单独的子数组（分别在单独的迭代中）中挑选出第一个元素（），$name然后将每个子数组（）中的剩余数据转换为每列一个数组的数组：.[0]abc025xyz025.[1:]

[["true"],["test.lun"]]
[["true","false","true"],["product.lun","app.lun","ora.lun"]]

这是在map()调用中完成的，其中每个元素都被提取为数组。如果这不起作用，则该元素是投入数组。因此，每个元素要么保留为数组，要么转换为单个元素数组。

第二行迭代这些列数组的索引，并将输出创建为 CSV。

测试：

$ jq -r '.[].results[] | .[0] as $name | .[1:] | map([.[]]? // [.]) | (.[0]|keys[]) as $i | [ $name, .[][$i] ] | @csv' file
"abc025","true","test.lun"
"xyz025","true","product.lun"
"xyz025","false","app.lun"
"xyz025","true","ora.lun"

请注意，这是一个概括我对你上一个问题的解决方案。您也可以将此代码与该问题中的数据一起使用。

您还可以对超过三列的数据使用此方法。该文件

[
  {
    "results": [
      [ "abc025",
        "true",
        "test.lun",
        "blueberry" ],
      [ "xyz025",
        ["true","false","true"],
        ["product.lun","app.lun","ora.lun"],
        ["strawberry","cloudberry","lingonberry"] ]
    ]
  }
]

会转换成

"abc025","true","test.lun","blueberry"
"xyz025","true","product.lun","strawberry"
"xyz025","false","app.lun","cloudberry"
"xyz025","true","ora.lun","lingonberry"

相关内容