PowerShell Parse JSON with header and nested items into lines

General
1

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.

2

To achieve the desired output, you need to loop through the Data array in your JSON, calculate the total Cost for each Header row, and then iterate through the Items to create Item rows. Below is the corrected PowerShell script to process your JSON:

PowerShell Script

# Load the JSON file
$json = Get-Content -Path 'response.json' | ConvertFrom-Json

# Iterate through each "Data" entry
foreach ($entry in $json.Data) {
    # Calculate the total cost for the header row
    $totalCost = ($entry.Items | Measure-Object -Property Amount -Sum).Sum

    # Print the Header row
    Write-Output "row    ID               CODE      Cost"
    Write-Output "Header $($entry.ID) $($entry.Code) $totalCost"

    # Iterate through each Item and print its details
    $itemNumber = 1
    foreach ($item in $entry.Items) {
        Write-Output "Item $itemNumber $($entry.ID) $($entry.Code) $($item.Amount)"
        $itemNumber++
    }

    # Add a blank line between entries for clarity
    Write-Output ""
}

Explanation of the Script

  1. Load JSON:
  • Use Get-Content and ConvertFrom-Json to load and parse the JSON file into a PowerShell object.
  1. Iterate through Data:
  • Loop through each entry in the Data array using foreach.
  1. Calculate Total Cost:
  • Use Measure-Object with the -Property Amount -Sum to calculate the total cost of all Items for each entry.
  1. Print Header Row:
  • Use Write-Output to display the Header row with the total cost.
  1. Iterate through Items:
  • Loop through each Item in the Items array and print the details, incrementing the Item number for each.
  1. Formatting:
  • Use blank lines for readability between sections.

Output

For the provided JSON, the script will produce the following output:

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

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

Notes

  • Precision: Ensure your PowerShell environment uses the correct number format for monetary values.
  • Output Redirection: If you need to save the output to a file, append | Out-File -FilePath 'output.txt' to the script.