Extract nested fields from JSON using Select-Object

Question

I have a JSON file which I converted using ConvertFrom-Json and I want selected fields from it. I tried to pipeline the output to Select-Object but I was able to get the name and not other details.

Below is my output of ConvertFrom-Json and my script.

display_name   : TEL05
name           : TEL05
is_muted       : False
meta           : @{agent_checks=System.Object[]; timezones=System.Object[]; winV=System.Object[]; machine=AMD64; platform=win32; 
                 gohai={"cpu":{"cpu_cores":"12","cpu_logical_processors":"24","family":"6","mhz":"2497","model":"63","model_name":"Intel(R) 
                 Xeon(R) CPU E5-2680 v3 @ 2.50GHz","stepping":"2","vendor_id":"GenuineIntel"},"filesystem":[{"kb_size":"358396","mounted_on":
                 "","name":"\\\\?\\Volume{00241358-f193-11e7-80b3-806e6f6e6963}\\"},{"kb_size":"104499196","mounted_on":"C:\\","name":"\\\\?\
                 \Volume{00241359-f193-11e7-80b3-806e6f6e6963}\\"},{"kb_size":"Unknown","mounted_on":"D:\\","name":"\\\\?\\Volume{0024135d-f1
                 93-11e7-80b3-806e6f6e6963}\\"}],"gohai":{"build_date":"Mon Jun  5 18:30:34 GMT 
                 2017","git_branch":"last-stable","git_hash":"7de20ed","go_version":"go version go1.6.4 windows/amd64"},"memory":{"total":"34
                 261516288"},"network":{"ipaddress":"10.13.52.15","ipaddressv6":"fe80::dcf6:212:7ce0:8feb%16","macaddress":"44-A8-42-3A-9D-E9
                 "},"platform":{"GOOARCH":"amd64","GOOS":"windows","goV":"1.6.4","hostname":"TELXCVOIP04","kernel_name":"Windows","kernel_rel
                 ease":"6.3.9600","machine":"x86_64","os":"Windows Server 2012 R2 Standard","pythonV":"2.7.12"}}; host_id=363974563; 
                 pythonV=2.7.12; processor=Intel64 Family 6 Model 63 Stepping 2, GenuineIntel; agent_version=5.14.0}
host_name      : TEL05
has_metrics    : True

I want host name and agent_version (inside meta tag). how do I achieve this?

I tried the below but it did not work.

$jsonstring = (Get-Content 'servers.JSON') | ConvertFrom-Json
$jsonstring.rows| select name, meta.agent_version

Answer 1

You cannot use "dot notation" to index into nested hashes with Select-Object - only actual keys are supported, and meta.agent_version does not exist as its own key.

Select-Object can calculate new values for you, though, and traversing a nested object is a calculation. For this you can use the @{ name="new propery name"; expression={ script block } } notation, which can be abbreviated to @{ n="propery name"; e={ script block } }.

You can freely mix this form with actually existing properties, so this would work:

$jsonstring.rows | select name, @{n="AgentVersion"; e={ $_.meta.agent_version } }

The $_ refers to the context object, just like in the Foreach_Object cmdlet.

Be aware that "dot notation" like $_.meta.agent_version will generally select all values matching this path, which means it can return an array if more than one value matches. Not an issue in this particular case, but easily overlooked when using dot notation on nested objects.