What is the proper way to retrieve a nested JSON field using TClientDataset in Delphi?

Question

I am using TRestClient>TRestRequest>TRestResponse>TRESTResponseDataSetAdapter to retrieve JSON data from a Rest API. This works well and I create a TClientDataset in memory:

  // Create a new in memory dataset
  gStaffTable := TClientDataset.Create(nil);

  with gStaffTable do
  begin
    // Add some fields to it
    FieldDefs.Add('FirstName', ftString, 255, False);         //0
    FieldDefs.Add('MiddleName', ftString, 255, False);        //1
    FieldDefs.Add('LastName', ftString, 255, False);          //2
    // ...add the other fields 3..26 here
    // Department is a nested field
    FieldDefs.Add('Department', ftString, 255, False);        //27

    // Create it
    CreateDataSet;
    // Add an AfterScroll procedure
    AfterScroll := gStaffTableAfterScroll;
    OnCalcFields := gStaffTableOnCalcFields;

    IndexFieldNames := 'FullName';
    Open;
    FieldByName('Firstname').DisplayLabel := 'First Name';
    FieldByName('MiddleName').DisplayLabel := 'Middle Name';
    //.. add the rest of the displaylabels here
 
   end;

I can then read my data into the fields:

  ClientDataset.First;
  while not ClientDataSet.EOF do
  begin
    with gStaffTable do
    begin
      try
        application.ProcessMessages;
        append;
          FieldbyName('Firstname').AsString := ClientDataSetfirst_name.AsString;
          FieldbyName('LastName').AsString := ClientDataSetlast_name.AsString;
          FieldbyName('Fullname').AsString := ClientDataSetfirst_name.AsString + ' ' + 
          //.. do the same for the rest of the fields
          
          // Department is a nested field - need to modify this to read the department name
          // {"name":"Finance","id":29111}
          FieldbyName('Department').AsString :=  ClientDataSet.FieldByName('Department').AsString;

        post;
      except

      end;
    end;
    application.ProcessMessages;
    ClientDataSet.Next;
  end;

I can then just assign the DataField to a component on the GUI (partial screen snip):

You can see from this what my problem is. The Department field is a nested element and I cannot figure out how to access {"name":"Finance","id":29111} to display Finance.

I have tried various resolutions to similar problems but cannot find one that works.

Answer 1

You can use the TJSONObject.ParseJSONValue class function for parsing the JSON string.

For example, you can define a function to extract the value:

uses
  System.JSON;

function ExtractPairStringValue(const AJSONString : string; const APairName : string) : string;
var
  Obj : TJSONValue;
begin
  Obj := TJSONObject.ParseJSONValue(AJSONString);
  try
    Result := Obj.GetValue<string>(APairName);
  finally
    Obj.Free;
  end;
end;

Then you'll be able to extract the value like this:

FieldByName('Department').AsString := ExtractPairStringValue(ClientDataSet.FieldByName('Department').AsString, 'name');

Answer 2

Add another field to dataset "DepartmentName" then set an TField.OnGetText event on him.

procedure GetDepartmentNameValue should extract data from JSON If you want to set value to finance make procedure to do it

procedure TForm1.FieldOnGetText(Sender: TField; var Text: string;
  DisplayText: Boolean);
begin
    Text := GetDepartmentNameValue(Sender);
end;

procedure TForm1.FieldOnSetText(Sender: TField; var Text: string; DisplayText: Boolean);
begin
  SetDepartmentNameValue(sender, Text);
end;

Answer 3

You can create a nested ClientDataSet to handle DataBinding. Here's my example:

var
  gStaffTable: TClientDataSet;
begin
  gStaffTable := ClientDataSet1;
  
  with gStaffTable do
  begin
    // Add fields
    FieldDefs.Add('FirstName', ftString, 255, False);         //0
    FieldDefs.Add('MiddleName', ftString, 255, False);        //1
    FieldDefs.Add('LastName', ftString, 255, False);          //2
  
    // Define a nested field for the 'Department' structure
    with FieldDefs.AddFieldDef do
    begin
      Name := 'Department';
      DataType := ftDataSet;
      ChildDefs.Add('Name', ftString, 255, False);
      ChildDefs.Add('id', ftInteger);
    end;
  end;

  // Create the DataSet
  gStaffTable.CreateDataSet;
end;

In this code, we first define the main fields of the dataset. Then, we define a nested field for the 'Department' structure, which contains a 'Name' and an 'id'. After defining the structure of our fields, we call CreateDataSet to finalize the dataset structure.

Now, you have a nested ClientDataSet that corresponds to your JSON structure, and you can use this to bind your data. Please note that you will need to handle the data assignment from the JSON to the dataset manually or via another DataBinding mechanism.