Delphi: Accessing JSON Objects within a JSON Array

Question

I have a JSON Object, let's name it jObject that looks like this:

{
  "id": 0,
  "data": "[{DAT_INCL: \"08/03/2012 10:07:08\", NUM_ORDE: 1, NUM_ATND: 1, NUM_ACAO: 2, NUM_RESU: 3},
            {DAT_INCL: \"08/03/2012 10:07:09\", NUM_ORDE: 2, NUM_ATND: 1, NUM_ACAO: 4, NUM_RESU: 5},
            {DAT_INCL: \"08/03/2012 10:07:09\", NUM_ORDE: 3, NUM_ATND: 1, NUM_ACAO: 8, NUM_RESU: NULL}]"
}

As you can see, it contains two pairs, one of which is an array with three objects in this case (the amount of objects is dynamic) with multiple "key: values"(these don't vary, being always the same 5 fields), which I want to insert into an SQL database, "key" being column, "value" being field. Question is, how do I access each object individually?

Code-wise what I did was extract the pair that contained this array by putting it in jPair

jPair := OriginalObject.Get(1); 

and then captured the array

jArray:= TJSONArray(jPair.JsonValue);

(Also, as a bonus, when I evaluate jArray.Size, the result is 6226004. What?)

Solution 1

If you have an array from DBXJSON, then it is a TJSONArray. Call its Get method to get an element of the array.

var
  Value: TJSONValue;

Value := jArray.Get(0);

You can also go through the entire array with a for loop:

for Value in jArray do

---

But if you check the Size property and get 6226004 instead of 3, that suggests there's something else wrong here. My guess is that what you think is a TJSONArray isn't really that type. Use as to do a checked type cast:

jArray := jPair.JsonValue as TJSONArray;

You'll get an EInvalidCast exception if that fails.

Solution 2

here is an sample code to parse and output your json data. I've modified your JSON data and added ArrayData field, wich contains your initial array of objects:

program Project1;
{$APPTYPE CONSOLE}
{$R *.res}

uses
  System.SysUtils, dbxjson;

const JSON_DATA = '{"ArrayData":['+
                    '{"DAT_INCL":"07/03/2012 17:33:03", "NUM_ORDE":1,"NUM_ATND":1, "NUM_ACAO":2, "NUM_RESU":3},'+
                    '{"DAT_INCL":"07/03/2012 17:33:05", "NUM_ORDE":2,"NUM_ATND":1, "NUM_ACAO":4, "NUM_RESU":5},'+
                    '{"DAT_INCL":"07/03/2012 17:33:05", "NUM_ORDE":3,"NUM_ATND":1, "NUM_ACAO":8, "NUM_RESU":null}'+
                   ']}';


var jsv   : TJsonValue;
    originalObject : TJsonObject;

    jsPair : TJsonPair;
    jsArr : TJsonArray;
    jso  : TJsonObject;
    i : integer;
begin
    try
        //parse json string
        jsv := TJSONObject.ParseJSONValue(JSON_DATA);
        try
            //value as object
            originalObject := jsv as TJsonObject;

            //get pair, wich contains Array of objects
            jspair := originalObject.Get('ArrayData');
            //pair value as array
            jsArr := jsPair.jsonValue as  TJsonArray;

            writeln('array size: ', jsArr.Size);
            //enumerate objects in array
            for i := 0 to jsArr.Size - 1 do begin
                writeln('element ', i);
                // i-th object
                jso := jsArr.Get(i) as TJsonObject;

                //enumerate object fields
                for jsPair in jso do begin
                    writeln('   ', jsPair.JsonString.Value, ': ', jsPair.JsonValue.Value);
                end;
            end;
        finally
            jsv.Free();
            readln;
        end;
    except
        on E: Exception do
          Writeln(E.ClassName, ': ', E.Message);
    end;
end.

Solution 3

Practical example of adding JSON data from an array to a TComboBox

This example assumes you have included the JSON unit. It also assumes you have a TComboBox named cmbCompany. There is nothing wrong with the accepted answers, I just wanted to document.

uses JSON;

procedure LoadCompanies;
var
  i : Integer;
  companyArray : TJsonArray;
  company : TJsonObject;

begin
  //get the company data
  companyData := TJSONObject.ParseJSONValue('{data:[{"name": "One"},{"name": "Two"}]}') as TJSONObject;
  try
    cmbCompany.Items.Clear;
    with companyData do
    begin
      //Important bit which relates to the question!
      companyArray := (Get('data').JsonValue as TJSONArray);
      try 
        for i := 0 to companyArray.Size-1 do
        begin
          //Get one of the array values
          company := (companyArray.Get(i) as TJSONObject);

          //Add name to combo box, you can obviously get other values in similar fashion
          cmbCompany.Items.Add(company.GetValue<String>('name'));
        end;
      finally
        companyArray.Free;
      end;
    end;
  finally
    companyData.Free;
  end;
end;

Comments

[bpromas]

Indeed I got an EInvalidCast Exception. Does that mean that the array I have in my JsonObject is not actually an array? Working with JSON is becoming more and more of an uphill battle.

[Rob Kennedy]

It means it's not a TJSONArray. The debugger should be able to tell you what it really is. If the debugger doesn't tell you in a tool tip, then display the result of calling jPair.JsonValue.ClassName.

[bpromas]

Yeah, it's a TJSONString, and a badly formatted one at that. How the heck am I gonna parse through this?

[Rob Kennedy]

It appears to be a string that contains more JSON data. Parse it the same way you parse other JSON-formatted strings of data. And then complain to the person responsible for generating that data because it's double-encoded.

[bpromas]

I was able to extract it down to the JsonString, and that contains my JsonArray. I can't figure out how to remove the JSONArray from the JSONString though, so I might as well bother you some more.

[Rob Kennedy]

I don't understand what further problem you're having. You can construct a JSON object from a string the same way you always do. Use TJSONObject.ParseJSONValue. Call it on your string: jArray := TJSONObject.ParseJSONValue((jPair.JsonValue as TJSONString).Value) as TJSONArray.

[bpromas]

"TJSONObject.ParseJSONValue((jPair.JsonValue as TJSONString).Value)" seems to be returning nil.

[RobbZ]

Thanks for your example it helped me. However instead of iterating over the size of the array you can for jsValue in jsArr do begin jsObj := jsValue as TJSONObject; It just reads much cleaner to me.