我正在寻找将 JSON 转换为 CSV 的解决方案。似乎大多数解决方案都希望 JSON 是单个对象而不是对象数组。
我在这里尝试过的所有解决方案似乎都与我的输入不符，这些输入来自冰壶这个网站。
jq当输入是数组而不是对象时，如何使用其他工具将 JSON 转换为 CSV 。
[
{
"id": "4",
"link": "https://pressbooks.online.ucf.edu/amnatgov/",
"metadata": {
"@context": "http://schema.org",
"@type": "Book",
"name": "American Government",
"inLanguage": "en",
"copyrightYear": "2016",
"disambiguatingDescription": "The content of this textbook has been developed and arranged to provide a logical progression from the fundamental principles of institutional design at the founding, to avenues of political participation, to thorough coverage of the political structures that constitute American government. The book builds upon what students have already learned and emphasizes connections between topics as well as between theory and applications. The goal of each section is to enable students not just to recognize concepts, but to work with them in ways that will be useful in later courses, future careers, and as engaged citizens. ",
"image": "https://pressbooks.online.ucf.edu/app/uploads/sites/4/2020/01/American-Government.png",
"isBasedOn": "https://ucf-dev.pb.unizin.org/pos2041",
"author": [
{
"@type": "Person",
"name": "OpenStax"
}
],
"datePublished": "2016-01-06",
"copyrightHolder": {
"@type": "Organization",
"name": "cnxamgov"
},
"license": {
"@type": "CreativeWork",
"url": "https://creativecommons.org/licenses/by/4.0/",
"name": "CC BY (Attribution)"
}
},
"_links": {
"api": [
{
"href": "https://pressbooks.online.ucf.edu/amnatgov/wp-json/"
}
],
"metadata": [
{
"href": "https://pressbooks.online.ucf.edu/amnatgov/wp-json/pressbooks/v2/metadata"
}
],
"self": [
{
"href": "https://pressbooks.online.ucf.edu/wp-json/pressbooks/v2/books/4"
}
]
}
}
]
所需格式：
id, link, context, type, name, inLanguage, image, author_type, author_name, license_type, license_url, license_name
答案1
问题并不在于您显示的 JSON 是一个数组，而是该数组的每个元素（其中只有一个）是一个相当复杂的结构。可以直接将每个数组条目中的相关数据提取到较短的平面数组中，然后使用 in 将其转换为@csvCSV jq：
jq -r '.[] | [
.id,
.link,
.metadata."@context",
.metadata."@type",
.metadata.name,
.metadata.inLanguage,
.metadata.image,
.metadata.author[0]."@type",
.metadata.author[0].name,
.metadata.license."@type",
.metadata.license.url,
.metadata.license.name
] | @csv' file.json
...但请注意我如何被迫决定我们只对第一作者感兴趣（.metadata.author子结构是一个数组）。
输出：
"4","https://pressbooks.online.ucf.edu/amnatgov/","http://schema.org","Book","American Government","en","https://pressbooks.online.ucf.edu/app/uploads/sites/4/2020/01/American-Government.png","Person","OpenStax","CreativeWork","https://creativecommons.org/licenses/by/4.0/","CC BY (Attribution)"
要创建由所有作者姓名串联而成的作者姓名字符串（对于作者类型也类似），使用;as 分隔符，您可以代替.metadata.author[0].name上面的使用[.metadata.author[].name]|join(";")（以及 [.metadata.author[]."@type"]|join(";")类型），这样您的命令就变成
jq -r '.[] | [
.id,
.link,
.metadata."@context",
.metadata."@type",
.metadata.name,
.metadata.inLanguage,
.metadata.image,
( [ .metadata.author[]."@type" ] | join(";") ),
( [ .metadata.author[].name ] | join(";") ),
.metadata.license."@type",
.metadata.license.url,
.metadata.license.name
] | @csv' file.json
答案2
使用米勒（https://github.com/johnkerl/miller）你可以“扁平化”JSON，运行
mlr --j2c cat input.json >output.csv
+----+---------------------------------------------+-------------------+----------------+---------------------+---------------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+---------------------------------------+-------------------------+------------------------+------------------------+--------------------------------+-------------------------------+------------------------+----------------------------------------------+-----------------------+-----------------------------------------------------+---------------------------------------------------------------------------+-----------------------------------------------------------------+
| id | link | metadata:@context | metadata:@type | metadata:name | metadata:inLanguage | metadata:copyrightYear | metadata:disambiguatingDescription | metadata:image | metadata:isBasedOn | metadata:author:0:@type | metadata:author:0:name | metadata:datePublished | metadata:copyrightHolder:@type | metadata:copyrightHolder:name | metadata:license:@type | metadata:license:url | metadata:license:name | _links:api:0:href | _links:metadata:0:href | _links:self:0:href |
+----+---------------------------------------------+-------------------+----------------+---------------------+---------------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+---------------------------------------+-------------------------+------------------------+------------------------+--------------------------------+-------------------------------+------------------------+----------------------------------------------+-----------------------+-----------------------------------------------------+---------------------------------------------------------------------------+-----------------------------------------------------------------+
| 4 | https://pressbooks.online.ucf.edu/amnatgov/ | http://schema.org | Book | American Government | en | 2016 | The content of this textbook has been developed and arranged to provide a logical progression from the fundamental principles of institutional design at the founding, to avenues of political participation, to thorough coverage of the political structures that constitute American government. The book builds upon what students have already learned and emphasizes connections between topics as well as between theory and applications. The goal of each section is to enable students not just to recognize concepts, but to work with them in ways that will be useful in later courses, future careers, and as engaged citizens. | https://pressbooks.online.ucf.edu/app/uploads/sites/4/2020/01/American-Government.png | https://ucf-dev.pb.unizin.org/pos2041 | Person | OpenStax | 2016-01-06 | Organization | cnxamgov | CreativeWork | https://creativecommons.org/licenses/by/4.0/ | CC BY (Attribution) | https://pressbooks.online.ucf.edu/amnatgov/wp-json/ | https://pressbooks.online.ucf.edu/amnatgov/wp-json/pressbooks/v2/metadata | https://pressbooks.online.ucf.edu/wp-json/pressbooks/v2/books/4 |
+----+---------------------------------------------+-------------------+----------------+---------------------+---------------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+---------------------------------------+-------------------------+------------------------+------------------------+--------------------------------+-------------------------------+------------------------+----------------------------------------------+-----------------------+-----------------------------------------------------+---------------------------------------------------------------------------+-----------------------------------------------------------------+
然后提取所需的字段并使用重命名它们
mlr -I --csv cut -f "id","link","metadata:@context","metadata:@type","metadata:name","metadata:inLanguage","metadata:image","metadata:author:0:@type","metadata:author:0:name","metadata:license:@type","metadata:license:url","metadata:license:name" \
then label id,link,context,type,name,inLanguage,image,author_type,author_name,license_type,license_url,license_name output.csv
输出将是
+----+---------------------------------------------+-------------------+------+---------------------+------------+---------------------------------------------------------------------------------------+-------------+-------------+--------------+----------------------------------------------+---------------------+
| id | link | context | type | name | inLanguage | image | author_type | author_name | license_type | license_url | license_name |
+----+---------------------------------------------+-------------------+------+---------------------+------------+---------------------------------------------------------------------------------------+-------------+-------------+--------------+----------------------------------------------+---------------------+
| 4 | https://pressbooks.online.ucf.edu/amnatgov/ | http://schema.org | Book | American Government | en | https://pressbooks.online.ucf.edu/app/uploads/sites/4/2020/01/American-Government.png | Person | OpenStax | CreativeWork | https://creativecommons.org/licenses/by/4.0/ | CC BY (Attribution) |
+----+---------------------------------------------+-------------------+------+---------------------+------------+---------------------------------------------------------------------------------------+-------------+-------------+--------------+----------------------------------------------+---------------------+