PowerProtect Data Manager: Declarative Data Extracts and Complex Nested Data Structures

In my last article we saw how you can easily produce basic data extracts in a declarative manner (no code) simply by creating a JSON template for the data you're interested in.

The code and documentation can be found here:

https://github.com/SkunkworksAutomation/power-protect-ezdata

I'm going to nerd out on you a bit so please bear with me here.

Most programming languages and BI tools have support for working with JSON. PowerShell is no exception. When you query a REST API via the PowerShell cmdlet:

Invoke-RestMethod

PowerShell takes the response returned by the REST API, in JSON, and converts it into a PowerShell object. This is important to understand because once the data is converted you have access to PowerShell's extremely robust query cmdlets and comparison operators.

Given that information the question becomes... how do we expose some of this goodness in a "no code" fashion to the end user so they can generate data extracts on complex nested data structures?

In order for this to work we need to "follow the path" in the way that the PowerShell language natively follows the dot (.) path when referencing the properties for nested objects.

Take this JSON object for example

{
    "name":"Test",
    "example": {
        "name": "Example1"
    }
}        

We have an object named "Test" that has a property called "example". "example" has a nested property called "name" with the value of "Example1".

On our report we want the value of the "name" property AND the value of the "name" property nested under the "example" property. So "Test" and "Example1".

Below is how we would declare the fields for the report.

  "fields": [
    {
      "label": "name",
      "value": "name"
    },
    {
      "label": "exampleName",
      "value": "example.name"
    }
  ]        

Let's see what this looks like when we test it out...

Easy enough...

Now let's make the data structure way more complicated by adding an array of objects, under the "example" property, called "values" then creating another property called "nested" which will be an array of objects as well.

You can see where getting at the data you need could get complex if you were hand writing code!

The JSON structure described above would look like this...

{
    "name":"Test",
    "example": {
        "name": "Example1",
        "values":[
            {
                "id":1,
                "name":"value1",
                "nested":[
                    {
                        "name":"green"
                    },
                    {
                        "name":"red"
                    }
                ]
            },
            {
                "id":2,
                "name":"value2",
                "nested":[
                    {
                        "name":"yellow"
                    },
                    {
                        "name":"orange"
                    }
                ]
            }
        ]
    }
}        

From a reporting prospective we only need the data that is relevant to us... we need to be able to pick out the data we want in a declarative manner. In this case I want to extract both colors into separate report fields nested under "example | values | value2 | nested".

Following the path is the important part here. Now that we understand where the data resides that we need access to... declaring it in our JSON template is pretty straight forward.

Building on the original example here is what our "fields" configuration would look like.

 "fields": [
    {
      "label": "name",
      "value": "name"
    },
    {
      "label": "exampleName",
      "value": "example.name"
    },
    {
        "label": "exampleValuesName",
        "value": "example.values|?id eq 2|name"
    },
    {
        "label": "exampleValuesColor1",
        "value": "example.values|?id eq 2|nested|?0|name"
    },
    {
        "label": "exampleValuesColor2",
        "value": "example.values|?id eq 2|nested|?name eq orange|name"
    }
  ]        

Lets see what this looks like when we execute the test...

Lets break down the two relevant field configurations for a better understanding.

{
        "label": "exampleValuesColor1",
        "value": "example.values|?id eq 2|nested|?0|name"
}        

We can see here that we begin by traversing from the root response to our first array of objects

example.values

filter expressions are separated by the | (pipe) character.

Then as you guessed the ? starts the query. We are looking for an object within the example.values array with an id equal to 2 which returns the objected named "value2".

We then traverse to the "nested" array.

For this array we are simply returning the first element in the array which is object 0 as most arrays in programming are 0 based.

From there we select then "name" property. In the screenshot above the relevant output is the field labeled: exampleValuesColor1 which has a value of yellow.

Next we have a similar configuration with some slight differences.

{
        "label": "exampleValuesColor2",
        "value": "example.values|?name eq value2|nested|?name eq orange|name"
}         

We start off the same by traversing to:

example.values

Then for our first query in our filter expressions we are looking for an object within the example.values array with an name equal to value2 which returns the objected named "value2".

We then traverse to the "nested" array.

We query the nested array for an object with the name of orange and return it's name property which you could have guessed is orange.

Both approaches work in this case but they are slightly different. the first approach uses the indexed value of the element contained within the array while the latter queries by a specific property value.

This means that the first approach won't net consistent results if the objects within the array in question are returned in a random order. Typically I'll only use positional / array index in queries when the underlying object doesn't have a property with a unique value for the query.

And with that I'll stop my yammering and wish you a great week!