Powershell ConvertFrom-Json array string to separate rows

user_15427466 0
Viewed 140

I have a json file that I'm trying to convert to csv using the converfrom-json command. I'm having trouble parsing one part of the array string into separate rows. Here's an example:

[
  {
    "GroupName": "A",
    "GroupID": "G001",
    "GroupMemberIds": [
      "M001",
      "M002",
      "M003",
      "M004"
    ]
  },
  {
    "GroupName": "B",
    "GroupID": "G002",
    "GroupMemberIds": [
      "M001",
      "M004",
      "M005",
      "M006"
    ]
  }
]

My desired output to csv is:

GroupName GroupID GroupMemberId
A G001 M001
A G001 M002
A G001 M003
A G001 M004
B G002 M001
B G002 M004
B G002 M005
B G002 M006

I started with the following and tried different iterations but haven't found a solution yet. any help is greatly appreciated.

$obj = Get-Content -Path "C:\Temp\File.json" | ConvertFrom-Json $obj | select groupname, groupid, memberid | export-CSV "C:\Temp\groupmembers.csv" -NoTypeInformation

here's a sample file: https://drive.google.com/file/d/18WErwuOlU-4068852hsb9S8n9Bs52ndu/view?usp=sharing

powershell json
1 Answers
Get-Content -Path "C:\Temp\File.json" | ConvertFrom-Json | ForEach-Object {
    $group = $_;
    $_.GroupMemberIds | ForEach-Object {
        new-object PSCustomObject -Property ([ordered] @{
            "GroupName"     = $group.GroupName
            "GroupId"       = $group.GroupId
            "GroupMemberId" = $_
        })
    }
} | Export-CSV "C:\Temp\groupmembers.csv" -NoTypeInformation

The first ForEach-Object loops over the top-level "group" objects, and the nested ForEach-Object loops over each of the entries in its "GroupMemberIds" array and outputs a new object with the GroupName, GroupId, GroupMemberId properties, and these are what get converted to a csv file.

PS> Get-Content "C:\temp\groupmembers.csv"
"GroupName","GroupId","GroupMemberId"
"A","G001","M001"
"A","G001","M002"
"A","G001","M003"
"A","G001","M004"
"B","G002","M001"
"B","G002","M004"
"B","G002","M005"
"B","G002","M006"

Update - the above works in PowerShell Core, but not PowerShell 5.1 due to some differences between the way ConvertTo-Json outputs array objects - see https://github.com/PowerShell/PowerShell/issues/3424 for one example.

A workaround is to put wrap the ConvertFrom-Json in a Grouping Operator (basically ( ... )):

(Get-Content -Path "C:\Temp\File.json" | ConvertFrom-Json) | ForEach-Object {
    $group = $_;
    $_.GroupMemberIds | ForEach-Object {
        new-object PSCustomObject -Property ([ordered] @{
            "GroupName"     = $group.GroupName
            "GroupId"       = $group.GroupId
            "GroupMemberId" = $_
        })
    }
} | Export-CSV "C:\Temp\groupmembers.csv" -NoTypeInformation

and then it should work.

user_31569060
Related