☰

JSON配列をCSVに変換

tag-icon • tag-icon csv json jq
JSON配列をCSVに変換

私は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が配列であるのではなく、配列の各要素（1つだけ）がやや複雑な構造であることです。各配列項目の関連データをより短い平面配列に直接抽出し、次を使用して@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

おすすめ記事