☰

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이 배열이라는 것이 아니라 배열의 각 요소(단 하나뿐임)가 다소 복잡한 구조라는 것입니다. 각 배열 항목의 관련 데이터를 더 짧은 평면 배열로 직접 추출한 다음 다음을 사용하여 @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) |
+----+---------------------------------------------+-------------------+------+---------------------+------------+---------------------------------------------------------------------------------------+-------------+-------------+--------------+----------------------------------------------+---------------------+

관련 정보