json - 将具有嵌套值的字段从JSON导出到CSV

Question

我需要将一些JSON数据导入到数据库中。我想使用PowerShell并弄清楚一点，但是我需要帮助来整理最后的部分。

这是我现有的PowerShell脚本。

(Get-Content $path -Raw | ConvertFrom-json) | 
select -Expand data | select -Expand stations | Export-CSV
"C:\CitiBike\output.csv"

它可以完成所有工作，但可以提供Rental_method。我的问题是，如何获得带有车站ID的出租方式？

这是我的JSON数据:

{
   "last_updated":1475973127,
   "ttl":10,
   "data":{
      "stations":[
         {
            "station_id":"72",
            "name":"W 52 St & 11 Ave",
            "short_name":"6926.01",
            "lat":40.76727216,
            "lon":-73.99392888,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":39,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"79",
            "name":"Franklin St & W Broadway",
            "short_name":"5430.08",
            "lat":40.71911552,
            "lon":-74.00666661,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":33,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"82",
            "name":"St James Pl & Pearl St",
            "short_name":"5167.06",
            "lat":40.71117416,
            "lon":-74.00016545,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":27,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"83",
            "name":"Atlantic Ave & Fort Greene Pl",
            "short_name":"4354.07",
            "lat":40.68382604,
            "lon":-73.97632328,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":62,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"116",
            "name":"W 17 St & 8 Ave",
            "short_name":"6148.02",
            "lat":40.74177603,
            "lon":-74.00149746,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":39,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"119",
            "name":"Park Ave & St Edwards St",
            "short_name":"4700.06",
            "lat":40.69608941,
            "lon":-73.97803415,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":19,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"120",
            "name":"Lexington Ave & Classon Ave",
            "short_name":"4452.03",
            "lat":40.68676793,
            "lon":-73.95928168,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":19,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"127",
            "name":"Barrow St & Hudson St",
            "short_name":"5805.05",
            "lat":40.73172428,
            "lon":-74.00674436,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":31,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"128",
            "name":"MacDougal St & Prince St",
            "short_name":"5687.04",
            "lat":40.72710258,
            "lon":-74.00297088,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":30,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"137",
            "name":"E 56 St & Madison Ave",
            "short_name":"6771.02",
            "lat":40.761628,
            "lon":-73.972924,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":46,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"143",
            "name":"Clinton St & Joralemon St",
            "short_name":"4605.04",
            "lat":40.69239502,
            "lon":-73.99337909,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":24,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"144",
            "name":"Nassau St & Navy St",
            "short_name":"4812.02",
            "lat":40.69839895,
            "lon":-73.98068914,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":19,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"146",
            "name":"Hudson St & Reade St",
            "short_name":"5359.10",
            "lat":40.71625008,
            "lon":-74.0091059,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":39,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"147",
            "name":"Greenwich St & Warren St",
            "short_name":"5329.01",
            "lat":40.71542197,
            "lon":-74.01121978,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":33,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"150",
            "name":"E 2 St & Avenue C",
            "short_name":"5476.03",
            "lat":40.7208736,
            "lon":-73.98085795,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":31,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"151",
            "name":"Cleveland Pl & Spring St",
            "short_name":"5492.05",
            "lat":40.722103786686034,
            "lon":-73.99724900722504,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":33,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"152",
            "name":"Warren St & Church St",
            "short_name":"5288.09",
            "lat":40.71473993,
            "lon":-74.00910627,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":29,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"153",
            "name":"E 40 St & 5 Ave",
            "short_name":"6474.11",
            "lat":40.752062307,
            "lon":-73.9816324043,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":55,
            "eightd_has_key_dispenser":false
         },
         {
            "station_id":"157",
            "name":"Henry St & Atlantic Ave",
            "short_name":"4531.05",
            "lat":40.69089272,
            "lon":-73.99612349,
            "region_id":71,
            "rental_methods":[
               "KEY",
               "CREDITCARD"
            ],
            "capacity":23,
            "eightd_has_key_dispenser":false
         }
      ]
   }
}

Answer 1

CSV字段必须是平面值，而不是嵌套的对象或数组。
将元素连接成字符串:

Get-Content r:\1.json -Raw |
    ConvertFrom-Json | 
    Select -Expand data |
    Select -Expand stations |
    ForEach {
        $_.rental_methods = $_.rental_methods -join ' '
        $_
    } |
    Export-Csv r:\1.csv -NoTypeInformation