Modifying feature's JSON attribute on PostGIS with QGIS

Question

I want to edit (with QGIS) an attribute of my feature stored in PostGIS. First, I select the feature using one of the buttons below.

Then I use the edit feature button (Modify the attributes of all selected features simultaneously). This opens a form view of the selected feature. I do not want to open the attribute table as it feels horrible to edit with the attribute table. The form view is a nice experience in editing the features.

I can do this easily on free text fields (green check mark) but somehow cannot edit the JSON part (red cross); the following picture.

I tried an alternative method using the SQL UPDATE [table] SET [col] = [value] WHERE id = [id] command. This is not acceptable as it is deemed (by me) dangerous and inconvenient even with the BEGIN transaction. Furthermore, this is a QGIS question.

How do I edit the JSON attribute field using the QGIS edit-selected feature?

Answer 1

Unlike jsonb, the regular json type is pretty much just a pre-validated text. You could

alter table your_table 
   alter column connectionpoints type text using (connectionpoints::text)
  --,add constraint is_valid_json check (connectionpoints is json)--pg16+
  --,add constraint is_valid_json check (pg_input_is_valid(connectionpoints,'json'))--pg16+
  ,add constraint is_valid_json check (connectionpoints::json#>>'{}' is not null);

To kind of have the cookie and eat it too: it makes that column a regular, QGIS-editable text, while still making sure it's a valid json. _{Demo at db<>fiddle}

Problem is, that requires explicit type casts to work with some functions and operators, so you'd probably have to add a generated column so that you don't break some other parts of your system that rely on that column being an actual json:

alter table your_table 
   alter column connectionpoints type text using (connectionpoints::text)
  --,add constraint is_valid_json check (connectionpoints is json)--pg16+
  --,add constraint is_valid_json check (pg_input_is_valid(connectionpoints,'json'))--pg16+
  ,add constraint is_valid_json check (connectionpoints::json#>>'{}' is not null)
  ,add column connectionpoints_j json generated always as (connectionpoints::json) stored;

Which is pretty wasteful, but technically it does let you indirectly edit the json column in QGIS: you edit the text, and that automatically pops up in the json that's generated based on it. Note that the generated column is read-only, so it'd might end up being useless if those other parts of the system required it to be not just a json, but a directly editable json.

You could also hide that table behind a view that casts the text value to ::json, but that would only help for reads. Whoever interacted with the table by inserting/updating, won't be able to do that through the view as connectionpoints wouldn't qualify as updatable.

ERROR:  cannot insert into column "connectionpoints" of view "your_table"
DETAIL:  View columns that are not columns of their base relation are not updatable.