Powershell - Search json tree

Question

I have a json structure in a file my.json that looks similar to below:

{
   "key1":{
      "key1A":{
         "someKey":"someValue"
      },
      "key1B":{
         "someKey":"someValue"
      }
   },
   "key2":{
      "key2A":{
         "someKey":"someValue"
      },
      "key2B":{
         "someKey":"someValue"
      }         
   },       
   "key3":{
      "key3A":{
         "someKey":"someValue"
      },
      "key3B":{
         "someKey":"someValue"
      }
   }
}

Using powershell I would like to search for "node" key1B and select it. The assumptions are

key1B only occurs once in the whole tree
The search term (key1B) can either be a root element or a child of a root element.

If I were to know the parent under which node key1B is I could use below:

Get-Content -Raw my.json | ConvertFrom-Json | Select-Object -ExpandProperty key1 | Select-Object -ExpandProperty key1B

How can I make above select generic so that the node I'm searching for can be either in the root or a child of the root?

Your JSON isn't syntactically correct, `"key1"` and `"key2"` both have no closing `}` — Nico Nekoru, Jun 23 '20 at 18:55
Try `((Get-Content -Raw my.json | ConvertFrom-Json) | gm | ? {$_.Definition -imatch "key1A"}).name` This will get the parent node of `key1A` — Nico Nekoru, Jun 23 '20 at 19:03
Was able to grab content of `key1a` with your JSON as `$testJson` like so: ```$($testJson | ConvertFrom-Json).$((($testJson | ConvertFrom-Json) | gm | ? {$_.Definition -imatch "key1A"}).name).key1a``` Very convuluted but would be easier if I had a separate variable with the actual converted json, proof of concept though — Nico Nekoru, Jun 23 '20 at 19:09

score 2 · Answer 1 · edited Jun 24 '20 at 03:27

2

jq has a recursive descent .. operator that can search all the keys like xpath //.

get-content file.json | jq '.. | .key1B? | select(. != null)'

{
  "someKey1b": "someValue1b"
}

edited Jun 24 '20 at 03:27

7cc

1,149
4
10

answered Jun 23 '20 at 19:23

js2010

23,033
6
64
66

Is `jq` an alias? My system cannot find `jq` – Nico Nekoru Jun 23 '20 at 19:29
@NekoMusume No it's an external command to download. – js2010 Jun 23 '20 at 19:30

score 2 · Answer 2 · answered Jun 24 '20 at 03:23

2

# convert as a Hashtable
$json = Get-Content -Raw .\my.json | ConvertFrom-Json -AsHashtable

# a root element
$json.key1B

# a child of a root element
$json.Values.key1B

# descendant
function findByKey {
  param($json, $key)
  if ($json.$key) { $json.$key }
  else {
    $json.Values | ? values | % { findByKey $_ $key } | select -First 1
  }
}

findByKey $json "key1B"

answered Jun 24 '20 at 03:23

7cc

1,149
4
10

1

The only thing is it requires powershell 7. – js2010 Jun 24 '20 at 04:12

score 1 · Answer 3 · answered Jun 23 '20 at 19:27

You can find Key1A or whatever key you are looking for by looking at the definition column of Get-Member.

Let's define your JSON As variable $TestJson:

$testJson = @"
{
   "key1":{
      "key1A":{
         "someKey":"someValue"
      },
      "key1B":{
         "someKey":"someValue"
      }
   },
   "key2":{
      "key2A":{
         "someKey":"someValue"
      },
      "key2B":{
         "someKey":"someValue"
      }
   },         
   "key3":{
      "key3A":{
         "someKey":"someValue"
      },
      "key3B":{
         "someKey":"someValue"
      }
   }
} 
"@
$testJson = $testJson | ConvertFrom-Json

We are looking for Key1A in $testJson which we do not know is under the parent node key1, we can do this by looking at the output of $testJson | gm

$testJson | gm


   TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition
----        ----------   ----------
Equals      Method       bool Equals(System.Object obj)
GetHashCode Method       int GetHashCode()
GetType     Method       type GetType()
ToString    Method       string ToString()
key1        NoteProperty System.Management.Automation.PSCustomObject key1=@{key1A=; key1B=}
key2        NoteProperty System.Management.Automation.PSCustomObject key2=@{key2A=; key2B=}
key3        NoteProperty System.Management.Automation.PSCustomObject key3=@{key3A=; key3B=}

We can see here that all of the nodes and their sub-nodes are listed in the definitions tab, with bigger JSONs, we wouldn't be able to see the whole definitions tab with this so we could wither to one of these two things:

$testJson | gm | select-object "Definition"
($testJson | gm).Definition

So if we want to find Key1A we can do

$testJson | gm | ? {$_.Definition -imatch "key1A"}

Which finds the definition in where key1a is in (case-insensitive as specified by -i instead of -c) which gives us the output of



   TypeName: System.Management.Automation.PSCustomObject

Name MemberType   Definition
---- ----------   ----------
key1 NoteProperty System.Management.Automation.PSCustomObject key1=@{key1A=; key1B=}

Where as you can see the parent node is key1 and we can grab that as well with

($testJson | gm | ? {$_.Definition -imatch "key1A"}).name
key1

And to view the content of key1 we can do

$($testJson).$(($testJson | gm | ? {$_.Definition -imatch "key1A"}).name)

key1A                key1B
-----                -----
@{someKey=someValue} @{someKey=someValue}

And key1a

$($testJson).$(($testJson | gm | ? {$_.Definition -imatch "key1A"}).name).key1a

someKey
-------
someValue

Powershell - Search json tree

3 Answers3