mORMot Open Source

Bo

Member

From: Melbourne

Registered: 2016-07-04

Posts: 48

Website

How do I get every name:value pair from a Json?

Hi,

After I get the variant from _Json, how do I get every name:value pair down to the very most deep ones in the hierarchy of a Json?
For example:

var
  v : Variant;
begin
  v := _Json('{"abc":123,"b":"c", "c": {"d":"e"}, "e": [1,2,3]}');

then I want to list the name(Json path):value pair of the most deep ones (leaves in the hierarchy):

$.abc:123
$.b:c
$.c.d:e
$.e[0]:1
$.e[1]:2
$.e[2]:3

here is my code:

function GetPathAndValue( JDOC: TDocVariantData; AParentPath: string ='$'): TList<TSQLResourceIndex>;
var
  LPathValues: TList<TSQLResourceIndex>;
  LDoc : TDocVariantData;
  i, j: Integer;
  LVal : TDocVariantData;
  LIdx, LNewIdx : TSQLResourceIndex;
  LList : TList<TSQLResourceIndex>;
begin
  LDoc := JDOC;
  LPathValues := TList<TSQLResourceIndex>.Create;
  for i := 0 to LDoc.Count -1 do
  begin
    try
      LVal := TDocVariantData(LDoc.Values[i]); //conversion is failed when the value is an integer
    except
      LIdx := TSQLResourceIndex.Create;
      LIdx.JPath := AParentPath + '.' + LDoc.Names[i];
      LIdx.JValue := LDoc.Values[i];
      LPathValues.Add(LIdx);
      Continue;
    end;
    if LVal.Kind = dvObject then
    begin
      LList := GetPathAndValue(LVal, AParentPath+'.'+LDoc.Names[i]) ;
      for LIdx in LList do
      begin
        LNewIdx := TSQLResourceIndex.Create;
        LNewIdx.JPath := LIdx.JPath;
        LNewIdx.JValue := LIdx.JValue;
        LPathValues.Add(LNewIdx);
      end;
      LList.Free;
    end
    else if (Lval.Kind = dvArray) then
    begin
      for j := 0 to LVal.Count -1 do
      begin
        LList := GetPathAndValue(TDocVariantData(LVal.Values[j]),AParentPath+'.'+LDoc.Names[i]);
        for LIdx in LList do
        begin
          LNewIdx := TSQLResourceIndex.Create;
          LNewIdx.JPath := LIdx.JPath;
          LNewIdx.JValue := LIdx.JValue;
          LPathValues.Add(LNewIdx);
        end;
        LList.Free;
      end;
    end
    else begin
      LIdx := TSQLResourceIndex.Create;
      LIdx.JPath := AParentPath + '.' + LDoc.Names[i];
      LIdx.JValue := LDoc.Values[i];
      LPathValues.Add(LIdx);
    end;
  end;
  Result := LPathValues;
end;

GetPathAndValue(TDocVariantData(v));

Although above code does the job, but I was wondering if there is a better code for checking what is in the value, instead of doing

    try
      LVal := TDocVariantData(LDoc.Values[i]); //conversion is failed when the value is an integer
    except
    ...
    end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: How do I get every name:value pair from a Json?

1. TDocVariantData(LDoc.Values[]) will fail for sure: if Values[] is an integer, it is not a TDocVariantData.
BTW, you should better use _Safe() to avoid such failure or unexpected access violation, if you are not sure that the value is a DocVariant.

2. Use 'const' for TDocVariantData parameters (like with variant values), to avoid an hidden copy when passing the value.
And avoid making a local copy to LDoc: it doesn't make sense, for sure.

3. You don't show how your JDoc is filled.

Bo

Member

From: Melbourne

Registered: 2016-07-04

Posts: 48

Website

Re: How do I get every name:value pair from a Json?

Hi ab,

Thanks for your reply.

I filled up JDoc by using _Json function and then cast  it to a TDocVariantData:

var
  v : Variant;
  l  : TList<TSQLResourceIndex>;
begin
  v := _Json('{"abc":123,"b":"c", "c": {"d":"e"}, "e": [1,2,3]}');
  l := GetPathAndValue(TDocVariantData(v));

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: How do I get every name:value pair from a Json?

You can directly access and add the LDoc.Values[] items, recursively, using _Safe(LDoc.Values[])^ and a local PDocVariantData to identify which sub-items are in fact another array of object.

Bo

Member

From: Melbourne

Registered: 2016-07-04

Posts: 48

Website

Re: How do I get every name:value pair from a Json?

I replace

    try
      LVal := TDocVariantData(LDoc.Values[i]); //conversion is failed when the value is an integer
    except
      LIdx := TSQLResourceIndex.Create;
      LIdx.JPath := AParentPath + '.' + LDoc.Names[i];
      LIdx.JValue := LDoc.Values[i];
      LPathValues.Add(LIdx);
      Continue;
    end;

with

      LVal := _Safe(LDoc.Values[i])^;   

and it seems working fine.

FYI, I then updated database (Sqlite) with the list of LIdx (TSQLResourceIndex), it took about 4~5 seconds to insert 35 records, I did not satisfy the performance but then I found the Batch updating in the performance sample, I switched to the batch updating and bang, it only took <200ms! Amazing, great work ab.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: How do I get every name:value pair from a Json?

Check the doc about SQLite3 performance.
See https://synopse.info/files/html/Synopse … ml#TITL_60

Inserting 35 records could be below 1 ms, with a batch using automatic transactions over exclusive SQLite3.