ZEOS and Postgres json columns

[jhvhs]

Hi everyone!

I have an application that uses a few columns backed by the PostgreSQL JSON data type. I have found a number of JSON-related database posts, but those mostly were concerned about serialising existing data into JSON. In contrast, I am storing some JSON value entered manually by the user in a column that is automatically validated by the DB server.

The problem I have is this - by default Field editor for TZTable does not recognise the column data type and adds those fields as TField instances. Binding those directly to DBMemo result in the inability to save data - an exception is raised before Post => EDatabaseError "Invalid type conversion to String in field [field_name]". Another strangeness in this case is that the default value showing up in the memo is a pair of brackets.

Adding those fields explicitly as a TStringField allows text input, and won't raise any exception during Post, but a click of the refresh button will show that nothing was saved. Seems that for whatever reason, valid JSON entered in the memo is rejected by Postgres. For instance, some quote escaping may be taking place.

Tracing my way through lazarus and zeos code via debugger, I was sadly unable to find the problem. Is there a simple way to find what exact value is sent to the backend and why? And if escaping is distorting the data, is there a way to override the behaviour?

Any help on this topic would be most appreciated.

Best regards,

jhvhs.

[goodname]

First good to see that someone has found a good use for the PostgreSQL JSON data type. The bad news is that it is relatively new and AFAIK ZEOS and SQLdb don't know about the JSON data type. Suggest that you use a VIEW of the table and cast the JSON field to varchar(255). You can cast to 'text' as well and set onGetText events.

As for writing to the database FPC has a JSON unit that you can use. The string output from the JSON unit should be recognized by PostgreSQL.

[jhvhs]

@Goodname, thank you very much for the suggestion. Fortunately, there is no problem with displaying data - TStringField does the trick. 

As for writing to the database FPC has a JSON unit that you can use. The string output from the JSON unit should be recognized by PostgreSQL.

I have used the FPC JSON unit before. Could you, please elaborate on how to hook it up for a specific field? Or would you recommend running an extra query OnAfterPost of the corresponding TZTable instance as a quick and dirty solution?

Best regards,

jhvhs

[goodname]

Was thinking in terms of a parameterized query. Use the JSON unit to build a JSON structure then using the string output to set a parameter. Would not have to run a separate query but just set the SQLInsert and SQLUpdate custom queries. I typically use SQLDB and I'm guessing that ZEOS has the same ability.

Just remembered that a JSON DataSet was released with FPC 2.6.2. I have never used it before so would be interested in knowing if you can use it.
http://forum.lazarus.freepascal.org/index.php/topic,20046.0.html

[jhvhs]

Hi, @Goodname.

I have ended up using a TStringGrid as a UI for the JSON column. Seems that it provides better UX (less error-prone), and was the quickest solution for me.

I populate the control each time the DataSource onDataChanged fired, and commit the data back using a parametrised query, as you have kindly suggested, each time the OnAfterPost fires on the DataSet.

Just remembered that a JSON DataSet was released with FPC 2.6.2. I have never used it before so would be interested in knowing if you can use it.
http://forum.lazarus.freepascal.org/index.php/topic,20046.0.html

The JSON DataSet serves a different purpose - it is an in-memory DataSet backed by JSON storage. I was thinking about using it, however, I could not find a way to enable it in lazarus.

Thank you very much for your help!

Best regards,

jhvhs.

PS. When I have tried to add the JSON field as a TStringField, it displayed properly in a TDBMemo, but when posting data, NULL was sent to PostgreSQL in the update statement instead of TDBMemo contents:

Code: [Select]

LOG:  execute 214971247: UPDATE public.products SET extra_attributes=$1 WHERE id=$2
DETAIL:  parameters: $1 = NULL, $2 = '4'
Do you think it's a bug I should report?

[goodname]

Do you think it's a bug I should report?

I'm not sure if it is a bug or not. If you think it is a bug then it may be worth building a short code block so that others can reproduce the bug. People more familiar with the expected behaviour can look at it.