mORMot Open Source

JD

Member

Registered: 2015-08-20

Posts: 101

Using ISQLDBRows' FetchAllToJSON with PostgreSQL's JSON functions

Hi there all,

I am using an interface based service to get data from a PostgreSQL database. Some of the data comes from functions that already return JSON data using PostgreSQL JSON functions like row_to_json(_) and json_agg(). The problem is that the interface service returns JSON with escape characters. For example instead of returning

[{"resultat": {"resource": "/genre", "genre": [{"numero": 1,"description": "Femme"},{"numero": 2,"description": "Homme"}]}}]

generated by the PostgreSQL database, FetchAllToJSON returns

[{\"resultat\":\"{\\\"resource\\\":\\\"/genre\\\",\\\"genre\\\":[{\\\"numero\\\":1,\\\"description\\\":\\\"Femme\\\"}, \\n {\\\"numero\\\":2,\\\"description\\\":\\\"Homme\\\"}]}\"}]

The relevant lines in the service code are

function TRESTMethods.Gender(aURI: string; aID: integer): RawJSON;
begin
  ...
      Res := aServer.fDbProps.Execute(Format('select * from genre(%s)', [QuotedStr(aURI)]), []);
      Result := Res.FetchAllAsJSON(True);
  ...
end;

Is there an alternative to using FetchAllAsJSON that can just accept and return the PostgreSQL generated JSON as-is?

Thanks,

JD

JD

Member

Registered: 2015-08-20

Posts: 101

Re: Using ISQLDBRows' FetchAllToJSON with PostgreSQL's JSON functions

This does not work either even though I set TTextWriterKind to twNone.

      S := EmptyStr;
      while Res.Step do
        S := S + VariantSaveJSON(Res['resultat'], twNone);
      Result := S;

I also tried S := S + VariantToString(Res['resultat']); and there are still escape characters in the JSON string. Has anyone dealt with this sort of problem before?

Thanks,

JD

JD

Member

Registered: 2015-08-20

Posts: 101

Re: Using ISQLDBRows' FetchAllToJSON with PostgreSQL's JSON functions

I finally settled on the following code

      S := EmptyStr;
      while Res.Step do
        S := S + VariantSaveJSON(Res['resultat'], twNone);
      Result := S;

Browsers and REST clients show the JSON with escape characters but my Lazarus client application displays the JSON without escape characters which is OK for the moment. I don't know why this is so. If anyone has an idea why it is like that, please let me know.

JD

Last edited by JD (2017-10-06 08:38:41)