I'm trying to extract info from a JSON file via a ConvertFrom-Json command. Its format is:
{
"Id": "0",
"Name": "John",
"Actions": [
"@{ActionType=Add; ObjectId=abc-123}",
"@{ActionType=Delete; ObjectId=xyz-789}"
],
"IsSuccess": true
}
The end solution is a CSV:
Id | Name | ActionType | ObjectId |
---|---|---|---|
0 | John | Add | abc-123 |
0 | John | Delete | xyz-789 |
I've tried every variant of Hashtable/Array/Stringified Custom Object, but I can't extract the nested information. I'm surely missing something easy, but can anyone point me in the right direction?
As mentioned in the comments, this is a problem you need to solve where the original JSON document is produced in the first place.
The strings in the Actions
array are default string representations of custom objects, indicating that you didn't use a sufficiently high number for ConvertTo-Json
's -Depth
parameter:
@{
Actions = @(
[pscustomobject]@{
ActionType = 'Add'
ObjectId = 'abc-123'
}
)
} |ConvertTo-Json -Depth 1
Which produces:
{
"Actions": [
"@{ActionType=Add; ObjectId=abc-123}"
]
}
Whereas if we increase the value of the -Depth
parameter:
@{
Actions = @(
[pscustomobject]@{
ActionType = 'Add'
ObjectId = 'abc-123'
}
)
} |ConvertTo-Json -Depth 2
We get a valid JSON description of the whole object:
{
"Actions": [
{
"ActionType": "Add",
"ObjectId": "abc-123"
}
]
}
Go back to the script that fetches the data from the remote API and then specify a large value for -Depth
, and the problem with re-importing goes away:
$data = Invoke-RestMethod ...
# just set the -Depth parameter to a large value and you're good
$data |ConvertTo-Json -Depth 99 |Set-Content exported.json