TDAJSONDataStreamer - can the JSON layout be changed?

Data Abstract
1

I’m getting started with DataAbstract/Delphi and I’m using the ROHttpApiDispatcher and a DAJSONDataStreamer to return the contents of a query as JSON. I’m wondering if there is a way to change the layout/format of the generated JSON.

Here is a quick sample using the methods that I’m using for reference:

   vWhere := format('pk_id = %d', [AId]);
   vDataSet := Connection.NewDataset('select * from test_table where ' + vWhere) as IDADataset;
   vDataSet.LogicalName := 'foo';
   vDataSet.Open;

      Result := TROHttpApiResult.Create(HTTP_200_code, id_ContentType_application_json_charset_utf8,'',false);
      vJSON:=TDAJSONDataStreamer.Create(Self);
      vJSON.Initialize(Result,aiwrite);
      try
        vJSON.WriteDataset(vDataSet, [woRows], -1);  
      finally
        vJSON.Finalize;
      end;

The resulting JSON is not what I was expecting. The result has a “fields” array, followed by a “rows” array:

{
    "datasets": [
        {
            "name": "foo",
            "data": {
                "fields": [
                    {
                        "name": "FIELD_NAME_HERE"
                    },
             .../snip/...
               "rows": [
                    [
                        1,
             .../snip/...

I get that this is more compact, as the field names are not repeated in each row of the data, but it is more difficult to read.

Is there an example using the this JSON to load into a TDataSet in delphi or c#?

Is there a way to change the layout of the JSON generated?

What I’m looking for is something more like this:

{
  "datasets": [
    {
      "name": "foo",
      "data": [
        {
          "Id": 1,
          "Name": "Customers 1",
          "Birth": "2014-01-22 14:05:03"
        },
        {
          "Id": 2,
          "Name": "Customers 2",
          "Birth": "2014-01-22 14:05:03"
        }
      ]
    }
  ]
}

Thanks,
Leo

1 Like
2

Not as of right now I believe, no. Essentially, you’d want to create a custom copy of there JsonStreamer that serializes the data in the format you prefer.

1 Like
3

Hi,

You can recreate JSON.
something like

temp := JSON_ParseStream(Result); 
l_datasets := temp.AsObject.FindItem(pn_Datasets);
temp1 := TROJSONValue.Create(nil);
temp1.AsObject:= TROJSONObject.Create;
l_datasets1 := temp1.AsObject.AddArrayProperty(pn_Datasets).AsArray;
for i := 0 to l_datasets.Count-1 do begin
// copy datasets node according to your rules from l_datasets to l_datasets1 object
// check examples in uDAJSONDataStreamer.pas
end;
Result.Size := 0;
temp1.SaveToStream(Result);
temp1.Free;
temp.Free;
1 Like
4

Hi Evgeny,

I’m not that familiar with the internal workings of uDAJSONDataStreamer, and i’m having trouble determining what to use within uDAJSONDataStreamer to copy the nodes for the field names and values.

I looked at TDAJSONDataStreamer.InternalDoWriteDataset() as an idea of what to do, but it is using private methods - GetDataArraysByPropName, etc.

I made some minor changes to the example you provided, but need a hint on how to pull the fields and values from l_datasets and create the a “field”:“value” output using TROJSONArray.

      result.Position := 0;   // **added this**
      temp := JSON_ParseStream(Result);
      l_datasets := temp.AsObject.FindItem(pn_Datasets);
      temp1 := TROJSONValue.Create(nil);
      temp1.AsObject:= TROJSONObject.Create;
      l_datasets1 := temp1.AsObject.AddArrayProperty(pn_Datasets).AsArray;
      for i := 0 to l_datasets.AsArray.Count-1 do begin  // **added AsArray**
         // copy datasets node according to your rules from l_datasets to l_datasets1 object
         // check examples in uDAJSONDataStreamer.pas
         // need a hint here :-)
      end;
      Result.Size := 0;
      temp1.SaveToStream(Result);
      temp1.Free;
      temp.Free;

Anything you can provide to push me in the right direction would be appreciated.

Thanks again,
Leo

5

As a follow-up, I am using my own class to create a JSON result from a TDataSet, and then loading that into the result using the LoadFromString() method:

   vDataset.Open;
   Result := TROHttpApiResult.Create(HTTP_200_code, id_ContentType_application_json_charset_utf8,'',false);
   result.LoadFromString( PrettyJSON(vDataSet) );

The prettyJSON calls a class to convert a TDataSet to JSON the way I wanted. There are a few tools for this already written in delphi that I was able to google around and find.

Thanks!

6

Hi,

try this simple code:

  result.Position := 0;
  temp := JSON_ParseStream(Result);
  l_datasets := temp.AsObject.FindItem(pn_Datasets).AsArray;
  temp1 := TROJSONValue.Create(nil);
  temp1.AsObject:= TROJSONObject.Create;
  l_datasets1 := temp1.AsObject.AddArrayProperty(pn_Datasets).AsArray;
  for i := 0 to l_datasets.Count - 1 do begin
    l_dataset := l_datasets[i].AsObject;
    l_data := l_dataset.GetObjectItemByName('data', False);
    l_fields := l_data.GetArrayItemByName('fields', False);
    l_rows := l_data.GetArrayItemByName('rows', False);

    l_dataset1 := l_datasets1.AddObject.AsObject;
    l_dataset1.AddStringProperty('name', l_dataset.ItemByName('name').AsString);
    l_data1 := l_dataset1.AddArrayProperty('data').AsArray;
    for j := 0 to l_rows.Count - 1 do begin
      l_rowData := l_rows[j].AsArray;
      l_rowData1 := l_data1.AddObject.AsObject;
      for k := 0 to l_rowData.Count - 1 do
        l_rowData1.AddVariantProperty(l_fields[k].AsObject[0].asString, l_rowData[k].VarValue);
    end;
  end;
  Result.Size := 0;
  temp1.SaveToStream(Result);
  temp1.Free;
  temp.Free;