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:

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). Drop a TFDMemTable onto your form. Drop a TJSONDatasetAdapter adapter onto your form Drop a TStringGrid onto your form. Set the JSONDatasetAdapter1.Dataset property to FDMemTable1 Set the JSONDatasetAdapter1.JSON property to some JSON data (see example data below). 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!