Encode JSON to a FireDAC Memory Table without REST Request

At least twice this month I’ve been asked the question “Can I pass JSON to the TRESTDatasetAdapter component without binding it to a request?” and the short answer is no, you can’t. So what is the problem we’re trying to solve, and how can we go about it?

The Problem

The TRESTDatasetAdapter takes JSON data returned from a REST service an inserts it into a data set, usually an in-memory data set component. I imagine the main use for this is to display that data using the Live Bindings feature of Delphi / C++ Builder. The problem is that the JSON data must be returned from a REST service, and it must be well formatted as a straight array of JSON data.

Many REST services do not stick to good practice. Instead, they may return nested JSON data, or a container object around the data array. This kind of data cannot be handled by the TRESTDatasetAdapter component.

The Solution

[ See comments for bugs / updates ]

The solution, as indicated by the question I’m addressing, is to somehow go straight from a JSON string to the data set. This would allow you to make the request, pre-process the JSON data to be compatible, and then feed it into the data set for display. Unfortunately, the TRESTDatasetAdapter does not support this feature.

I solved this issue by creating my own custom equivalent component to TRESTDatasetAdapter which I named TJSONDatasetAdapter.
Here’s the source:

unit jsonadapter;

interface
uses
  System.SysUtils,
  System.Classes,
  System.Json,
  Data.DB;

type
  EDataTypeCoersion = class(Exception); //- Can't alter data-type for existing field definition.
  EDataTypeUnsupported = class(Exception); //- Can't support data-type from json data. (objects and arrays)
  EUnknownDataType = class(Exception); //- Unable to determine data type from json data.

type
  TJSONDatasetAdapter = class(TComponent)
  private
    fJSON: TStrings;
    fDatasetRef: TDataset;
    procedure SetDatasetRef(const Value: TDataset);
    procedure setJSON(const Value: TStrings);
    procedure SetFieldDefs(a: TJSONArray);
    procedure InsertData(a: TJSONArray);
  public
    constructor Create( aOwner: TComponent ); override;
    destructor Destroy; override;
  public
    procedure UpdateDataset;
  published
    property JSON: TStrings read fJSON write setJSON;
    property Dataset: TDataset read fDatasetRef write SetDatasetRef;
  end;

procedure Register;

implementation

procedure Register;
begin
  RegisterComponents('REST Client', [TJSONDatasetAdapter]);
end;

{ TJSONDatasetAdapter }

constructor TJSONDatasetAdapter.Create(aOwner: TComponent);
begin
  inherited Create( aOwner );
  fJSON := TStringList.Create;
  fJSON.Text := '[]';
  fDatasetRef := nil;
end;

destructor TJSONDatasetAdapter.Destroy;
begin
  fJSON.DisposeOf;
  fDatasetRef := nil;
  inherited Destroy;
end;

procedure TJSONDatasetAdapter.SetDatasetRef(const Value: TDataset);
begin
  fDatasetRef := Value;
  UpdateDataset;
end;

procedure TJSONDatasetAdapter.setJSON(const Value: TStrings);
begin
  if not assigned(value) then begin
    fJSON.Clear;
    exit;
  end;
  fJSON.Assign(Value);
  UpdateDataset;
end;

procedure TJSONDatasetAdapter.SetFieldDefs( a: TJSONArray );
var
  o: TJSONObject;
  e: TJSONPairEnumerator;
  p: TJSONPair;
  v: TJSONValue;
  n: string;
  idx: uint32;
  FieldDef: TFieldDef;
begin
  if a.Count<1 then begin
    exit;
  end;
  //- Loop through data to determine data-types.
  for idx := 0 to pred(a.Count) do begin
    v := a.Items[idx];
    if not (v is TJSONObject) then begin
      exit;
    end;
    o := v as TJSONObject;
    try
      e := o.GetEnumerator;
      if not e.MoveNext then begin
        exit;
      end;
      repeat
        p := e.GetCurrent;
        if not assigned(p) then continue;
        //- Get the name of the field, and ensure we have a field def.
        n := Lowercase(Trim(p.JsonString.ToString));
        n := StringReplace(n,'"','',[rfReplaceAll]);
        FieldDef := nil;
        if fDatasetRef.FieldDefs.IndexOf(n)>=0 then begin
          FieldDef := fDatasetRef.FieldDefs.Find(n);
        end;
        if not assigned(FieldDef) then begin
          FieldDef := fDatasetRef.FieldDefs.AddFieldDef;
          FieldDef.Name := n;
        end;
        //- Determine the type of field.
        v := p.JsonValue;
        if v is TJSONString then begin
          if (FieldDef.DataType=TFieldType.ftUnknown) then begin
            FieldDef.DataType := TFieldType.ftString;
          end;
        end else if v is TJSONNumber then begin
          if (FieldDef.DataType=TFieldType.ftUnknown) then begin
            FieldDef.DataType := TFieldType.ftFloat;
          end else if (FieldDef.DataType <> TFieldType.ftFloat) then begin
            raise EDataTypeCoersion.Create('');
          end;
        end else if v is TJSONBool then begin
          if (FieldDef.DataType=TFieldType.ftUnknown) then begin
            FieldDef.DataType := TFieldType.ftBoolean;
          end else if (FieldDef.DataType<>ftBoolean) then begin
            raise EDataTypeCoersion.Create('');
          end;
        end else if v is TJSONNull then begin
          //- Do nothing, another record may indicate data type.
        end else if v is TJSONObject then begin
          raise EDataTypeUnsupported.Create('');
        end else if v is TJSONArray then begin
          raise EDataTypeUnsupported.Create('');
        end;
      until not e.MoveNext;
    finally
      o := nil;
    end;
  end;
 //- Ensure that all field defs have known data types.
 if fDatasetRef.FieldDefs.Count<1 then begin
   exit;
 end;
 for idx := 0 to pred(fDatasetRef.FieldDefs.Count) do begin
   if fDatasetRef.FieldDefs[idx].DataType=TFieldType.ftUnknown then begin
     raise EUnknownDataType.Create('field: '+fDatasetRef.FieldDefs[idx].Name);
   end;
 end;
end;

procedure TJSONDatasetAdapter.InsertData( a: TJSONArray );
var
  idx: uint32;
  idy: uint32;
  v: TJSONValue;
  o: TJSONObject;
  FieldName: string;
begin
  if fDatasetRef.FieldDefs.Count<1 then begin
    exit;
  end;
  if a.Count<1 then begin
    exit;
  end;
  for idx := 0 to pred(a.Count) do begin
    v := a.Items[idx];
    if not (v is TJSONObject) then continue; //[ Exception here? ]
    o := v as TJSONObject;
    fDatasetRef.Insert;
    for idy := 0 to pred(fDatasetRef.FieldDefs.Count) do begin
      FieldName := fDatasetRef.FieldDefs[idy].Name;
      v := o.GetValue(FieldName);
      if assigned(v) then begin
        if v is TJSONString then begin
          fDatasetRef.FieldByName(FieldName).AsString := TJSONString(v).Value;
        end else begin
          fDatasetRef.FieldByName(FieldName).AsString := v.ToJSON;
        end;
      end;
    end;
    fDatasetRef.Post;
  end;
end;

procedure TJSONDatasetAdapter.UpdateDataset;
var
  o: TJSONObject;
  a: TJSONArray;
  v: TJSONValue;
begin
  if not assigned(fDatasetRef) then begin
    exit;
  end;
  fDatasetRef.Active := False;
  fDatasetRef.FieldDefs.Clear;
  try
    o := TJSONObject.ParseJSONValue(TEncoding.ASCII.GetBytes('{ "data": '+fJSON.Text+'}'),0) as TJSONObject;
  except
    on E: Exception do begin
     exit;
    end;
  end;
  if not assigned(o) then begin
    exit;
  end;
  v := o.GetValue('data');
  if not assigned(v) then begin
    exit;
  end;
  if not (v is TJSONArray) then begin
    exit;
  end;
  a := v as TJSONArray;
  if a.Count=0 then begin
    exit;
  end;
  SetFieldDefs(a);
  fDatasetRef.Active := True;
  InsertData(a);
end;

end.

You can also download this source with a package which may be installed into your IDE, Download Here: jsonadapt

So how do I use it?

Having installed the component, it should appear on your component palette under the “REST Client” category.
Here are some instructions for building a sample application for it:

1. Create a new application (VCL or FMX).
   (note, you may need to set your project path to include the location of jsonadapter.pas if you did not configure this during installation).
2. Drop a TFDMemTable onto your form.
3. Drop a TJSONDatasetAdapter adapter onto your form
4. Drop a TStringGrid onto your form.
5. Set the JSONDatasetAdapter1.Dataset property to FDMemTable1
6. Set the JSONDatasetAdapter1.JSON property to some JSON data (see example data below).
7. Use live bindings to bind your FDMemTable1 component to the string grid.

For good measure, I’ve recorded myself building this very demo here…
(Best viewed Full Screen, recorded in 1080p)

Here’s the sample JSON data that I used:

[{"county_name":"Del Norte","latitude":41.869383569541917,"longitude":-124.21444724840021,"spot_id":652,"spot_name":"Smith River Kellog Road"},
{"county_name":"Del Norte","latitude":41.786844846578227,"longitude":-124.25521494045159,"spot_id":653,"spot_name":"Point St George"},
{"county_name":"Del Norte","latitude":41.76951799906643,"longitude":-124.23922049300231,"spot_id":654,"spot_name":"Garths Reef"}]

Summary

As usual, my disclaimer:

This code is free to use as you see fit. The code is prepared for demonstration purposes only. I will not be held responsible for any damages caused by the use or misuse of this code.

So you now have the ability to push JSON data directly into a TFDMemTable without having to first attach a TRESTRequest component, go forth and consume JSON.

Thanks for Reading!