CODEWITHSUNDEEP
jsonpowershellansible
Marvelous
Marvelous

Reputation: 361

Powershell convert to Json is bad format

I am using win_shell to convert powershell output to json format, so that i can filter it later. The problem is i am getting bad Json format.

Here is the code

    - win_shell: |
         Get-ChildItem -Path <some_path> |
         Where-Object {$_.PSIsContainer} | Sort-Object LastWriteTime -Descending |
         Select-Object -First 20 | ConvertTo-Json
         register: register_results

     - debug:
         var: register_results

The stdout lines i am getting is not clean to be used in a json filter: 

  "stderr": "",
  "rc": 0,
  "stdout_lines": [
      "[",
      "    {",
      "        \"Name\":  \"976\",",
      "  \"FullName\"\"F:\\\\some\\\\path\\\\to\\\\folder\\\\976\",",
      "  \"Parent\":  {",
      "                       \"Name\":  \"first\",",
      "                       \"Parent\":  \"All\",",
      "                       \"Exists\":  true,",
      "                       \"Root\":  \"F:\\\\\",",
      "                       \"Extension\":  \"\",",
      etc...

Those extra whitespaces cause errors when i try to filter for example "parent" or "Name". Looks like there must be other parameter beside "ConvertToJson"to get the output cleaner.

Is there anyway to do that?

Upvotes: 3

Views: 12426

Answers (3)

Kody
Kody

Reputation: 975

According to this post, the JSON formatting for ConvertTo-Json is planned to be improved in PowerShell 6. You can override the formatting yourself after ConvertTo-Json like the post suggests. Some code from the post mentioned to potentially solve your issue:

# Formats JSON in a nicer format than the built-in ConvertTo-Json does.
function Format-Json([Parameter(Mandatory, ValueFromPipeline)][String] $json) {
  $indent = 0;
  ($json -Split '\n' |
    % {
      if ($_ -match '[\}\]]') {
        # This line contains  ] or }, decrement the indentation level
        $indent--
      }
      $line = (' ' * $indent * 2) + $_.TrimStart().Replace(':  ', ': ')
      if ($_ -match '[\{\[]') {
        # This line contains [ or {, increment the indentation level
        $indent++
      }
      $line
  }) -Join "`n"
}

$obj = @{}
$json = $obj | ConvertTo-Json | Format-Json

Alternatively, you should be able to use ConvertTo-JsonNewtonsoft or Newtonsoft.Json directly by installing and importing the module and use that instead of ConvertTo-Json...

Install-Module Newtonsoft.Json
Import-Module Newtonsoft.Json

$obj = @{}
$json = $obj | ConvertTo-JsonNewtonsoft

# or Newtonsoft.Json directly (same code)

$obj = @{}
$json = [Newtonsoft.Json.JsonConvert]::SerializeObject($obj, [Newtonsoft.Json.Formatting]::Indented)

Upvotes: 6

mklement0
mklement0

Reputation: 439193

  • What ConvertTo-Json outputs isn't bad JSON, it is pretty-printed JSON:

    • Pretty-printed JSON uses multi-line output with whitespace-based indentation for better readability.

    • Pretty-printed JSON is still valid JSON, however, and any JSON parser should recognize it.

  • You can opt out of this pretty-printing with the -Compress switch, for a more efficient, but less readable representation:

    • You'll get a single-line output string (even for multiple inputs), with no extraneous whitespace.

The output you're showing shows the pretty-printed JSON string embedded inside another JSON string, as a string property value (hence the escaping of the embedded " as \").

Therefore, in order to process such embedded JSON, you must:

  • parse the containing JSON
  • get the value of the property that contains the embedded JSON (<parsedContainingJson>.stdout_lines)
  • then parse that.

Given that whatever produces the containing JSON broke the multi-line ConvertTo-Json output string into an array of lines (as also suggested by property name stdout_lines), you'd first have to join the array elements back into a single string before processing them as JSON.

If you want to avoid that step, use ConvertTo-Json -Compress.

Upvotes: 4

general-gouda
general-gouda

Reputation: 318

Outputting it to a file ought to make it easier to read back in correctly rather than trying to copy for the host output. It really depends on what language you are trying to use to filter on later.

Get-ChildItem -Path <some_path> |
         Where-Object {$_.PSIsContainer} | Sort-Object LastWriteTime -Descending |
         Select-Object -First 20 | ConvertTo-Json | Out-File C:\Some\Where\Awesome\OutputJson.json

You could also export it to clixml if you are going to use PowerShell to do the filtering. That will help PowerShell pull it in as a recognizable PS object.

Get-ChildItem -Path "C:\Scripts" |
         Where-Object {$_.PSIsContainer} | Sort-Object LastWriteTime -Descending |
         Select-Object -First 20 | Export-Clixml C:\Some\Where\Awesome\Exported.xml

Upvotes: 0

Related Questions