json bash编译输出到一个csv使用jq?

oxiaedzo  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(87)

我正在写一个bash脚本,从一个不一致的json数据样本中创建一个csv,但遇到了一系列问题:
我的json数据看起来像这样:

json_data='[
  {
    "ResourceARN": "arn",
    "Tags": [
      {
        "Key": "STAGE",
        "Value": "st"
      }
    ]
  },
  {
    "ResourceARN": "arn",
    "Tags": [
      {
        "Key": "STAGE",
        "Value": "st"
      }
    ]
  },
  {
    "ResourceARN": "arn",
    "Tags": [
      {
        "Key": "aws:cloudformation:stack-name",
        "Value": "aud"
      },
      {
        "Key": "CostCenter",
        "Value": "Development"
      },
      {
        "Key": "aws:cloudformation:stack-id",
        "Value": "arn"
      },
      {
        "Key": "STAGE",
        "Value": "ec"
      },
      {
        "Key": "Environment",
        "Value": "development"
      },
      {
        "Key": "Region",
        "Value": "us-west-2"
      },
      {
        "Key": "Service",
        "Value": "MP"
      },
      {
        "Key": "aws:cloudformation:logical-id",
        "Value": "ApiGatewayRestApi"
      },
      {
        "Key": "Team",
        "Value": "VNP"
      }
    ]
  } and so on
]'

字符串
我试图创建一个CSV与以下列名称:ARN,阶段,成本中心,服务,域,团队和价值观应该是什么是在标签数组的键值对中找到的,无论键值对不存在,然后只是离开列为空。
我的代码看起来像这样:

# Define the columns
columns=("ARN" "STAGE" "CostCenter" "Service" "Domain" "Team")

# Create CSV header
header=$(IFS=','; echo "${columns[*]}")

# Function to get value for a specific key in the Tags array
get_value() {
  key="$1"
  tags="$2"
  value=$(echo "$tags" | jq -r --arg key "$key" '.[] | select(.Key == $key) | .Value')
  if [ "$value" == "null" ]; then
    echo ""
  else
    echo "$value"
  fi
}

# Create the CSV data
csv_data=""
for item in "${json_data[@]}"; do
  resource_arn=$(echo "$item" | jq -r '.ResourceARN')
  row="$resource_arn"
  for col in "${columns[@]}"; do
    value=$(get_value "$col" "$(echo "$item" | jq -c '.Tags')")
    row="$row,$value"
  done
  csv_data="$csv_data$row\n"
done

# Output the CSV to a file
echo -e "$header\n$csv_data" > output.csv

echo "CSV data has been written to output.csv"


但我总是碰到

jq: error (at <stdin>:57): Cannot index array with string "ResourceARN"
jq: error (at <stdin>:57): Cannot index array with string "Tags"
jq: error (at <stdin>:57): Cannot index array with string "Tags"
jq: error (at <stdin>:57): Cannot index array with string "Tags"
jq: error (at <stdin>:57): Cannot index array with string "Tags"
jq: error (at <stdin>:57): Cannot index array with string "Tags"
jq: error (at <stdin>:57): Cannot index array with string "Tags"


基本上,最终的CSV应该看起来像

ARN STAGE CostCenter Service Domain Team
arn st.   ""          ""      ""     ""
arn st    ""          ""      ""     "" 
arn ec    Development MP.     ""     VNP
....


有人能指出我做错了什么吗?或者是否有一种简单的方法可以做到这一点?我想jq会简单地将值留空并输出有值的值?谢谢。

kokeuurv

kokeuurv1#

既然你说你想生成CSV,让我从一个过滤器开始:

["ARN", "STAGE", "CostCenter", "Service", "Domain", "Team"] as $columns
| $columns, 
  (.[]
   | .ResourceARN as $ARN
   |  .Tags | from_entries
   | [$ARN, .[ $columns[1:][]]] )
| @csv

字符串
您的示例性输出似乎更接近TSV,并且您表示希望缺失的字段由""表示,因此我们可以相应地调整上面的内容:

["ARN", "STAGE", "CostCenter", "Service", "Domain", "Team"] as $columns
| "\"\"" as $q
| $columns, 
  (.[]
   | .ResourceARN as $ARN
   |  .Tags | from_entries
   | [$ARN, (.[ $columns[1:][]] | (. // $q ) ) ] )
| @tsv


(The这里的技巧是使用STREAM | (. // $q)而不是STREAM // $q。)
使用您的示例输入,将生成:

ARN STAGE   CostCenter  Service Domain  Team
arn st  ""  ""  ""  ""
arn st  ""  ""  ""  ""
arn ec  Development MP  ""  VNP


您可能希望考虑其他变体,例如使用join(",")而不是@csv@tsv

相关问题