如何使用 jq 将任意简单的 JSON 转换为 CSV? [英] How to convert arbitrary simple JSON to CSV using jq?

问题描述

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

这个站点上有很多 Q&As 涵盖了对字段进行硬编码的特定数据模型，但是这个问题的答案应该适用于任何 JSON，唯一的限制是它是具有标量属性的对象数组(没有深层/复杂/子对象，因为扁平化这些是另一个问题).结果应包含给出字段名称的标题行.将优先考虑保留第一个对象的字段顺序的答案，但这不是必需的.结果可以用双引号将所有单元格括起来，或者只将那些需要引用的单元格括起来(例如'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"}]

   可能的输出:

   代码、名称、级别、国家新南威尔士州，新南威尔士州，州，澳大利亚AB,阿尔伯塔省,CAABD，阿伯丁郡，议会区，GBAK,阿拉斯加州,美国

   可能的输出:

   "code","name","level","country"新南威尔士州"、新南威尔士州"、州"、澳大利亚"AB"、阿尔伯塔"、省"、CA"ABD"、阿伯丁郡"、议会区"、GB"AK"、阿拉斯加"、州"、美国"

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}]

   可能的输出:

   名称、值、级别砰,!,0字母,"a,b,c",0字母,"x,y,z",1砰,"""!""",0

   可能的输出:

   "name","value","level"砰"，！"，0"字母"、a、b、c"、0"字母"、x、y、z"、1""砰","""!""","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'

Using jq, how can arbitrary JSON encoding an array of shallow objects be converted to CSV?

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').

Examples

1. 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"}
   ]
   

   Possible output:

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

   Possible output:

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

2. 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}
   ]
   

   Possible output:

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

   Possible output:

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

解决方案

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

Finally, put the column names before the rows, as a header for the CSV, and pass the resulting row stream to the @csv filter.

$cols, $rows[] | @csv

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 将任意简单的 JSON 转换为 CSV?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！