Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

PowerShell Parse JSON with header and nested items into lines

I have a JSON like this,

{
    "TotalCount": 2,
    "Data": [
        {
            "ID": 9663696221792,
            "Code": "08099991",
            "Items": [
                {
                    "Amount": 5.05,
                    "LineNo": 1
                },
                {
                    "Amount": 16.08,
                    "LineNo": 2
                }               
            ]
        },
        {
            "ID": 9663696221793,
            "Code": "08028001",
            "Items": [
                {
                    "Amount": 26.13,
                    "LineNo": 1
                }
            ]
        }
    ]

}

And I need the output to be like this,

row ID CODE Cost
header 9663696221792 08099991 21.13
Item 1 9663696221792 08099991 5.05
Item 2 9663696221792 08099991 16.08

and

row ID CODE Cost
Header 9663696221793 08028001 26.13
Item 1 9663696221793 08028001 26.13

and so on for more headers and items

so far
$array = Get-Content -Path 'response.json' | ConvertFrom-Json

then I cycle through

foreach ($root in $array.data)

then though each item

foreach ($Item in $array.data.items)

however this cycles through all Items in the data array.

any ideas.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    First loop

    foreach ($root in $array.data)

    Inside loop (inside the root loop)

    foreach ($item in $root.items)


  2. 0

    • Indeed, the nested loop must refer to the outer loop’s iteration variable rather than to the input object graph as a whole, as Daniel points out.

    • If you mistakenly access the whole object graph in the nested loop with $array.Data.Items, you perform member-access enumeration on the $array.Data array, meaning that the .Item property values across all array elements are returned.

    To put it all together (variables renamed for clarity); note that member-access enumeration is still selectively used, namely in $product.Items.Amount in order to obtain the amounts across all .Item elements:

    # Note: -Raw makes Get-Content much faster by reading the entire file
#       content into as single, multiline string.
$objectGraphfromJson = Get-Content -Raw response.json | ConvertFrom-Json

# Loop over all elements of the array in the `.Data` property.
foreach ($product in $objectGraphfromJson.Data) {
  # Create and output the header object.
  [pscustomobject] @{
    row = 'Header'
    ID = $product.ID
    Code = $product.Code
    # Sum up the amounts across all child items.
    Cost = ($product.Items.Amount | Measure-Object -Sum).Sum
  }
  # For each `.Data` element, loop over its items and 
  # create and output an object for each.
  foreach ($item in $product.Items) {
    [pscustomobject] @{
      row = 'Item ' + $item.LineNo
      ID = $product.ID
      Code = $product.Code
      Cost = $item.Amount
    }  
  }
}

    Output (the equivalent of what you requested, based on PowerShell’s automatic for-display formatting of the objects that were output):

    row               ID Code       Cost
---               -- ----       ----
Header 9663696221792 08099991 21.130
Item 1 9663696221792 08099991  5.050
Item 2 9663696221792 08099991 16.080
Header 9663696221793 08028001 26.130
Item 1 9663696221793 08028001 26.130
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search