jqコマンドにてjsonからcsv形式に変換

Question

■質問内容
以下のようなjsonをcsvに変換したいです。
jqで頑張っているのですがうまくいかず、、、

■json

{
    "SecurityGroups": [
        {
            "Description": "sg1",
            "IpPermissions": [
                {
                    "PrefixListIds": [],
                    "FromPort": 0,
                    "IpRanges": [
                        {
                            "Description": "Des1",
                            "CidrIp": "10.0.0.1/24"
                        },
                        {
                            "Description": "Des2",
                            "CidrIp": "10.0.0.2/24"
                        }
                    ],
                    "ToPort": 65535,
                    "IpProtocol": "tcp"
                },
                {
                    "PrefixListIds": [],
                    "IpRanges": [
                        {
                            "Description": "Des1",
                            "CidrIp": "10.0.1.1/24"
                        },
                        {
                            "Description": "Des2",
                            "CidrIp": "10.0.1.2/24"
                        }
                    ],
                    "IpProtocol": "-1"
                }
            ],
            "GroupName": "gn1",
            "VpcId": "vpc1",
            "OwnerId": "111",
            "GroupId": "gi1"
        }
    ]
}

■csvフォーマット

# GroupName,GroupId,IpProtocol,FromPort,ToPort,CidrIp,Description
gn1,gi1,tcp,0,65535,10.0.0.1/24,Des1
gn1,gi1,tcp,0,65535,10.0.0.2/24,Des2
gn1,gi1,-1,,,10.0.1.1/24,Des1
gn1,gi1,-1,,,10.0.1.2/24,Des2

Comments

[yambejp]

言語はjsで良いのでしょうか？→失礼しましたjqコマンドですね
csvはファイル出力するのでしょうか？
csv中の特殊文字の扱いはどうするのでしょうか？

[hoshi-takanori]

jq で csv に変換なんてできるのか、と思って調べてみましたが、もっと単純なデータ構造じゃないと難しそうですね…。
https://medium.com/veltra-engineering/jq-supports-json-to-csv-fb5c951a9575

[hakoritenshi]

階層を揃えられれば、あとはリストにして@csvなのですが、
階層を揃えられず、悩んでいます。

以下が今のところ一番惜しい感じなのですが、フルメッシュに項目が作られてしまって想定と違う個数になってしまうんです、、、

[root@rhel8 12:00:01 ~]# cat hoge.json | jq -r '.SecurityGroups[]|{GroupName,IpPermissions:.IpPermissions[].IpRanges[],FromPort:.IpPermissions[].IpProtocol}'
{
"GroupName": "gn1",
"IpPermissions": {
"Description": "Des1",
"CidrIp": "10.0.0.1/24"
},
"FromPort": "tcp"
}
{
"GroupName": "gn1",
"IpPermissions": {
"Description": "Des1",
"CidrIp": "10.0.0.1/24"
},
"FromPort": "-1"
}
{
"GroupName": "gn1",
"IpPermissions": {
"Description": "Des2",
"CidrIp": "10.0.0.2/24"
},
"FromPort": "tcp"
}
{
"GroupName": "gn1",
"IpPermissions": {
"Description": "Des2",
"CidrIp": "10.0.0.2/24"
},
"FromPort": "-1"
}
{
"GroupName": "gn1",
"IpPermissions": {
"Description": "Des1",
"CidrIp": "10.0.1.1/24"
},
"FromPort": "tcp"
}
{
"GroupName": "gn1",
"IpPermissions": {
"Description": "Des1",
"CidrIp": "10.0.1.1/24"
},
"FromPort": "-1"
}
{
"GroupName": "gn1",
"IpPermissions": {
"Description": "Des2",
"CidrIp": "10.0.1.2/24"
},
"FromPort": "tcp"
}
{
"GroupName": "gn1",
"IpPermissions": {
"Description": "Des2",
"CidrIp": "10.0.1.2/24"
},
"FromPort": "-1"
}
[root@rhel8 12:00:05 ~]#

Answer 1

自己解決できました！

[root@rhel8 13:32:17 tmp]# 
cat hoge.json | 
  jq -r '.SecurityGroups[] |
    {
      Description,
      GroupName,
      GroupId,
      IpPermissions:.IpPermissions[]
    } |
    {
      Description,
      GroupName,
      GroupId,
      IpProtocol:.IpPermissions.IpProtocol,
      FromPort:.IpPermissions.FromPort,
      ToPort:.IpPermissions.ToPort,
      IpRanges:.IpPermissions.IpRanges[]
    } |
    [
      .Description,.GroupName,
      .GroupId,.IpProtocol,
      .FromPort,.ToPort,
      .IpRanges.Description,
      .IpRanges.CidrIp
    ] |@csv'

"sg1","gn1","gi1","tcp",0,65535,"Des1","10.0.0.1/24"
"sg1","gn1","gi1","tcp",0,65535,"Des2","10.0.0.2/24"
"sg1","gn1","gi1","-1",,,"Des1","10.0.1.1/24"
"sg1","gn1","gi1","-1",,,"Des2","10.0.1.2/24"
[root@rhel8 13:32:19 tmp]#

■参考サイト
https://qiita.com/sotoiwa/items/431358283dee00eb6f46
https://jupitrisonlabs.hatenadiary.jp/entry/20151127/1448606090