CODEWITHSUNDEEP
jsonpowershell
mystack
mystack

Reputation: 5532

Read Json string using Powershell

I have a json string as given below.

$json='        {
    "code":  0,
    "ms":  "success",
    "dt":  {
             "st":  1,
             "mns":  [
                              "@{name=product 1 - w; att=response_time; st=1; time=2020-08-21T14:18:03-0400; locations=System.Object[]; attN=RESPONSETIME; mt=PORT; attL=Response Time; attribute_value=67; unit=ms; monitor_id=12}",
                              "@{name=product 2 - w; att=response_time; st=1; time=2020-08-21T14:18:03-0400; locations=System.Object[]; attN=RESPONSETIME; mt=PORT; attL=Response Time; attribute_value=70; unit=ms; monitor_id=23}",
                              "@{name=product 3 - w; att=response_time; st=1; time=2020-08-21T14:18:03-0400; locations=System.Object[]; attN=RESPONSETIME; mt=PORT; attL=Response Time; attribute_value=66; unit=ms; monitor_id=24}",
                              "@{st=5; name=product 4 - w; mt=PORT; monitor_id=35}"
                          ],
             "gd":  "12345",
             "gn":  "group_name 5 - w"
         }
}'

I tried to read the status value given under "mns" using the below code

    $jsonobject= ConvertFrom-Json -InputObject $json
    foreach ($mn in $jsonobject.dt.mns)
     {
      $mndata=ConvertFrom-StringData -StringData $mn
      Write-Host $mndata["st"]
     }

But i'm not able to retrieve the value of $mndata["st"] .

Upvotes: 1

Views: 539

Answers (4)

Aditya Nair
Aditya Nair

Reputation: 572

Try this:

$jsonobject= $json | ConvertFrom-Json
foreach ($mn in $jsonobject.dt.mns)
{
    $stringToBeLocated = "st="
    $mnStValueIndex = $mn.IndexOf($stringToBeLocated)
    $mnStValue = $mn.Substring($mnStValueIndex+$stringToBeLocated.Length, 1)
    Write-Output $mnStValue
}

Output:

enter image description here

Upvotes: 0

Cid
Cid

Reputation: 15257

Your problem is that ConvertFrom-StringData works with key-value pairs separated by a new line. On each iterations, your only value is (for the first index) key : @{name, value : product 1 - w; att=response_time; st=1; time=2020-08-21T14:18:03-0400; locations=System.Object[]; attN=RESPONSETIME; mt=PORT; attL=Response Time; attribute_value=67; unit=ms; monitor_id=12}.

I don't know the @{key1=value1; key2=value2} format, but you can modify it to get a usable format. $mn.trim("@{}") should do the job to remove the surrounding @{ and }.

Then, you want to use newlines instead of ; so ConvertFrom-StringData can correctly convert the datas. You can use .Replace(";", [Environment]::NewLine)

foreach ($mn in $jsonobject.dt.mns)
 {
     $mndata = ConvertFrom-StringData -StringData $mn.trim("@{}").Replace(";", [Environment]::NewLine)
     Write-Host $mndata["st"]
 }

This outputs :

1

1

1

5

Upvotes: 0

Daniel Björk
Daniel Björk

Reputation: 2507

Since the mns is not proper json you need to treat it like a string.

$jsonobject= ConvertFrom-Json -InputObject $json

foreach ($mn in $jsonobject.dt.mns)
{
    Write-Output ($mn | Select-String -Pattern 'st=(\d+)').Matches.Groups[1].Value
}

Upvotes: 1

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174900

ConvertFrom-StringData expects key-value pairs on separate lines, so you will have to re-format the input string a bit first:

foreach ($mn in $jsonobject.dt.mns)
{
  $multiLine = $mn.Trim('@{};') -replace ';\s*',"`r`n"
  $mndata = ConvertFrom-StringData -StringData $multiLine
  Write-Host $mndata["st"]
}

Given the format of the strings (and the locations=System.Object[] pair), it appears that the original JSON might have been produced by ConvertTo-Json with an insufficient -Depth parameter, you might have an easier time solving this problem by going back and fixing that wherever you get this data from

Upvotes: 2

Related Questions