powershellextract

Can't Extract Hashtable/Stringified Custom Values from Powershell


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?


Solution

  • 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