arrays - 使用 jq 格式化为 CSV JSON 文件

Question

我在一个名为 myfile.json 的文件中有一些数据。我需要使用 jq 格式化 - 在 JSON 中它看起来像这样；

{ "result": [ { "service": "ebsvolume", "name": "gtest", "resourceIdentifier": "vol-999999999999", "accountName": "g-test-acct", "vendorAccountId": "12345678912", "availabilityZone": "ap-southeast-2c", "region": "ap-southeast-2", "effectiveHourly": 998.56, "totalSpend": 167.7, "idle": 0, "lastSeen": "2018-08-16T22:00:00Z", "volumeType": "io1", "state": "in-use", "volumeSize": 180, "iops": 2000, "throughput": 500, "lastAttachedTime": "2018-08-08T22:00:00Z", "lastAttachedId": "i-086f957ee", "recommendations": [ { "action": "Rightsize", "preferenceOrder": 2, "risk": 0, "savingsPct": 91, "savings": 189.05, "volumeType": "gp2", "volumeSize": 120, }, { "action": "Rightsize", "preferenceOrder": 4, "risk": 0, "savingsPct": 97, "savings": 166.23, "volumeType": "gp2", "volumeSize": 167, }, { "action": "Rightsize", "preferenceOrder": 6, "risk": 0, "savingsPct": 91, "savings": 111.77, "volumeType": "gp2", "volumeSize": 169, } ] } }

我使用以下内容更好地格式化了它

jq '.result[] | [.service,.name,.resourceIdentifier,.accountName,.vendorAccountId,.availabilityZone,.region,.effectiveHourly,.totalSpend,.idle,.lastSeen,.volumeType,.state,.volumeSize,.iops,.throughput,.lastAttachedTime,.lastAttachedId] |@csv' ./myfile.json

这得到了以下输出；

"\"ebsvolume\",\"gtest\",\"vol-999999999999\",\"g-test-acct\",\"12345678912\",\"ap-southeast-2c\",\"ap-southeast-2\",998.56,167.7,0,\"2018-08-16T22:00:00Z\",\"io1\",\"in-use\",180,2000,500,\"2018-08-08T22:00:00Z\",\"i-086f957ee\""

我想通了，但这并不完全是我想要达到的目标。我希望将每个建议列在单独的行下方，而不是在同一行的末尾。

jq '.result[] | [.service,.name,.resourceIdentifier,.accountName,.vendorAccountId,.availabilityZone,.region,.effectiveHourly,.totalSpend,.idle,.lastSeen,.volumeType,.state,.volumeSize,.iops,.throughput,.lastAttachedTime,.lastAttachedId,.recommendations[].action] |@csv' ./myfile.json

这个网：

"\"ebsvolume\",\"gtest\",\"vol-999999999999\",\"g-test-acct\",\"12345678912\",\"ap-southeast-2c\",\"ap-southeast-2\",998.56,167.7,0,\"2018-08-16T22:00:00Z\",\"io1\",\"in-use\",180,2000,500,\"2018-08-08T22:00:00Z\",\"i-086f957ee\",\"Rightsize\",\"Rightsize\",\"Rightsize\""

我想要的是

"\"ebsvolume\",\"gtest\",\"vol-999999999999\",\"g-test-acct\",\"12345678912\",\"ap-southeast-2c\",\"ap-southeast-2\",998.56,167.7,0,\"2018-08-16T22:00:00Z\",\"io1\",\"in-use\",180,2000,500,\"2018-08-08T22:00:00Z\",\"i-086f957ee\", \"Rightsize\", \"Rightsize\", \"Rightsize\""

所以不完全确定如何处理 jq 中“建议”部分中的数组，我认为它可能被称为 unflattening？

标签： arraysjsonformattingjq

Answer 1

你可以试试这个：

jq '.result[] | [ flatten[] | try(.action) // . ] | @csv' file
"\"ebsvolume\",\"gtest\",\"vol-999999999999\",\"g-test-acct\",\"12345678912\",\"ap-southeast-2c\",\"ap-southeast-2\",998.56,167.7,0,\"2018-08-16T22:00:00Z\",\"io1\",\"in-use\",180,2000,500,\"2018-08-08T22:00:00Z\",\"i-086f957ee\",\"Rightsize\",\"Rightsize\",\"Rightsize\""

flatten做它所说的。

try测试.action既不是null也不是false。如果是，它发出它的值，否则jq发出另一个值（运算符//）。

The filtered values are put into an array in order to get them converted with the @csv operator.