Powershell convert to Json is bad format

Question

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?

Answer 1

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)

Answer 2

• 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.