Powershell parsing API ouput JSON

Question

I am trying to find a way to manipulate the output obtained from the API request.

The output is similar to this:

[
    {
        "Date": "19/5/2022",
        "ProductId": "0001",
        "ProductName": "Fizzy Drink",
        "Cost": "2.32",
        "Currency": "USD",
    },
    {
        "Date": "16/5/2022",
        "ProductId": "0002",
        "ProductName": "Dark Chocolate",
        "Cost": "6.52",
        "Currency": "USD",
    },
    {
        "Date": "10/5/2022",
        "ProductId": "0003",
        "ProductName": "Snacks",
        "Cost": "4.2",
        "Currency": "USD",
    }
]

How can manipulate this type of data?

My goal is to obtain something like this:

Date 19/5/2022

ProductID 0001

ProductName Fizzy Drink

Cost 2.32

Currency USD

Date 16/5/2022

ProductID 0002

ProductName Dark Chocolate

Cost 6.52

Currency USD

I've tried something like this: (without success)

foreach($GetInfo in $IDInfo)  
{  
$ID = $GetInfo.data.Date 
$Name = $GetInfo.data.ProductId 
Write-Host $ID  
Write-Host $Name  
}

But every time I got, the date on one line, ProductID on the second line, and ProductName on the third line.

like:

19/5/2022 16/5/2022 16/5/2022 
0001 0002 0003 
Fizzy Drink Dark Chocolate Snacks

Can we see your own attempts at this? You may be after `ConvertFrom-Json`. — Abraham Zinala, Aug 02 '22 at 17:22
Your output doesn’t appear to be valid json (or “json lines”) for a number of reasons. Can you please post a sample output that is *exactly* like your output rather than just “similar”? Anonymise it if needed, but it should be syntactically identical otherwise answers are going to focus on your invalid format… — mclayton, Aug 02 '22 at 20:53
If you copy the JSON now in the question, open a command prompt, and enter `Get-Clipboard | ConvertFrom-Json`, this appears to produce exactly what you need. what more would you expect? — Jeroen Mostert, Aug 03 '22 at 11:14
Despite my test I'm unable to get a correct result :( I can retrieve data, but every time data are: date date date instead of: Date productID | date ProductID — LEFBE, Aug 06 '22 at 18:14

score 0 · Answer 1 · answered Aug 16 '22 at 15:01

Your output implies that $GetInfo.data is an array, which means that a property access such as .Date uses member-access enumeration, which means that the .Date values of all array elements are returned.

Thus, you need an inner loop as well:

foreach($GetInfo in $IDInfo)  
{  
  # Loop over the elements of the array in .data
  foreach ($element in $GetInfo.data) { 
     $Id = $element.Date
     $Name = $element.ProductId 
     # ... work with these variables here;
     #     e.g., output their values (don't use Write-Host, unless
     #     you want to print to the display only).
     $Id, $Name 
  }
}

Note that, as Jeroen Mostert points out, passing your sample JSON text to ConvertFrom-Json conveniently parses the JSON array into .NET objects (of type [pscsustomobject]), whose properties you can access:

However, there is a caveat in Windows PowerShell (no longer in PowerShell (Core) 7+):

Note: Given that your sample JSON input has extraneous trailing , chars. after each object's final property, I'm assuming you're using PowerShell (Core), because Windows PowerShell would complain about that; however, the pitfall discussed below is still worth pointing out for future readers.

If you're directly processing the output from a ConvertFrom-Json or Invoke-RestMethod call that parses a JSON array and you want to process the resulting objects one by one, force ConvertFrom-Json to enumerate the top-level array parsed from JSON by enclosing the call in (...):

$json = @'
[
    {
        "Date": "19/5/2022",
        "ProductId": "0001",
        "ProductName": "Fizzy Drink",
        "Cost": "2.32",
        "Currency": "USD"
    },
    {
        "Date": "16/5/2022",
        "ProductId": "0002",
        "ProductName": "Dark Chocolate",
        "Cost": "6.52",
        "Currency": "USD"
    },
    {
        "Date": "10/5/2022",
        "ProductId": "0003",
        "ProductName": "Snacks",
        "Cost": "4.2",
        "Currency": "USD"
    }
]
'@

# Note the (...) around the ConvertFrom-Json call, required in Windows PowerShell.
# The same would apply if you send the ConvertFrom-Json output to the *pipeline*:
#    (ConvertFrom-Json $json) | ForEach-Object { "This date: " + $_.Date }
foreach ($obj in (ConvertFrom-Json $json)) {
  "This date: " + $obj.Date
}

Background:

In versions before PowerShell (Core) 7.0, ConvertFrom-Json and Invoke-RestMethod send arrays parsed from JSON as a whole through the pipeline, rather than element by element, leading to unexpected behavior.
Enclosing a command (pipeline) in (...), the grouping operator causes these arrays to be enumerated.

See this answer for more information.

Powershell parsing API ouput JSON

1 Answers1