CODEWITHSUNDEEP
jsonsortingpowershell
user2926144
user2926144

Reputation: 35

Sorting json response using powershell scripting

I am making a REST API call and getting a json response in return. I want to get the highest string from the response. 

$x = Invoke-RestMethod -Method Get -Uri $r

This returns something like this 

    {
  "results": [
    {
      "uri": "h//foo/test.1.0.19.4.n"
    },
    {
      "uri": "h://foo/test.1.0.20.6.n"
    },
    {
      "uri": "h://foo/test.1.0.20.7.n"
    }
  ]
}

From this I want to extract "test.1.0.20.7.n", this the largest when compared between .19 and .20 and when I compare the last one i want .7 over .6

I have been trying to use 

$X| Select uri |  Sort-Object uri

it just prints 

test...
test...
test...

Upvotes: 2

Views: 3251

Answers (2)

mklement0
mklement0

Reputation: 438093

To provide an alternative to Remko's answer that:

  • Uses type [version] to achieve the desired sorting:

    • [version] performs the appropriate component-by-component comparison for sorting.
    • Aside from being a more direct implementation of the underlying concept, this has the advantage of not having to worry about overflowing an integer data type.

  • Extracts only the substring of interest, such as test.1.0.20.7.n

# Parse sample JSON input into a PS custom object.
$objFromJson = @'
    {
  "results": [
    {
      "uri": "h://foo/test.1.0.19.4.n"
    },
    {
      "uri": "h://foo/test.1.0.20.7.n"
    },
    {
      "uri": "h://foo/test.1.0.20.6.n"
    }
  ]
}
'@ | ConvertFrom-Json

# Sort the results by the version-number-like tokens embedded in the `uri`
# property of the elements of the collection stored in the `.result` property,
# output the one with the highest version number, then remove
# the URL path prefix with `Split-Path`
$objFromJson.results | 
 Sort-Object -Descending {[version] ($_.uri -replace '^.+(\d+\.\d+\.\d+\.\d+).+$', '$1')} |
   Select-Object -First 1 |
     Split-Path -Leaf -Path { $_.uri }

The above yields:

test.1.0.20.7.n

In the -replace operation above:

  • regex ^.+(\d+\.\d+\.\d+\.\d+).+$ matches the entire URI and captures the version-like part (e.g., 1.0.19.4) in a capture group,
  • whose value ($1) is then used as the replacement string, which effectively returns just the version-like part, which the [version] cast then converts to that type.

@Remko suggests making the regex more flexible, in case you need to match version-like strings with fewer components. In practice, casting from a string to [version] is limited to 2 to 4 components (e.g., strings 1.2, 1.2.3, and 1.2.3.4 work, but 1 and 1.2.3.4.5 don't), which you can model with the following regex:
'^.+((\d+\.){1,3}\d+).+$'

If your version-like strings have more components, you must implement your own sorting (Remko's answer may work, if the numbers that result from interpreting all digits in the version-like string as a single integer don't grow too large).

Upvotes: 1

Remko
Remko

Reputation: 7340

You can do this by leveraging "custom" sort with an Expression as I blogged about earlier. Following sample works for me:

$json = @"
    {
  "results": [
    {
      "uri": "h://foo/test.1.0.19.4.n"
    },
    {
      "uri": "h://foo/test.1.0.20.7.n"
    },
    {
      "uri": "h://foo/test.1.0.20.6.n"
    }
  ]
}
"@

$obj = ConvertFrom-Json $json

$obj.results | Sort-Object -Property @{Expression={[int]($_.uri -replace "\D", "")}} | select -Last 1

Output:

uri                    
---                    
h://foo/test.1.0.20.7.n

And with this dataset:

    {
  "results": [
    {
      "uri": "h://foo/test.1.0.19.4.n"
    },
    {
      "uri": "h://foo/test.1.1.5.7.n"
    },
    {
      "uri": "h://foo/test.1.2.20.1.n"
    },
    {
      "uri": "h://foo/test.1.0.20.7.n"
    },
    {
      "uri": "h://foo/test.1.0.20.6.n"
    }
  ]
}

The result is: 

uri                    
---                    
h://foo/test.1.2.20.1.n

Upvotes: 0

Related Questions