Flattening a Sub-Array in a Powershell Object, including parent object property

Question

Given the following JSON

[
  {
    "key": "James",
    "things": [
      {
        "id": 123,
        "name": "PRD"
      },
      {
        "id": 124,
        "name": "PRE"
      }
    ]
  },
  {
    "key": "Susan",
    "things": [
      {
        "id": 125,
        "name": "PRF"
      },
      {
        "id": 126,
        "name": "PRG"
      }
    ]
  }
]

Which, I've easily converted into a Powershell object:

$json = '[{"key":"James", "things":[{"id":123,"name":"PRD"},{"id":124,"name":"PRE"}]},{"key":"Susan", "things":[{"id":125,"name":"PRF"},{"id":126,"name":"PRG"}]}]'

$obj = $json | ConvertFrom-Json

How do I flatten the sub-array things and include the key from the parent object, so that my result set is

key    id name
---    -- ----
James 123 PRD  
James 124 PRE  
Susan 125 PRF
Susan 126 PRG

I've used the following to flatten the sub-array:

$obj | % { $_.things}

Which returns me

 id name
 -- ----
123 PRD  
124 PRE  
125 PRF
126 PRG

But, I can't quite figure out what to do next.

Any help would be much appreciated.

FYI, in case you didn't knew $obj | % { $_.things} can be replaced by $obj.things . Powershell will do its thing, enumarate the things and produce the same output in either cases. — Sage Pourpre
– Sage Pourpre, Commented Nov 26, 2021 at 9:55

Sage Pourpre · Accepted Answer · 2021-11-26 09:40:19Z

2

You loop into each key, then loop into each things, since you want 1 result per thing, and build a PSObject using the current key, id and name.

Here you go.

# initial code sample
$json = '[{"key":"James", "things":[{"id":123,"name":"PRD"},{"id":124,"name":"PRE"}]},{"key":"Susan", "things":[{"id":125,"name":"PRF"},{"id":126,"name":"PRG"}]}]'
$obj = $json | ConvertFrom-Json 

# Loop needed to flatten the object.
foreach ($i in $obj) {
    foreach ($t in $i.things) {
        [PSCustomObject]@{
            key  = $i.key
            id   = $t.id
            name = $t.name
        }
    }
}

Output

key    id name
---    -- ----
James 123 PRD
James 124 PRE
Susan 125 PRF
Susan 126 PRG

answered Nov 26, 2021 at 9:40

Sage Pourpre

10.4k3 gold badges30 silver badges43 bronze badges

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

3 Comments

Mutation Person Over a year ago

Hmmmm "Cannot convert value to type "System.Management.Automation.LanguagePrimitives+InternalPSCustomObject". Only core types are supported in this language mode."

Sage Pourpre Over a year ago

@JamesWiseman That is because you are working in constrained mode. As far as I know, this mode is very limited in what you can do. You might be able to do a pipeldouble pipeline for each with the use of pipeline variable and produce your output through a select statement that use a calculated property for the key... Not sure if calculated properties work in constrained mode though. I'll check when I am back in front of the PC.

Mutation Person Over a year ago

Turns out it was my local windows security policy. Running the Script in Powershell ISE with elevated 'Admin' privs fixed this. Interesting thought experiment though, achieving this with more primitive powershell.

Chalky · Accepted Answer · 2022-01-25 22:53:12Z

1

you can use built-in flattening with select as follows:

($json | ConvertFrom-Json) | Select-Object key -ExpandProperty things

answered Jan 25, 2022 at 22:53

Chalky

1,65218 silver badges21 bronze badges

Collectives™ on Stack Overflow

Flattening a Sub-Array in a Powershell Object, including parent object property

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related