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.

enter image description here

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.

enter image description here

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

enter image description here

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?

• Ian Turton: I suspect that the answer is not to store JSON in a postgis table, and if you do don't let people edit it.
• Vince: Note that PostGIS isn't storing that JSON, PostgreSQL is. PostGIS is the geometry/geography type implementation for PostgreSQL. So the Question is about QGIS-PG access without corrupting other fields, for which a view or stored procedure would be useful.
• sutan: @IanTurton Thank you. Unfortunately, that is not feasible, and the requirement is to edit jsonb column. This is easily done with HTML forms and server support, but it would be so great if we could do this with QGIS. Much more straightforward with QGIS.
• sutan: @Vince Thank you for clarifying that. Yes, it is Qgis-Postgresql. Saying Postgis is so common these days instead of postgresql.

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}

enter image description here

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;

enter image description here

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.

• sutan: I see, so alter the column from jsonb to text. Thank you for the feedback.