I want to retrieve data from JSON file in Powershell. I have this JSON data:

{
  "id": "abcxyz",
  "data": {
    "a": {
        "abc": "xyz"
    },
    "b": [
      {
        "bId": 2001,
        "bData": {
          "bAbc": [
            {
              "bAbcX": 123,
              "bAbcY": 456,
              "bAbcZ": "b1AbcZ0"
            },
            {
              "bAbcX": 312,
              "bAbcY": 654,
              "bAbcZ": "b1AbcZ1"
            }
          ],
          "bDef": [
            {
              "bDefX": 456,
              "bDefY": 654,
              "bDefZ": "b1DefZ0"
            },
            {
              "bDefX": 789,
              "bDefY": 987,
              "bDefZ": "b1DefZ1"
            }
          ]
        }
      },
      {
        "bId": 2002,
        "bData": {
          "bAbc": [
            {
              "bAbcX": 123,
              "bAbcY": 456,
              "bAbcZ": "b2AbcZ0"
            },
            {
              "bAbcX": 312,
              "bAbcY": 654,
              "bAbcZ": "b2AbcZ1"
            }
          ],
          "bDef": [
            {
              "bDefX": 456,
              "bDefY": 654,
              "bDefZ": "b2DefZ0"
            },
            {
              "bDefX": 789,
              "bDefY": 987,
              "bDefZ": "b2DefZ1"
            }
          ]
        }
      },
      {
        "bId": 2003,
        "bData": {
          "bAbc": [
            {
              "bAbcX": 123,
              "bAbcY": 456,
              "bAbcZ": "b3AbcZ0"
            },
            {
              "bAbcX": 312,
              "bAbcY": 654,
              "bAbcZ": "b3AbcZ1"
            }
          ],
          "bDef": [
            {
              "bDefX": 456,
              "bDefY": 654,
              "bDefZ": "b3DefZ0"
            },
            {
              "bDefX": 789,
              "bDefY": 987,
              "bDefZ": "b3DefZ1"
            }
          ]
        }
      }
    ]
  }
}

I want to retrieve data with JSONPath $.data.b[*].bData.bAbc[0].bAbcZ in Powershell. The expected result is:

[
  "b1AbcZ0",
  "b2AbcZ0",
  "b3AbcZ0"
]

The JSONPath is working in https://jsonpath.com/ and this is how I tried to retrieve it in powershell:

$JSON = Get-Content ".\test.json" | Out-String | ConvertFrom-Json
$JSON.data.b[*].bData.bAbc[0].bAbcZ

But when I tried in Powershell, * is invalid, this is the output:

  $JSON.data.b[*].bData.bAbc[0].bAbcZ
               ~
Array index expression is missing or not valid.

  $JSON.data.b[*].bData.bAbc[0].bAbcZ
                ~
You must provide a value expression following the '*' operator.

  $JSON.data.b[*].bData.bAbc[0].bAbcZ
                ~
Unexpected token ']' in expression or statement.
      CategoryInfo          : ParserError: (:) [], ParseException
      FullyQualifiedErrorId : MissingArrayIndexExpression

I also tried using $JSON.data.b.bData.bAbc[0].bAbcZ but it only retrieve the first data which is b1AbcZ0. What's the equivalent of JSON Wildcard (*) in Powershell object?

CodePudding user response：

It's sloppy but should do the trick. Just replace the .json file path in $JSONData.

$JSONData = Get-Content .\Documents\Untitled2.json |ConvertFrom-Json
$array = $JSONData.data.b.bdata.babc
$FinalArray = $JSONData.data.b.bdata | %{$_.bAbc[0].bAbcZ}
$FinalArray |Out-GridView

Let me know if that works!

CodePudding user response：

You can use a query similar to JSONPath by first unrolling the JSON object recursively. Then you can use the Where-Object command or .Where{} method to filter the JSON by path.

Here is a function to flatten the JSON. It outputs a sequence of [PSCustomObject] with properties Path and Value:

Function Expand-Json {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory, ValueFromPipeline)] [PSCustomObject] $InputObject,
        [Parameter()] [string] $Path
    )
    
    process {
        # Iterate the properties of InputObject
        foreach( $prop in $InputObject.PSObject.Properties ) {

            # Full path of the current property
            $propertyPath = "$Path.$($prop.Name)"

            # Output current property with path
            [PSCustomObject]@{ Path = $propertyPath; Value = $prop.Value }

            if( $prop.Value -is [PSCustomObject] ) {
                # Process child object recursively
                Expand-Json -InputObject $prop.Value -Path $propertyPath
            }
            elseif( $prop.Value -is [Collections.IList] ) {
                # Iterate array and process each element recursively
                $i = 0
                foreach( $item in $prop.Value ) {
                    $itemPath = "$propertyPath<$i>"; $i  
                    Expand-Json -InputObject $item -Path $itemPath
                }
            }
        }
    }
}

Usage example:

$JSON = Get-Content ".\test.json" -Raw | ConvertFrom-Json

# Unroll the JSON
$flatJSON = $JSON | Expand-Json   

# Filter by path - this outputs an array
$filteredJSON = $flatJSON | Where-Object Path -like '.data.b<*>.bData.bAbc<0>.bAbcZ'

# Convert data back to JSON string
$filteredJSON.Value | ConvertTo-Json

Output:

[
  "b1AbcZ0",
  "b2AbcZ0",
  "b3AbcZ0"
]

Notes:

• The path syntax .data.b<*>.bData.bAbc<0>.bAbcZ is slightly different from JSONPath to make it easier to use with the PowerShell -like operator.
  • There is no root object token $, because this character has special meaning for PowerShell string interpolation.
  • The array index is expressed using angular brackets <>, because square brackets [] have special meaning for the -like operator (they express a range of characters, see about Wildcards).
• The function Expand-Json is conceptually clear, but not very efficient. In general, recursive PowerShell functions tend to be comparatively slow because of the parameter binding overhead. If performance is paramount, there are several ways to improve performance considerably, e. g. by using a class or inline C#.