Querying nested JSON files in Powershell

Question

I have nested JSON file (saved on HDD as file.json):

    Val: [
    {str1: "REQ", 
     num1: 001, 
     Arr1: [
     {"id": "e6ddf4538b20",
     "displayName": "CA015",                    
     "result": "fail"},
    {"id": "538b20e6ddf4",
    "displayName": "CA014",                    
    "result": "na"}]
    },
    {str1: "AVL", 
     num1: 002, 
     Arr1: [
     {"id": "0e63d114",
     "displayName": "CA015",                    
     "result": "fail"},
     {"id": "0e63d1140e6ddf4",
     "displayName": "CA014",                    
     "result": "success"}]},
{str1: "unst", 
         num1: 003, 
         Arr1: [
         {"id": "o44k63jz14",
         "displayName": "CA015",                    
         "result": "success"},
         {"id": "0e63d1140e6ddf4",
         "displayName": "CA014",                    
         "result": "success"}]}
        ]

I can successfully import the JSON.

$imp = get-content -path 'c:\temp\file.json | out-string | convertFrom-json

Now, I want to query (for each object in Val array) if $imp.Arr1.displayName -like '*015' -and $imp.Arr1.result -eq 'fail' and return Str1, num1

Answer 1

As commented by @sirtao, from a StackOverflow question standpoint, I would recommend you to follow the how to ask guidelines and create an Minimal, Reproducible Example next time. In fact, if you would have done so, you might have already found out from similar questions that ConvertFrom-Json returns PSCustomObjects by default.
It is unclear from you question with PowerShell version you using knowing that in the later PowerShell versions the ConvertFrom-Json cmdlet has a -AsHashTable parameter which allows you to use the dot-notation as suggested in your question.

Anyways, as a common answer to this general question, you might use this ObjectGraphTools module which is able to treat any (hashtable) dictionary or PSCustomObject equally. Besides, it has an extended dot notation (Xdn) which helps you to easily target any node independent of it's depth:

Install-Module -Name ObjectGraphTools

Using the Get-Node cmdlet:

$Data = Get-content -path c:\temp\file.json | ConvertFrom-Json
$Node = $Data | Get-Node ~displayName=*15..result=fail....
$Node

Path   Name Depth Value
----   ---- ----- -----
Val[0]    0     2 @{str1=REQ; num1=1; Arr1=System.Object[]}
Val[1]    1     2 @{str1=AVL; num1=2; Arr1=System.Object[]}

Explanation:

• ~displayName=*15 queries any descendant leaf node named displayName and a value like *15
• .. gets the parent node
• result=fail selects the direct child nodes named result and a value of fail
• .... gets the great-grandparent node

To get the concerned Str1 and num1 leaf child nodes, you might use the Get-ChildNode cmdlet:

$Node | Get-ChildNode -Leaf

Path        Name Depth Value
----        ---- ----- -----
Val[0].str1 str1     3 REQ
Val[0].num1 num1     3 1
Val[1].str1 str1     3 AVL
Val[1].num1 num1     3 2

or:

($Node | Get-Node str1).Value
REQ
AVL

or:

($Data | Get-Node ~displayName=*15..result=fail....str1=REQ..num1).Value
1

To change the specific value:

($Data | Get-Node ~displayName=*15..result=fail....str1=REQ..num1).Value = 3
$Data | ConvertTo-Json -Depth 9 # ...