JSONを嬲ってみる

IT お仕事 覚え書き

Amazon Workspacesのデータをまとめたくて社内の方から情報を教えていただく。

JSONをCSVするのにPythonを入れて・・・

え？？？

フォーマット変更するだけやん。。。

ちょっと調べてみると、jqなるものがあるらしい。

stedolan.github.io

これをダウンロードして・・・

インストーラーじゃないのね。windows版は実行ファイル。

{
    "WorkspacesConnectionStatus": [
        {
            "WorkspaceId": "wd-cdqwe6cbq",
            "ConnectionState": "UNKNOWN",
            "ConnectionStateCheckTimestamp": "2022-07-14T01:57:57.871000+00:00",
            "LastKnownUserConnectionTimestamp": "2022-07-13T08:58:01.022000+00:00"
        },
        {
            "WorkspaceId": "wf-24pdassdj",
            "ConnectionState": "UNKNOWN",
            "ConnectionStateCheckTimestamp": "2022-07-14T01:58:23.047000+00:00",
            "LastKnownUserConnectionTimestamp": "2022-07-11T02:08:47.409000+00:00"
        },

こんなデータをCSV出力させたい。

 

>jq-win64.exe -r "." workspaces_connection_status.json

[
  {
    "WorkspaceId": "wd-cdqwe6cbq",
    "ConnectionState": "UNKNOWN",
    "ConnectionStateCheckTimestamp": "2022-07-14T01:57:57.871000+00:00",
    "LastKnownUserConnectionTimestamp": "2022-07-13T08:58:01.022000+00:00"
  },
  {
    "WorkspaceId": "wf-24pdassdj",
    "ConnectionState": "UNKNOWN",
    "ConnectionStateCheckTimestamp": "2022-07-14T01:58:23.047000+00:00",
    "LastKnownUserConnectionTimestamp": "2022-07-11T02:08:47.409000+00:00"
  },

1段階ネスト解除された。もう1段階かな

>jq-win64.exe -r "." workspaces_connection_status.json 
{
  "WorkspaceId": "wd-cdqwe6cbq",
  "ConnectionState": "UNKNOWN",
  "ConnectionStateCheckTimestamp": "2022-07-14T01:57:57.871000+00:00",
  "LastKnownUserConnectionTimestamp": "2022-07-13T08:58:01.022000+00:00"
}
{
  "WorkspaceId": "wf-24pdassdj",
  "ConnectionState": "UNKNOWN",
  "ConnectionStateCheckTimestamp": "2022-07-14T01:58:23.047000+00:00",
  "LastKnownUserConnectionTimestamp": "2022-07-11T02:08:47.409000+00:00"
}

よしよし。あとは要素を取り出す感じでCSV出力させて

>jq-win64.exe -r ". | [.WorkspaceId, .ConnectionState, .ConnectionStateCheckTimestamp, .LastKnownUserConnectionTimestamp]" workspaces_connection_status.json
[
  "wd-cdqwe6cbq",
  "UNKNOWN",
  "2022-07-14T01:57:57.871000+00:00",
  "2022-07-13T08:58:01.022000+00:00"
]
[
  "wf-24pdassdj",
  "UNKNOWN",
  "2022-07-14T01:58:23.047000+00:00",
  "2022-07-11T02:08:47.409000+00:00"
]

>jq-win64.exe -r ".[] | [.WorkspaceId, .ConnectionState, .ConnectionStateCheckTimestamp, .LastKnownUserConnectionTimestamp] | @csv" workspaces_connection_status.json |more
"wd-cdqwe6cbq","UNKNOWN","2022-07-14T01:57:57.871000+00:00","2022-07-13T08:58:01.022000+00:00"
"wf-24pdassdj","UNKNOWN","2022-07-14T01:58:23.047000+00:00","2022-07-11T02:08:47.409000+00:00"

フィルタの書き方覚えれば不要な項目を出力しないとかもすっとできるし、これが楽かも。

日付フォーマットが変なのは、エクセル側で関数でささっと修正。