json - 如何使用 jq 将任意简单 JSON 转换为 CSV？

Question

使用 jq ，如何将任意 JSON 编码的浅对象数组转换为 CSV？

这个网站上有很多问答，涵盖了对字段进行硬编码的特定数据模型，但是对于这个问题的答案应该适用于任何 JSON，唯一的限制是它是一个具有标量属性的对象数组(没有深度/complex/sub-objects，因为展平这些是另一个问题)。结果应包含给出字段名称的标题行。将优先考虑保留第一个对象的字段顺序的答案，但这不是必需的。结果可以用双引号将所有单元格括起来，或者只将那些需要引用的单元格括起来(例如'a，b')。

示例

1. 输入:

   [
       {"code": "NSW", "name": "New South Wales", "level":"state", "country": "AU"},
       {"code": "AB", "name": "Alberta", "level":"province", "country": "CA"},
       {"code": "ABD", "name": "Aberdeenshire", "level":"council area", "country": "GB"},
       {"code": "AK", "name": "Alaska", "level":"state", "country": "US"}
   ]
   

   可能的输出:

   code,name,level,country
   NSW,New South Wales,state,AU
   AB,Alberta,province,CA
   ABD,Aberdeenshire,council area,GB
   AK,Alaska,state,US
   

   可能的输出:

   "code","name","level","country"
   "NSW","New South Wales","state","AU"
   "AB","Alberta","province","CA"
   "ABD","Aberdeenshire","council area","GB"
   "AK","Alaska","state","US"
   

2. 输入:

   [
       {"name": "bang", "value": "!", "level": 0},
       {"name": "letters", "value": "a,b,c", "level": 0},
       {"name": "letters", "value": "x,y,z", "level": 1},
       {"name": "bang", "value": "\"!\"", "level": 1}
   ]
   

   可能的输出:

   name,value,level
   bang,!,0
   letters,"a,b,c",0
   letters,"x,y,z",1
   bang,"""!""",0
   

   可能的输出:

   "name","value","level"
   "bang","!","0"
   "letters","a,b,c","0"
   "letters","x,y,z","1"
   "bang","""!""","1"
   

Answer 1

首先，在您的对象数组输入中获取一个包含所有不同对象属性名称的数组。这些将是您的 CSV 的列:

(map(keys) | add | unique) as $cols

然后，对于对象数组输入中的每个对象，将您获得的列名映射到对象中的相应属性。这些将是您 CSV 的行。

map(. as $row | $cols | map($row[.])) as $rows

最后，将列名放在行之前，作为 CSV 的标题，并将生成的行流传递给 @csv 过滤器。

$cols, $rows[] | @csv

现在大家在一起。记得使用 -r 标志来获取原始字符串的结果:

jq -r '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv'