Powershell ConvertFrom-Json array string to separate rows

Question

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

mclayton · Accepted Answer · 2021-03-19 08:27:24Z

1

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.

edited Mar 19, 2021 at 8:27

answered Mar 18, 2021 at 21:53

mclayton

10.4k3 gold badges26 silver badges36 bronze badges

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

11 Comments

Doug Maurer Over a year ago

This is an ideal opportunity to use pipelinevariable. Remove $Group = $_ and add -PipelineVariable Group to ConvertFrom-Json

wd510 Over a year ago

Thanks, getting close. For the GroupName and GroupID columns, I'm getting a "System.Object[]" result for each cell.

mclayton Over a year ago

@wd510 - that implies your GroupName is more like "GroupName": ["A"] than "GroupName": "A" - check the json for the affected group objects and make sure it matches the format in your sample...

wd510 Over a year ago

I doubled-checked it and the format i had in my sample is right, there aren't any brackets around the value for GroupName and GroupID, just quotes. Only brackets are around the string of values for GroupMemberID.

mclayton Over a year ago

@DougMaurer - Nice. every now and then I come across another feature of PowerShell I've been blissfully unaware of, and today's one of those days :-).

|

Collectives™ on Stack Overflow

Powershell ConvertFrom-Json array string to separate rows

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related