Export fields with nested values from JSON to CSV

Question

I need to import some JSON data am getting into my database. I'd like to use PowerShell and figured out a little, but I need help getting the final piece together.

Here is my existing PowerShell script.

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

It does everything but get rental_methods out. My question is, how can I get rental methods out with the ID of the station?

Here is my JSON data:

{
   "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
         }
      ]
   }
}

woxxom · Accepted Answer · 2016-10-09 10:23:45Z

4

CSV fields must be flat values, not nested objects or arrays. Join the elements into a string:

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

answered Oct 9, 2016 at 10:23

woxxom

75.1k15 gold badges161 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jhoop2002 Over a year ago

That is awesome, Thanks. I've seen notation "$_" in a lot of examples. What does that do within this example? Does it reference the station element that is being iterated over?

woxxom Over a year ago

See What does $_ mean in PowerShell?.

Abhis Over a year ago

How can we handle if rentel method is not in array? Lets say "rental_methods":{ "mode1" : "KEY", "mode2": "CREDITCARD" }

Collectives™ on Stack Overflow

Export fields with nested values from JSON to CSV

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related