Powershell ConvertTo-Json missing square brackets and adding new characters

Question

Whit the help of the contributors @mklement0 and @Theo, I could get good results with the solution explained in this question Powershell ConvertTo-Json problem with double-quotation marks.

But due to the requirements of the JSON-format, I have two more issues:

The values with the mac-and ip-addresses should be indicated between square brackets ([ ]) and each one should be indicated between (" "), e.g.:

"mac_address":  ["00:10:XX:10:00:0X", "X0:X0:11:X0:00:0X", "X0:11:X0:11:XX:11"]

In the file.txt, I will also have the following key-value information:

product_executable : "C:\Program Files (x86)\McAfee\VirusScan Enterprise\SHSTAT.EXE" /!REMEDIATE

I used the current code in the question mentioned above and I get double backslash and a new one at the beginning:

"product_executable":  "\"C:\\Program Files (x86)\\McAfee\\VirusScan Enterprise\\SHSTAT.EXE\" /!REMEDIATE"

I already tried to handle the hashtable output ($oht) without success as well as trying to modify add and delete characters.

My file.txt contains the following structured information (two empty lines at the beginning as well):



adapter_name          : empty1
route_age             : 10
route_nexthop         : 172.0.0.1
route_protocol        : NETMGMT1
speed                 : null 
mac_address           : 11:10:XX:10:00:0X, X1:X0:11:X0:00:0X, X1:11:X0:11:XX:11
product_executable    : "C:\Program Files (x86)\McAfee\VirusScan Enterprise\SHSTAT.EXE" /!REMEDIATE

adapter_name          : empty2
route_age             : 100
route_nexthop         : 172.0.0.2
route_protocol        : NETMGMT2
speed                 : null 
mac_address           : 22:10:XX:10:00:0X, X2:X0:11:X0:00:0X, X2:11:X0:11:XX:11
product_executable    : "C:\Program Files (x86)\McAfee\VirusScan Enterprise\SHSTAT.EXE" /!REMEDIATE

adapter_name          : empty3
route_age             : 1000
route_nexthop         : 172.0.0.3
route_protocol        : NETMGMT3
speed                 : null 
mac_address           : 33:10:XX:10:00:0X, X3:X0:11:X0:00:0X, X3:11:X0:11:XX:11
product_executable    : "C:\Program Files (x86)\McAfee\VirusScan Enterprise\SHSTAT.EXE" /!REMEDIATE

The current code (see question mentioned) is:

# Read the input file as a whole (-Raw) and split it into blocks (paragraphs)
(Get-Content -Raw C:\scripts\file.txt) -split '\r?\n\r?\n' -ne '' |
  ForEach-Object { # Process each block
    # Initialize an ordered hashtable for the key-values pairs in this block.
    $oht = [ordered] @{}
    # Loop over the block's lines.
    foreach ($line in $_ -split '\r?\n' -ne '') {
      # Split the line into key and value...
      $key, $val = $line -split ':', 2
      # ... and add them to the hashtable.
      $oht[$key.Trim()] = $val.Trim()
    }
    $oht # Output the hashtable.
  } | ConvertTo-Json

The actual result is:

    [
      {
        "adapter_name"         : "empty1",
        "route_age"            : 10,
        "route_nexthop"        : "172.0.0.1",
        "route_protocol"       : "NETMGMT1",
        "speed"                : null,
        "mac_address"          : "11:10:XX:10:00:0X, X1:X0:11:X0:00:0X, X1:11:X0:11:XX:11",
        "product_executable"   : "\"C:\\Program Files (x86)\\McAfee\\VirusScan Enterprise\\SHSTAT.EXE" /!REMEDIATE"
      },
      {
        "adapter_name"         : "empty2",
        "route_age"            : 100,
        "route_nexthop"        : "172.0.0.2",
        "route_protocol"       : "NETMGMT2",
        "speed"                : null,
        "mac_address"          : "22:10:XX:10:00:0X, X2:X0:11:X0:00:0X, X2:11:X0:11:XX:11",
        "product_executable"   : "\"C:\\Program Files (x86)\\McAfee\\VirusScan Enterprise\\SHSTAT.EXE" /!REMEDIATE"
      },
      {
        "adapter_name"         : "empty3",
        "route_age"            : 1000,
        "route_nexthop"        : "172.0.0.3",
        "route_protocol"       : "NETMGMT3",
        "speed"                : null,
        "mac_address"          : "33:10:XX:10:00:0X, X3:X0:11:X0:00:0X, X3:11:X0:11:XX:11",
        "product_executable"   : "\"C:\\Program Files (x86)\\McAfee\\VirusScan Enterprise\\SHSTAT.EXE" /!REMEDIATE"
      }
    ]

And the expected result is:

    [
      {
        "adapter_name"         : "empty1",
        "route_age"            : 10,
        "route_nexthop"        : "172.0.0.1",
        "route_protocol"       : "NETMGMT1",
        "speed"                : null,
        "mac_address"          :  ["11:10:XX:10:00:0X", "X1:X0:11:X0:00:0X", "X1:11:X0:11:XX:11"],
        "product_executable"   : ""C:\Program Files (x86)\McAfee\VirusScan Enterprise\SHSTAT.EXE" /!REMEDIATE"
      },
      {
        "adapter_name"         : "empty2",
        "route_age"            : 100,
        "route_nexthop"        : "172.0.0.2",
        "route_protocol"       : "NETMGMT2",
        "speed"                : null,
        "mac_address"          :  ["22:10:XX:10:00:0X", "X2:X0:11:X0:00:0X", "X2:11:X0:11:XX:11"],
        "product_executable"   : ""C:\Program Files (x86)\McAfee\VirusScan Enterprise\SHSTAT.EXE" /!REMEDIATE"
      },
      {
        "adapter_name"         : "empty3",
        "route_age"            : 1000,
        "route_nexthop"        : "172.0.0.3",
        "route_protocol"       : "NETMGMT3",
        "speed"                : null,
        "mac_address"          :  ["33:10:XX:10:00:0X", "X3:X0:11:X0:00:0X", "X3:11:X0:11:XX:11"],
        "product_executable"   : ""C:\Program Files (x86)\McAfee\VirusScan Enterprise\SHSTAT.EXE" /!REMEDIATE"
      }
    ]

I would really appreciate your suggestions.

score 2 · Answer 1 · answered Sep 22 '19 at 01:04

Strings in JSON can take escape sequences. The character for specifying an escape sequence is backslash \.

Escape sequences are useful for, among other things:

Inserting non-printable or whitespace characters (like TAB or newlines or null)
Inserting a double quote " into the string (since a double quote both begins and ends the string, you must have a way to say "I want this quote to be part of the string, not to terminate it).
Inserting a literal backslash \ (since backlsash is the beginning of an escape sequence, you need a way to say "I want this backlash to be part of the string, not to begin an escape sequence)

Therefore in your example, what you're seeing is:

"\"C:\\Program Files (x86)\\McAfee\\VirusScan Enterprise\\SHSTAT.EXE\" /!REMEDIATE"

In the beginning, you have a double quote " to start the JSON string.
Then immediately after you have \", which says "The first character in this string is an actual ""
In the paths, which are themselves delimited by a backslash \, you need it to be double so that it can be interpreted as a single backslash, instead of trying to interpret it as escape sequences \P rogram Files \M cAfee \V irusScan, etc.
At the end of SHSTAT.EXE you see the next \" which is putting in the literal quote that ends your quoted executable string.

Long story short, everything is working as expected. When you deserialize the JSON, it will all come out the way it should!

Want to see for sure?

$myString = @'
"C:\Program Files (x86)\McAfee\VirusScan Enterprise\SHSTAT.EXE" /!REMEDIATE
'@

Write-Host $myString

$myJsonString = $myString | ConvertTo-Json

Write-Host $myJsonString

$undoJson = $myJsonString | ConvertFrom-Json

Write-Host $undoJson

Hi @briantist! thanks a lot for your prompt support and explanation! It is very interesting for me to go into detail the topic. Sorry, due to a very long trip, I could not answer before.I am preparing the information achieved by powershell with the aim of being loaded in a web application. For that reason, all about the JSON-format is important for me. — Juan Diego, Sep 23 '19 at 23:06

score 1 · Answer 2 · answered Sep 22 '19 at 02:38

You'll need to convert the mac address to an array.

# Read the input file as a whole (-Raw) and split it into blocks (paragraphs)
(Get-Content -Raw C:\scripts\file.txt) -split '\r?\n\r?\n' -ne '' |
  ForEach-Object { # Process each block
    # Initialize an ordered hashtable for the key-values pairs in this block.
    $oht = [ordered] @{}
    # Loop over the block's lines.
    foreach ($line in $_ -split '\r?\n' -ne '') {
      # Split the line into key and value...
      $key, $val = $line -split ':', 2
      # ... and add them to the hashtable.
      if ($key -like "*mac_address*"){
        $val = @($val.Replace(' ','').split(','))
      }
      $oht[$key.Trim()] = $val.Trim()
    }
    $oht # Output the hashtable.
  } | ConvertTo-Json

Hi @Mike Frank! thank you for your prompt answer! Sorry, due to a very long trip, I could not answer before. Your code is also getting the expected result. Maybe if there would be more key-values pairs with ip- or mac-addresses, the code could be a little longer to know previously if the key has an ip- or mac-address value. But if I name all the keys that contain addresses with *_address*, I would be able to ask for all key-*address*es, for instance: *if ($key -like "**_address*")* — Juan Diego, Sep 23 '19 at 23:12

mklement0 · Accepted Answer · 2019-09-22T08:21:43.280

briantist's helpful answer shows that the product_executable property values are correctly JSON-encoded.

As for the desire to turn the mac_address into arrays of strings: all that is needed is to split the value by separator string , into an array:

That is, instead of:

$oht[$key.Trim()] = $val.Trim()

use

$oht[$key.Trim()] = $($val.Trim() -split ', ')

^{If there's a chance that the amount of whitespace between the elements is variable, use
-split ',\s*'}

The $(...) ensures that if the -split operation returns just 1 element - i.e., if the input doesn't contain , - the input string is returned as-is rather than as a single-element array.

The above assumes:

that all property values that contain , should be parsed as arrays
that if mac_address happens to contain just one entry, it should be parsed as a scalar.

The following variation applies array parsing only to property mac_address, and always parses its value as an array (you could again surround the -split operation with $(...) to change that):

$oht[$key.Trim()] = 
  if ($key.Trim() -eq 'mac_address') { $val.Trim() -split ', ' } else { $val.Trim() }

thank you again for your prompt answer! Sorry, due to a very long trip, I could not answer before. I just tested it and the code is very effective, also if other key-values pairs have multiple addresses. For other different reason and after some tests, I also had to specify the enconding type of the json file by adding *ConvertTo-Json | Out-File -Encoding utf8 C:\scripts\file.json*, because only with *ConvertTo-Json* is the format of the *file.json* utf16 defined. Just to mention if someone has other requirements to work with the encoding type as I realized it. — Juan Diego, Sep 23 '19 at 23:06
@JuanDiego: Glad to hear it. Character encoding comes into play only when you send output to a _file_, and, unfortunately, Windows PowerShell is wildly inconsistent in this respect (a problem that has been fixed in PowerShell _Core_, which now consistently defaults to BOM-less UTF-8) - see [this answer](https://stackoverflow.com/a/40098904/45375). — mklement0, Sep 24 '19 at 02:13

Powershell ConvertTo-Json missing square brackets and adding new characters

3 Answers3