Parsing Json to retrieve nested record

[bzwirs 3]

I am trying to parse a json response to retrieve a nested record without any success.  Example of the json response is:

 

{
  "status": "1",
  "info": "success",
  "page_info":   {
    "total_page": "1",
    "record_per_page": "1",
    "current_page": "1",
    "total_record": "1"
  },
  "records": [  {
    "machine_id": "25884",
    "imei": "",
    "install_date": "",
    "last_report": "",
    .......
    .......
    "coin_mech":     {
      "coin_mech_id": "12345",
      "cm_model": "",
      "serial_no": "",
      "revision": "",
      "coin_in_tube": "",
      "tubes":       [
                {
          "tube_id": "3469107",
          "tube_no": "4",
          "coin_unit": "100",
          "coin_count": "93",
          "coin_inserted": "0",
          "coin_dispensed": "0",
          "tube_full": "0"
        },
                {
          "tube_id": "3469105",
          "tube_no": "5",
          "coin_unit": "200",
          "coin_count": "9",
          "coin_inserted": "0",
          "coin_dispensed": "0",
          "tube_full": "0"
        }
      ]
    }
  }]
}

 

The info I am after is the records for "tubes" which can contain up to 5 records (2 in the above example) .  Have tried several of the coding examples that I found in stackoverflow without success although I have not found an example with a similar record structure.   

 

Can someone please advise me on how to retrieve those records (would be great if in a dataset).

 

Thanks in advance.

 

Bill Zwirs

[Uwe Raabe 1891]

Which Delphi version are you using?

[bzwirs 3]

Sorry...normally first thing I add.   FMX application on Delphi 11.3.

 

Bill

[Uwe Raabe 1891]

The above JSON has an array "records", where each item contains a "coin_mech" object, which itself contains the "tubes" array. Are you interested in the "tubes" from one specific "records" item or do you want to collect all "tubes" arrays from all "records" items.

[bzwirs 3]

I need to collect all tubes data.  At this stage, specifically the coin_unit and coin_count fields.

 

Bill Zwirs

[Uwe Raabe 1891]

You can use a TRESTResponseDataSetAdapter and connect its Response property to your TRESTResponse instance (alternatively use a TRESTResponseJSON instance to provide a TJSONObject). Iterating through the records list and adding each tubes items to your dataset may need some additional coding around, though.

[mytbo 5]

7 hours ago, bzwirs said:

I need to collect all tubes data.  At this stage, specifically the coin_unit and coin_count fields.

With mORMot it can be solved as follows (all tubes for all records, or tubes for record with index):

uses
  mormot.core.base,
  mormot.core.text,
  mormot.core.os,
  mormot.core.variants,
  mormot.db.rad.ui;

function ShowTubesForRecordIndex(pmGrid: TDBGrid; const pmcDBFileName: TFileName; pmDBRecIdx: Integer): Boolean;
var
  docDB: TDocVariantData;
begin
  Result := False;
  if pmGrid = Nil then Exit; //=>

  FreeAndNil(pmGrid.DataSource);
  if docDB.InitJsonFromFile(pmcDBFileName, JSON_FAST_FLOAT) then
  begin
    pmGrid.DataSource := TDataSource.Create(pmGrid);
    var docPath: PDocVariantData := docDB.A['records']._[pmDBRecIdx].O['coin_mech'].A['tubes'];
    pmGrid.DataSource.DataSet := DocVariantToDataSet(pmGrid.DataSource, Variant(docPath^));
    Result := (pmGrid.DataSource.DataSet <> Nil);
  end;
end;

function ShowTubesOfAllRecords(pmGrid: TDBGrid; const pmcDBFileName: TFileName): Boolean;
var
  docDB: TDocVariantData;
  tubes: TVariantDynArray;
begin
  Result := False;
  if pmGrid = Nil then Exit; //=>

  FreeAndNil(pmGrid.DataSource);
  if docDB.InitJsonFromFile(pmcDBFileName, JSON_FAST_FLOAT) then
  begin
    var docTubesPath: PDocVariantData;
    var docRecordsPath: PDocVariantData := docDB.A['records'];
    for var i: Integer := 0 to docRecordsPath.Count - 1 do
    begin
      docTubesPath := docRecordsPath._[i].O['coin_mech'].A['tubes'];
      for var n: Integer := 0 to docTubesPath.Count - 1 do
      begin
        SetLength(tubes, Length(tubes) + 1);
        tubes[High(tubes)] := Variant(docTubesPath._[n]^);
      end;
    end;

    if Length(tubes) > 0 then
    begin
      pmGrid.DataSource := TDataSource.Create(pmGrid);
      pmGrid.DataSource.DataSet := VariantsToDataSet(pmGrid.DataSource, tubes, Length(tubes), [], []);
      Result := (pmGrid.DataSource.DataSet <> Nil);
    end;
  end;
end;

Used as follows:

var fileName: TFileName := MakePath([Executable.ProgramFilePath, 'DocDB.json']);
ShowTubesForRecordIndex(DBGrid, fileName, 0);
// ShowTubesOfAllRecords(DBGrid, fileName);

I have published an article on topic mORMot DocVariant here (forum Delphi-PRAXIS). Here is the translation into English with Google Translator. The result is not perfect (rather not so good), also some formatting is destroyed, but it is readable.

 

With best regards
Thomas

[aehimself 388]

Including the checks if a specific branch exists or not, you also can use the built-in System.Json unit:

Var
  jo, coinmech: TJSONObject;
  recenum, tubeenum: TJSONValue;
  records, tubes: TJSONArray;
begin
  jo := TJSONObject(TJSONObject.ParseJSONValue(Memo1.Text));

  If Not Assigned(jo) Then
    Exit;

  Try
    records := jo.GetValue<TJSONArray>('records');

    If Not Assigned(records) Then
      Exit;

    For recenum In records Do
    Begin
      coinmech := recenum.GetValue<TJSONObject>('coin_mech');

      If Not Assigned(coinmech) Then
        Continue;

      tubes := coinmech.GetValue<TJSONArray>('tubes');

      If Not Assigned(tubes) Then
        Continue;

      For tubeenum In tubes Do
      Begin
        WriteLn('Tube found, ID:' + tubeenum.GetValue<String>('tube_id'));
        // ...
        // Add a new record in a MemTable...?
      End;
    End;
  Finally
    FreeAndNil(jo);
  End;

The code can be siplified significantly but this way you can see what is happening, how TJSONObject handling works. The code above produced the following output:

[bzwirs 3]

Thank you to all.  This gives me a lot to try so will let you know at a later date whatever has ended up working for me.

 

Thanks again to everyone.

 

regards

 

Bill Zwirs

[bzwirs 3]

Hi,

 

Ended up using example from aehimself.  That was the easiest to implement in my app and worked straight away.

 

regards

 

Bill