Displaying Data to a certain Depth When Using Convertfrom-json

Question

Currently i'm attempting to create a script to convert my json files to csv files. At the moment I'm receiving most of the data, but the issue i'm having is expanding out the "glyphs" field to add that data into the csv as well. Below is an example of the json currently. This is similar to what's already out there, but my problem is with the -depth that the convertfrom-json pulls. It's only going to two levels and I need it to go to four.

{
  "nodes": [
    {
      "id": 23,
      "type": "Group",
      "label": "DOMAIN ADMINS@.COM",
      "glyphs": {
        "0": {
          "position": "top-right",
          "font": "\"Font Awesome 5 Free\"",
          "content": "",
          "fillColor": "black",
          "fontScale": 1.5,
          "fontStyle": "900"
        }
      },
      "folded": {
        "nodes": {},
        "edges": {}
      },

$user1 = $env:USERNAME
Get-Content C:\Users\$user1\Documents\json_to_convert.json |
convertfrom-json | select -ExpandProperty nodes |
Export-CSV C:\Users\$user1\Documents\jsonTest_$((Get-Date).ToString('MM-dd-yy')).csv -NoTypeInformation

So i'm just hoping to also input the "position", "font", "content", "fillColor", "fontScale", and "fontstyle" from "glyphs into my CSV file as well. Thos are the only fields i'm having trouble with.

Specify `-Depth`. By default, `Convert-*Json` only goes two levels deep. — Maximilian Burszley, Jul 11 '19 at 14:05
Possible duplicate of [Powershell convertfrom-json | convertto-csv](https://stackoverflow.com/questions/30485004/powershell-convertfrom-json-convertto-csv) — Maximilian Burszley, Jul 11 '19 at 14:05
Try `.... | ConvertFrom-Json | Flatten-Object | ConvertTo-Csv ...`. See: [Convert nested JSON array into separate columns in CSV file](https://stackoverflow.com/a/46081131/1701026) — iRon, Jul 11 '19 at 15:28
Possible duplicate of [Convert nested JSON array into separate columns in CSV file](https://stackoverflow.com/questions/45829754/convert-nested-json-array-into-separate-columns-in-csv-file) — iRon, Jul 11 '19 at 15:30

score 2 · Accepted Answer · answered Jul 11 '19 at 14:38

If you want to place the nested objects into separate CSV fields then you do have to think about how to represent that in a csv file. The below can be used to create custom properties that are calculated using an expression.

Get-Content C:\Users\$user1\Documents\json_to_convert.json | ConvertFrom-Json | Select -ExpandProperty nodes | `
Select -Property id,type,label, `
  @{Name='Glyphs-position';Expression={$_.glyphs.0 | Select -ExpandProperty position}},`
  @{Name='Glyphs-font';Expression={$_.glyphs.0 | Select -ExpandProperty font}},`
  @{Name='Glyphs-content';Expression={$_.glyphs.0 | Select -ExpandProperty content}},`
  @{Name='Glyphs-fillColor';Expression={$_.glyphs.0 | Select -ExpandProperty fillColor}},`
  @{Name='Glyphs-fontScale';Expression={$_.glyphs.0 | Select -ExpandProperty fontScale}},`
  @{Name='Glyphs-fontStyle';Expression={$_.glyphs.0 | Select -ExpandProperty fontStyle}}`
| ConvertTo-Csv

This could probably be written more efficiently but it demonstrates how to get the values you are after. The resultant PowerShell object produced is

id               : 23
type             : Group
label            : DOMAIN ADMINS@.COM
Glyphs-position  : top-right
Glyphs-font      : "Font Awesome 5 Free"
Glyphs-content   : 
Glyphs-fillColor : black
Glyphs-fontScale : 1.5
Glyphs-fontStyle : 900

and once converted to csv

"id","type","label","Glyphs-position","Glyphs-font","Glyphs-content","Glyphs-fillColor","Glyphs-fontScale","Glyphs-fontStyle","folded"
"23","Group","DOMAIN ADMINS@.COM","top-right","""Font Awesome 5 Free""","","black","1.5","900"

Displaying Data to a certain Depth When Using Convertfrom-json

1 Answers1