Extract a property from type JSON property

Question

I have some Azure usage data in the structure as returned from the Azure management cmdlet Get-UsageAggregates. This has some information in a property of type Json, from which I want to extract a sub-property.

These data are contained in variable $usage. If I execute

$usage.UsageAggregations.properties | 
    select-object metercategory,metersubcategory,unit,quantity
                 ,@{Name="resource"; Expression={$_.InstanceData }}

I get a list of objects like this:

MeterCategory    : Storage
MeterSubCategory : Geo Redundant
Unit             : GB
Quantity         : 3.76344
resource         : {"Microsoft.Resources":{"resourceUri":"/subscriptions/[GUID]/resourceGroups/default-storage-
                   northeurope/providers/Microsoft.ClassicStorage/storageAccounts/MyAccountName","location":"eunorth"}}

I would like to see the "MyAccountName" instead of the JSON string. How can I do that?

I tried to use

$usage.UsageAggregations.properties | 
    select-object metercategory,metersubcategory,unit,quantity
                 ,@{Name="resource"; 
                    Expression={$_.InstanceData | 
                                ConvertFrom-Json | 
                                select-object -Property Microsoft.Resources.resourceuri }}

But the output is

MeterCategory    : Storage
MeterSubCategory : Geo Redundant
Unit             : GB
Quantity         : 3.76344
resource         :  @{Microsoft.Resources.resourceuri=}

Adding .value after resourceuri also did not help.

Answer 1

The gist of your question is how to extract the string MyAccountName from the following JSON string (pretty-printed and simplified):

$json = @'
{
  "Microsoft.Resources": {
    "resourceUri":  "/subscriptions/[GUID]/resourceGroups/.../MyAccountName",
    "location":  "eunorth"
  }
}
'@

Combine ConvertFrom-Json with the -split operator:

(($json | ConvertFrom-Json).'Microsoft.Resources'.resourceUri -split '/')[-1]

• ConvertFrom-Json converts the JSON string into a custom object with a top-level property Microsoft.Resources property, which in turn is a custom object with properties resourceUri and location.

• .'Microsoft.Resources'.resourceUri therefore returns the value of the resourceUri JSON property - note the need to quote 'Microsoft.Resources' so as to ensure that its embedded . is considered part of the property name rather than as a separator between property names.

• -split '/' splits the value into tokens by / and returns them as an array.

• [-1] references the last element of that array, which is MyAccountName in this case.

---

As for what you tried:

Select-Object doesn't support property paths, only top-level property names.
In other words, you can only extract top-level properties from input objects; you cannot drill down into them.

Therefore, you can extract top-level property Microsoft.Resources - whose name just happens to contain a . itself - but not also its resourceUri property.

If you attempt to do so, the whole argument is considered a top-level property name literally named Microsoft.Resources.resourceUri.
If the input object has no such property, the property is created with a value of $null for the output object.^[1]

---

Generally, to make Select-Object extract a property value - as opposed to the default behavior of returning a custom object with that property, you must use -ExpandProperty instead of -Property.

Note that if you had used -ExpandProperty, the problem would have become more obvious, because PowerShell then reports an error if a (top-level) property by the given name cannot be found^[1]:

> $json | ConvertFrom-Json | Select-Object -ExpandProperty Microsoft.Resources.resourceUri
Select-Object : Property "Microsoft.Resources.resourceUri" cannot be found.
...

---

^{[1]Curiously, though, if you attempt to quote a component of a .-separated string, Select-Object simply passes the input object through - see this GitHub issue.}