將 JSON 數組轉換為 CSV

我正在尋找將 JSON 轉換為 CSV 的解決方案。似乎大多數解決方案都希望 JSON 是單個對象而不是對像數組。

我從這裡嘗試過的所有解決方案似乎都與我的輸入不同，這些輸入來自curling this site。

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

問題並不是您顯示的 JSON 是一個數組，而是數組的每個元素（您只有一個）都是一個相當複雜的結構。直接將每個數組條目中的相關數據提取到一個較短的平面數組中，然後使用@csvin將其轉換為 CSV 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)"

---

要創建作為所有作者姓名串聯的作者姓名字元串（對於作者類型也是如此），使用;分隔符，您可以代替.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

引用自：https://unix.stackexchange.com/questions/570548