如何使用jq将arbirtrary简单的JSON转换为CSV？ [英] How to convert arbirtrary simple JSON to CSV using jq?
问题描述
使用 jq ，如何将浅对象数组的任意JSON编码转换为CSV？
Using jq, how can arbitrary JSON encoding an array of shallow objects be converted to CSV?
在这个网站上有很多Q& As，它们涵盖了对字段进行硬编码的特定数据模型，但是这个问题的答案应该适用于任何JSON，唯一的限制是它是一个数组的对象与标量属性（没有深/复杂/子对象，因为扁平化这些是另一个问题）。结果应该包含一个标题行，给出字段名称。将优先考虑保留第一个对象的字段顺序的答案，但这不是一个要求。结果可以用双引号括起所有单元格，或者只包含需要引用的单元格（例如'a，b'）。
There are plenty of Q&As on this site that cover specific data models which hard-code the fields, but answers to this question should work given any JSON, with the only restriction that it's an array of objects with scalar properties (no deep/complex/sub-objects, as flattening these is another question). The result should contain a header row giving the field names. Preference will be given to answers that preserve the field order of the first object, but it's not a requirement. Results may enclose all cells with double-quotes, or only enclose those that require quoting (e.g. 'a,b').
-
输入：
Input:
[
{"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"
输入：
Input:
[
{"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
可能的输出： / p>
Possible output:
"name","value","level"
"bang","!","0"
"letters","a,b,c","0"
"letters","x,y,z","1"
"bang","""!""","1"
推荐答案
首先，获取一个包含对象数组输入中所有不同对象属性名称的数组。这些将是CSV的列：
First, obtain an array containing all the different object property names in your object array input. Those will be the columns of your CSV:
(map(keys) | add | unique) as $cols
然后，对于对象数组输入中的每个对象，将获取的列名映射到对象中的相应属性。
Then, for each object in the object array input, map the column names you obtained to the corresponding properties in the object. Those will be the rows of your CSV.
map(. as $row | $cols | map($row[.])) as $rows
最后，将列名放在行之前，作为CSV的标题，并将生成的行流传递到 @csv 过滤器。
$cols, $rows[] | @csv
现在一起。请记住使用 -r 标志将结果作为原始字符串：
All together now. Remember to use the -r flag to get the result as a raw string:
jq -r '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv'
这篇关于如何使用jq将arbirtrary简单的JSON转换为CSV？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
