How to cycle through JSON with multiple records in Delphi

Question

When receiving JSON such as the example below, how do I cycle through to extract each field value of each "record", so I can insert them into a database?

{
  "Accounts": [{
    "AccountID": "ebd06280-af70-4bed-97c6-7451a454ad85",
    "Code": "091",
    "Name": "Business Savings Account",
    "Type": "BANK",
    "TaxType": "NONE",
    "EnablePaymentsToAccount": false,
    "BankAccountNumber": "0209087654321050",
    "BankAccountType": "BANK",
    "CurrencyCode": "NZD"
  },{
    "AccountID": "7d05a53d-613d-4eb2-a2fc-dcb6adb80b80",
    "Code": "200",
    "Name": "Sales",
    "Type": "REVENUE",
    "TaxType": "OUTPUT2",
    "Description": "Income from any normal business activity",
    "EnablePaymentsToAccount": false
  }]
}

This is what I have so far, but I'm getting an error on the ParseJSONValue line - "Invalid class typecast"

try
  xeroRequest.Execute;
  if Assigned(xeroResponse.JSONValue) then begin
    jValue := xeroResponse.JSONValue;
    strJson := jValue.ToString;
    jArray := TJSonObject.ParseJSONValue(strJson) as TJSONArray;
    try
      idx := 1;
      for arrElement in jArray do begin
        RowValue := (arrElement as TJSonObject).GetValue('Accounts');
        if RowValue is TJSONArray then begin
          for RowItem in TJSONArray(RowValue) do begin
            RowItem.TryGetValue('AccountID', AcctId);
            memoJson.Lines.Add(Format('%d: %s', [idx, AcctId]));
          end;
        end;
        inc(idx);
      end;
    finally
      jArray.Free;
    end;
  end;
except on E:Exception do
  ShowMessage('Error: ' + xeroResponse.ErrorMessage + ' - ' + E.Message);
end;

Answer 1

You are using TRESTResponse to get the JSON. You can typecast its JSONValue property to TJSONObject and read the Accounts value from it, then typecast that value to TJSONArray and iterate it, typecasting each element to TJSONObject and read fields from that object as needed.

---

UPDATE: You don't need to parse the JSON, it's already been parsed for you. Just use the TRESTResponse.JSONValue as-is, it points at the top-level value of the parsed JSON.

As for the typecast error, it's because you are casting the JSONValue to TJSONArray, which is wrong as the JSON does not begin with an array ([...] ). It begins with an object ({...}), so you have to cast the JSONValue to TJSONObject first, as I stated above.

Also, DO NOT free any of the JSON items at all. You don't own any of them, the top-level TJSONObject pointed by JSONValue owns them, and the TRESTResponse owns the JSONValue.

Try this instead:

try
  xeroRequest.Execute;
  if Assigned(xeroResponse.JSONValue) then begin
    jObject := xeroResponse.JSONValue as TJSONObject;
    jArray := jObject.GetValue('Accounts') as TJSONArray;
    idx := 1;
    for arrElement in jArray do begin
      jObject := arrElement as TJSONObject;
      jObject.TryGetValue('AccountID', AcctId);
      memoJson.Lines.Add(Format('%d: %s', [idx, AcctId]));
      Inc(idx);
    end;
  end;
except
  on E:Exception do
    ShowMessage('Error: ' + xeroResponse.ErrorMessage + ' - ' + E.Message);
end;