I'm testing code to send payment details as JSON which is working however I'm struggling to extract information from the JSON response. I need to extract the content part of the JSON response as individual values so I can assign them to variables then insert them into a SQL table.

The response is assigned to $Result . I've tried $Result.content though obviously this gives you the whole content section. Is there a way to obtain individual values from content without going down the substring route?

Here is an example of a response:

    {"transaction":{"id":"83123b05-2435-40c9-851d-a5636f092637","processed_at":"2019-04-05T13:02:19.689188Z","transaction_type":"Debit","currency_code":"GBP","amount":25000,"recipient_id":"8a659242-8e70-47e1-85ca-2fe18cb262a0","status":"Waiting for funds","status_info":"Insufficient Funds To Process","reconciliation":null,"reference":"D000000516","account_id":"07b286ad-dabc-42a7-ab1b-5302fd382e6c","tag":null,"end_balance":null,"idempotent_key":"1f25e9f8-64cf-4e91-b4d3-8753437b6dbb","created_at":"2019-04-05T13:02:19Z","updated_at":"2019-04-05T13:02:19Z"}}

Here is my Powershell Code:

$Uri = "uriaddress"
$Content = Get-content C:\Test\BACS-DC190326093019.TXT | select-object -skip 4 
foreach($line in $Content){
  if($line.Substring(0,1) -match "[0-9]")
  $sorcode = $line.Substring(0,6)
  $accountNumber =   $line.Substring(6,8)

  $CustomerName = $line.Substring(82,18)

  $FundingAmount = $line.Substring(41,5)
  $FundingAmount = $FundingAmount 

  $Identifier = $line.Substring(64,10)
  $idempotent_key = New-Guid

  $body = @{
    "account_id"     = "000000"
    "currency_code"  = "GBP"
    "amount"         = $FundingAmount
    "recipient_name" = $CustomerName
    "account_no"     = $accountNumber
    "sort_code"      = $sorcode
    "idempotent_key" = $idempotent_key
    "reference"      = $Identifier
    "legal_type"     = "PRIVATE"

    $headers = @{}

    $Result = ''
    $Result = Invoke-WebRequest -Uri $Uri -ContentType 'multipart/form-data' -Method Post -Headers $headers -Body $body 


    IF ($Result.StatusDescription -eq 'OK') { 
       write-host "Payment Sent Succesfully" -ForegroundColor Green
    } ELSE {
       write-host "Payment Failed Succesfully" -ForegroundColor Red

    write-host ""

1 Answers

FoxDeploy On

PowerShell has built in support for processing JSON, so let's run your input through ConvertFrom-Json and see what we get.

$Result = '{"transaction":{"id":"83123b05-2435-40c9-851d-a5636f092637","processed_at":"2019-04-05T13:02:19.689188Z","transaction_type":"Debit","currency_code":"GBP","amount":25000,"recipient_id":"8a659242-8e70-47e1-85ca-2fe18cb262a0","status":"Waiting for funds","status_info":"Insufficient Funds To Process","reconciliation":null,"reference":"D000000516","account_id":"07b286ad-dabc-42a7-ab1b-5302fd382e6c","tag":null,"end_balance":null,"idempotent_key":"1f25e9f8-64cf-4e91-b4d3-8753437b6dbb","created_at":"2019-04-05T13:02:19Z","updated_at":"2019-04-05T13:02:19Z"}}' 

$Result = $Result | Convertfrom-json

In your usage, you'd just run the last part to convert $Result into a PowerShell Object.

$Result has one property called .transaction where all of the info lives. We can get this out with the following syntax.


id               : 83123b05-2435-40c9-851d-a5636f092637
processed_at     : 2019-04-05T13:02:19.689188Z
transaction_type : Debit
currency_code    : GBP
amount           : 25000
recipient_id     : 8a659242-8e70-47e1-85ca-2fe18cb262a0
status           : Waiting for funds
status_info      : Insufficient Funds To Process
reconciliation   : 
reference        : D000000516
account_id       : 07b286ad-dabc-42a7-ab1b-5302fd382e6c
tag              : 
end_balance      : 
idempotent_key   : 1f25e9f8-64cf-4e91-b4d3-8753437b6dbb
created_at       : 2019-04-05T13:02:19Z
updated_at       : 2019-04-05T13:02:19Z

So, now if we want to pull out specific values from there, we run the following.

C:\Users\Stephen> $result.transaction.amount

C:\Users\Stephen> $result.transaction.status
Waiting for funds

C:\Users\Stephen> "Transaction in the amount of $($result.transaction.amount) -- status: $($result.transaction.status)"
Transaction in the amount of 25000 -- status: Waiting for funds

This should get you on your way.

Final tidbit, if you swap out Invoke-WebRequest for Invoke-RestMethod, the conversion from JSON is done for you automatically!