Read Json string using Powershell

Question

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"] .

Your problem is that your mns records is strings and not Json. So either your parse it as a string or you update it to be proper json format. — Daniel Björk
– Daniel Björk, Commented Aug 24, 2020 at 8:12
I cannot update the json because it is a WEBREQUEST response. What you meant by parse as a string. You mean to use some REGEX?. I — mystack
– mystack, Commented Aug 24, 2020 at 8:18
@tjdoubts How did you obtain it? Using Invoke-WebRequest/Invoke-RestMethod? Please show us the exact command you run (sans URL) to get that exact JSON string — Mathias R. Jessen
– Mathias R. Jessen, Commented Aug 24, 2020 at 8:34

Mathias R. Jessen · Accepted Answer · 2020-08-24 08:19:36Z

2

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

answered Aug 24, 2020 at 8:19

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel Björk · Accepted Answer · 2020-08-24 08:20:29Z

1

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
}

answered Aug 24, 2020 at 8:20

Daniel Björk

2,5171 gold badge21 silver badges28 bronze badges

Comments

Cid · Accepted Answer · 2020-08-24 08:23:43Z

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

Aditya Nair · Accepted Answer · 2020-08-24 08:47:51Z

0

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:

answered Aug 24, 2020 at 8:47

Aditya Nair

5921 gold badge10 silver badges21 bronze badges

Collectives™ on Stack Overflow

Read Json string using Powershell

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related