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

You shouldn't call an object that comes out of ConvertFrom-Json "jsonstring", that's misleading. It's neither a string, nor is it JSON, for that matter. The JSON string is what Get-Content returns. After conversion it's an object, you should call it a sensible name - " servers" would work fine. Also, please always use the -Encoding parameter for Get-Content, because there is no magic file encoding detection and Get-Content will easily get it wrong. For most JSON files, specifying UTF-8 encoding is appropriate. The same applies to Set-Content. — Tomalak
– Tomalak, Commented May 14, 2018 at 10:50

Tomalak · Accepted Answer · 2021-09-23 16:21:55Z

6

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.

edited Sep 23, 2021 at 16:21

answered May 14, 2018 at 10:42

Tomalak

339k68 gold badges547 silver badges635 bronze badges

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

2 Comments

N. I. Over a year ago

For anyone reading this today: in the latest version of powershell, the colons must be replaced by equal signs (as in select @{n="Label"; e= {...}}).

Tomalak Over a year ago

@N.I. Good catch, thanks! The = has always worked, I've changed my answer accordingly.

Collectives™ on Stack Overflow

Extract nested fields from JSON using Select-Object

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related