Powershell : Referencing values in nested JSON arrays

Question

I'm attempting to use powershell to communicate with an api using the Invoke-RestMethod function.

From what I can tell this returns the JSON response as a powershell object (array). The result has 2 levels being a list of projects and then custom fields set against those projects.

I am trying to loop through each project and then through each custom field of the project and then use this info to perform various SQL actions.

At the moment I'm struggling with the fact that I can use a ForEach to loop through the top level "data" being the projects, but when I try loop through the custom fields for the project it's actually looping through all custom fields for all projects.

I've got this far using my limited experience with powershell and many hours of searching and reading. However I'm sure I'm just not understanding the whole concept here and hopefully it's just a simple issue with the way I'm trying to reference the data...

This is a an example of the api response.

"TotalPages":1, "TotalRecords":2, "Data":[  
      {  
         "code":"00000014",
         "description":"Digital Strategy",
         "customFieldValues":[  
            {  
               "key":135,
               "name":"Department",
               "value":"Sales"
            }
         ]
      },
      {  
         "code":"T3FA",
         "description":"Survey coding",
         "customFieldValues":[  
            {  
               "key":135,
               "name":"Department",
               "value":"Finance"
            }
         ]
      }    ] }

And this is my simple code to loop through the response.

    $APIResponse = Invoke-RestMethod -Uri $Uri -Headers $Headers

ForEach ($d in $APIResponse.data) {
    "Project Code = " + $d.code + ", Project Description = " + $d.description
    ForEach ($e in $APIResponse.data.customFieldValues) {
        "CustomField Key " + $e.key + ", Name " + $e.name  + ", Value " + $e.value
    }
}

I guess it's doing what it's being asked to do in that it's looping through all the customFieldValue but I'm needing it to just loop through the custom fields relating to the project that I'm currently looping through if that makes any sense?

Tommy Sharp · Accepted Answer · 2017-07-17 04:17:00Z

1

Okay I'm not sure how I didn't find this before but some trial and error lead me to this which appears to work well.

In the second ForEach I replaced $APIResponse.data.customFieldValues with $d.customFieldValues which appears to make it look through just the currently selected project.

    $APIResponse = Invoke-RestMethod -Uri $Uri -Headers $Headers

ForEach ($d in $APIResponse.data) {
    "Project Code = " + $d.code + ", Project Description = " + $d.description
    ForEach ($e in $d.customFieldValues) {
        "CustomField Key " + $e.key + ", Name " + $e.name  + ", Value " + $e.value
    }
}

answered Jul 17, 2017 at 4:17

Tommy Sharp

952 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

Powershell : Referencing values in nested JSON arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related