Updating jsonb column

I am trying to update a jsonb column in a Postgres table.

The query I have tried to work is:

UPDATE config
SET codes = jsonb_set(codes::jsonb, '{defaultItem}', '{"id:"{{ form.data.textInput5 }}","name":"{{ form.data.textInput6 }}" }')
WHERE id={{ table1.selectedRow.data.int_id }}

The error I get is "invalid input syntax for type json".

When I remove the quotes around the textInput fields I get the same error.

When I hardcode the values in the json this update works fine.


I am at the point of "debugging" where I generate a query statement that works in pgAdmin but not in Retool.

UPDATE integration_config
SET codes = jsonb_set(codes::jsonb, '{defaultItem}', '{"id":"{{form.data.textInput5 }}","name":"{{ form.data.textInput6 }}"}')
WHERE id='{{ table1.selectedRow.data.int_id }}'

so there must be a bug in Retool. If someone (anyone) in Support can let me know that the bug is being worked on, that would be great.

Hey @David_Adams!

Can you try passing the entirety of the JSON object in {{}}? i.e.:

    UPDATE config
    SET codes = jsonb_set(codes::jsonb, '{defaultItem}', {{ {id: form.data.textInput5,name: form.data.textInput6 } }})
    WHERE id={{ table1.selectedRow.data.int_id }}

Edit: Ah! Jinx :sweat: sorry about the late reply here

that does work, thank you.

I have a number of columns to update and would like to send the entire JSON to the column.

when I try this:

UPDATE integration_config
SET codat='{"companyId":{{'"' + codatForm.data.codatCompanyId + '"'}},"defaultItem":{"id": {{'"' + codatForm.data.codatDefaultItemId + '"'}}, "name": {{'"' + codatForm.data.codatDefaultItemName + '"'}} }}'
WHERE id={{ table1.selectedRow.data.int_id }};

I get:

invalid input syntax for type json

isn't this valid JSON? It does work when I use pgAdmin to make the same request.

In order to avoid SQL injection attacks dynamic values are inserted into queries using prepared statements by default. Having it on means usually passing fully constructed values with {{}} as opposed to building them within the query itself (similar to the previous suggestion):

UPDATE integration_config
SET codat={{ {"companyId":codatForm.data.codatCompanyId,"defaultItem":{"id": codatForm.data.codatDefaultItemId, "name": codatForm.data.codatDefaultItemName } } }}
WHERE id={{ table1.selectedRow.data.int_id }};

Your way is definitely valid though! It's possible to turn prepared statements off making the entire query dynamic, in which case your example should work. But we generally recommend keeping them on if possible.