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?

This post seems to also be related to https://stackoverflow.com/questions/24789365/prettify-json-in-powershell-3. — Kody, Aug 02 '19 at 15:52
If you're just looking for single-line, no-extra-whitespace output, use `ConvertTo-Json -Compress`. — mklement0, Aug 02 '19 at 15:59
Possible duplicate of [PowerShell JSON adding value formate](https://stackoverflow.com/questions/50826637/powershell-json-adding-value-formate) — Theo, Aug 02 '19 at 18:33

Kody · Answer 1 · 2019-08-02T22:17:33.157

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)

+1 for pointing out that it's improved in v6 and later. I switched to Powershell Core and it was perfect! — oatsoda, Aug 24 '20 at 15:22

score 3 · Accepted Answer · answered Aug 02 '19 at 19:18

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.

score 0 · Answer 3 · answered Aug 02 '19 at 15:55

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

Powershell convert to Json is bad format

3 Answers3

Linked