0

I got a question about How to use Powershell flatten a nested JSON and covert to CSV. Below is my JSON, which is a mail message log get from Office 365 with many users messages, I need to filter the columns, flatten and convert to CSV : 

createdDateTime,
receivedDateTime,
from_name
from_adress
To_name_1
To_adress_2
To_name_2
To_adress_2
...

The "from" field has only one data. But the "toRecipients" is a array.

{
  ...
  "createdDateTime": "xxxx-xx-xx",
  "receivedDateTime": "xxxx-xx-xx",
  "isRead": true,
  "from": {
    "emailAddress": {
      "name": "John",
      "adress": "john@onmicrosoftware.com"
    }
  },
  "toRecipients": [
    {
      "emailAddress": {
        "name": "Amy",
        "adress": "Amy@onmicrosoftware.com"
      }
    },
    {
      "emailAddress": {
        "name": "Amy",
        "adress": "Amy@onmicrosoftware.com"
      }
    }
  ]
}
Cbsch
  • 1,164
  • 2
  • 10
  • 16
Eric Chen
  • 53
  • 1
  • 8
  • Hi, if I understand correctly your filter works and you have trouble by exporting the results as CSV, right? Does this help you (?) : [convert-json-to-csv-using-powershell](https://stackoverflow.com/questions/43594860/convert-json-to-csv-using-powershell/43595214) - If you cannot use the solution mentioned in this Post I would try to create a second array and save the filtered json input by creating a new PSObject: `$obj = New-Object PSObject` – AzureBaum Jun 12 '20 at 08:22
  • Have a look at [CsvCruncher](https://github.com/OndraZizka/csv-cruncher), although that does not support going through nested arrays. – Ondra Žižka Nov 07 '21 at 04:19

1 Answers1

1

Here is a complete runnable example. It will create a file "C:\test.csv".

There is no "automatic" way of flattening a nested object to a flat object. But you can manually create assign properties to a flat object.

First I parse the JSON text into a powershell object

$obj = @"
{
  "createdDateTime": "xxxx-xx-xx",
  "receivedDateTime": "xxxx-xx-xx",
  "isRead": true,
  "from": {
    "emailAddress": {
      "name": "John",
      "adress": "john@onmicrosoftware.com"
    }
  },
  "toRecipients": [
    {
      "emailAddress": {
        "name": "Amy",
        "adress": "Amy@onmicrosoftware.com"
      }
    },
    {
      "emailAddress": {
        "name": "Amy",
        "adress": "Amy@onmicrosoftware.com"
      }
    }
  ]
}
"@ | ConvertFrom-Json

Now take the Powershell object (or list of objects, this will work even if you have many of these entries) and pipe it to ForEach-Object. Inside the loop map the different properties to a flat object.

$flattened = $obj | ForEach-Object {
    return [PSCustomObject]@{
        createdDateTime = $_.createdDateTime
        receivedDateTime = $_.receivedDateTime
        from_name = $_.from.emailAddress.name
        from_adress = $_.from.emailAddress.adress
        to_name_1 = $_.toRecipients[0].emailAddress.name
        to_adress_1 = $_.toRecipients[0].emailAddress.adress
        to_name_2 = $_.toRecipients[1].emailAddress.name
        to_adress_2 = $_.toRecipients[1].emailAddress.adress
    }
}

Now you can export the entire thing as a CSV

$flattened | Export-Csv C:\test.csv -Delimiter ";" -Encoding UTF8

This assumes that there will always be 2 toRecipients. It would be possible to dynamically add to_name_3, to_name_4, and so on if more are encountered, but that's quite a bit more complicated.

Cbsch
  • 1,164
  • 2
  • 10
  • 16
  • Thank you Cbsch for your proper reply, it's really helpful. Yes, that would be a problem and becoming too complicated to get torecipients dynamically. What if I just need to get All nested toRecipients array context to One column in CSV for further analytics? – Eric Chen Jun 14 '20 at 22:33
  • That would be more easy to do. `$_.toRecipients.emailAddress.adress -join ', '` will for example give you all the email addresses as one string value with the addresses separated by a comma. – Cbsch Jun 15 '20 at 06:15
  • Thank you very much Cbsch. I tried your suggested as above, 'To_EmailAddress' = $_.toRecipients.emailAddress.address -join ', ' but this returned weird as below, as others field returned perfectly "System.Object&, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 Address(int)" For alternative way, I just request to could return all raw data of 'toRecipients' to one CSV column, it also works for me if possible. Tons of thanks for your advice. – Eric Chen Jun 22 '20 at 08:33