如何使用jq将任意简单的JSON转换为CSV?

问题描述

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

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

此网站上有很多问答，涵盖了对字段进行硬编码的特定数据模型，但是对于任何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').

1. 输入:

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

可能的输出:

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

可能的输出:

"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

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

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过滤器.

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

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