JSON嵌套数组到CSV _大数据知识库

我有一个Json文件，我需要使用Powershell转换为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"

根据备注：
如果我没理解错你的问题，你想为每个 * $productName.lines做$usagetype *，你想为每个 * $cloudaccount.lines做那个 *...
这意味着foreach ( $usagetype in $productName.lines ) { ...循环应该进入foreach ( $productName in $cloudaccount.lines ) { ...循环，输出[pscustomobject] @{应该进入该内部循环：foreach ( $productName in $cloudaccount.lines ) { ...
换句话说，如果你需要读取一个JSON Nested Array，你需要 * 嵌套 * 你的循环。

$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

JSON嵌套数组到CSV

1条答案

相关问题

热门标签

最新问答