如何用PowerShell将嵌套JSON数组转换为指定格式CSV？
2026-7-31
PowerShell嵌套JSON转CSV完整数据输出解决方案
需要将嵌套JSON数组文件data.json转换为包含全层级数据的CSV文件dataout.csv，需涵盖客户、云账户、产品、使用类型及对应价格、成本、用量信息。原脚本仅输出部分数据，以下是问题详情及修正方案：
原脚本
$json = @' [ { "entity": { "id": "2344", "type": "customer", "company": "IT Consultant.", "name": "John T" }, "lines": [ { "entity": { "id": "070537205486", "type": "aws_linked_account", "cost_currency": "USD", "price_currency": "USD" }, "lines": [ { "entity": { "id": "Amazon Elastic Compute Cloud", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "EU-EBS:SnapshotUsage", "type": "usage_type" }, "data": { "price": "0.4600000000", "cost": "0.4400000000", "usage": "9.1744031906" } } ] }, { "entity": { "id": "AWS Cost Explorer", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "USE1-APIRequest", "type": "usage_type" }, "data": { "price": "0.1800000000", "cost": "0.1700000000", "usage": "18.0000000000" } } ] }, { "entity": { "id": "Tech Data AWS Business Support - Reseller", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "Dollar", "type": "usage_type" }, "data": { "price": "0.2300000000", "cost": "0.2300000000", "usage": "1.0000000000" } } ] } ] }, { "entity": { "id": "852839205775", "type": "aws_linked_account", "cost_currency": "USD", "price_currency": "USD" }, "lines": [ { "entity": { "id": "Amazon Elastic Compute Cloud", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "EU-EBS:SnapshotUsage", "type": "usage_type" }, "data": { "price": "0.9800000000", "cost": "0.9500000000", "usage": "19.6428565979" } } ] } ] } ] }, { "entity": { "id": "1455", "type": "customer", "company": "Insurance Company", "name": "Eric M." }, "lines": [ { "entity": { "id": "353813116714", "type": "aws_linked_account", "cost_currency": "USD", "price_currency": "USD" }, "lines": [ { "entity": { "id": "Amazon Elastic Compute Cloud", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "EU-EBS:SnapshotUsage", "type": "usage_type" }, "data": { "price": "0.4600000000", "cost": "0.4400000000", "usage": "9.1744031906" } } ] }, { "entity": { "id": "AWS Cost Explorer", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "USE1-APIRequest", "type": "usage_type" }, "data": { "price": "0.1800000000", "cost": "0.1700000000", "usage": "18.0000000000" } } ] }, { "entity": { "id": "Tech Data AWS Business Support - Reseller", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "Dollar", "type": "usage_type" }, "data": { "price": "0.2300000000", "cost": "0.2300000000", "usage": "1.0000000000" } } ] } ] } ] } ] '@ $Data = $json | ConvertFrom-Json $output = foreach ( $customer in $data ) { $customerName = "$($customer.entity.company) ($($customer.entity.name))" foreach ( $cloudAccount in $customer.lines ) { $cloudAccountNumber = $cloudAccount.entity.id # Continue to nest down to get out all colums data foreach ( $productName in $cloudaccount.lines ) { $cloudproductname = $productName.entity.id } foreach ( $usagetype in $productName.lines ) { $cloudusagetype = $usagetype.entity.id $cloudprice = $usagetype.data.price $cloudcost = $usagetype.data.cost $cloudusage = $usagetype.data.usage } # output the result [pscustomobject] @{ "Customer Name" = $customerName "Cloud Account Number" = $cloudAccountNumber "Product Name" = $cloudproductname "Usage Type" = $cloudusagetype "Price" = $cloudprice "Cost" = $cloudcost "Usage" = $cloudusage # ... } } } # Convert to csv $output | Export-Csv -Path myfil.csv
当前输出
"Customer Name","Cloud Account Number","Product Name","Usage Type","Price","Cost","Usage" "IT Consultant. (John T)","070537205486","Tech Data AWS Business Support - Reseller","Dollar","0.2300000000","0.2300000000","1.0000000000" "IT Consultant. (John T)","852839205775","Amazon Elastic Compute Cloud","EU-EBS:SnapshotUsage","0.9800000000","0.9500000000","19.6428565979" "Insurance Company (Eric M.)","353813116714","Tech Data AWS Business Support - Reseller","Dollar","0.2300000000","0.2300000000","1.0000000000"
期望输出
"Customer Name","Cloud Account Number","Product Name","Usage Type","Price","Cost","Usage" "IT Consultant. (John T)","070537205486","Amazon Elastic Compute Cloud","EU-EBS:SnapshotUsage","0.4600000000","0.4400000000","9.1744031906" "IT Consultant. (John T)","070537205486","AWS Cost Explorer","USE1-APIRequest","0.1800000000","0.1700000000","18.0000000000" "IT Consultant. (John T)","070537205486","Tech Data AWS Business Support - Reseller","Dollar","0.2300000000","0.2300000000","1.0000000000" "IT Consultant. (John T)","852839205775","Amazon Elastic Compute Cloud","EU-EBS:SnapshotUsage","0.9800000000","0.9500000000","19.6428565979" "Insurance Company (Eric M.)","353813116714","Amazon Elastic Compute Cloud","EU-EBS:SnapshotUsage","0.4600000000","0.4400000000","9.1744031906" "Insurance Company (Eric M.)","353813116714","AWS Cost Explorer","USE1-APIRequest","0.1800000000","0.1700000000","18.0000000000" "Insurance Company (Eric M.)","353813116714","Tech Data AWS Business Support - Reseller","Dollar","0.2300000000","0.2300000000","1.0000000000"
问题原因
原脚本循环逻辑存在错误：
- 产品循环与使用类型循环未正确嵌套，仅捕获每个云账户下的最后一个产品及对应使用类型
- 输出对象放置在云账户循环内，而非最内层的使用类型循环，导致每个云账户仅输出一行数据，无法覆盖所有产品与使用类型的组合
修正后脚本
$json = @' [ { "entity": { "id": "2344", "type": "customer", "company": "IT Consultant.", "name": "John T" }, "lines": [ { "entity": { "id": "070537205486", "type": "aws_linked_account", "cost_currency": "USD", "price_currency": "USD" }, "lines": [ { "entity": { "id": "Amazon Elastic Compute Cloud", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "EU-EBS:SnapshotUsage", "type": "usage_type" }, "data": { "price": "0.4600000000", "cost": "0.4400000000", "usage": "9.1744031906" } } ] }, { "entity": { "id": "AWS Cost Explorer", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "USE1-APIRequest", "type": "usage_type" }, "data": { "price": "0.1800000000", "cost": "0.1700000000", "usage": "18.0000000000" } } ] }, { "entity": { "id": "Tech Data AWS Business Support - Reseller", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "Dollar", "type": "usage_type" }, "data": { "price": "0.2300000000", "cost": "0.2300000000", "usage": "1.0000000000" } } ] } ] }, { "entity": { "id": "852839205775", "type": "aws_linked_account", "cost_currency": "USD", "price_currency": "USD" }, "lines": [ { "entity": { "id": "Amazon Elastic Compute Cloud", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "EU-EBS:SnapshotUsage", "type": "usage_type" }, "data": { "price": "0.9800000000", "cost": "0.9500000000", "usage": "19.6428565979" } } ] } ] } ] }, { "entity": { "id": "1455", "type": "customer", "company": "Insurance Company", "name": "Eric M." }, "lines": [ { "entity": { "id": "353813116714", "type": "aws_linked_account", "cost_currency": "USD", "price_currency": "USD" }, "lines": [ { "entity": { "id": "Amazon Elastic Compute Cloud", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "EU-EBS:SnapshotUsage", "type": "usage_type" }, "data": { "price": "0.4600000000", "cost": "0.4400000000", "usage": "9.1744031906" } } ] }, { "entity": { "id": "AWS Cost Explorer", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "USE1-APIRequest", "type": "usage_type" }, "data": { "price": "0.1800000000", "cost": "0.1700000000", "usage": "18.0000000000" } } ] }, { "entity": { "id": "Tech Data AWS Business Support - Reseller", "type": "product", "source": "aws_billing" }, "lines": [ { "entity": { "id": "Dollar", "type": "usage_type" }, "data": { "price": "0.2300000
相关产品推荐
相关产品推荐
