Updating jsonb column

Queries and Resources
,
1

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.

Thoughts?

2

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.

3

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

4

that does work, thank you.

5

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.

6

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.

7

@Kabirdas
I am trying to run this query -
UPDATE access_management
SET meta = jsonb_insert( meta::jsonb,
'{status_update}',
COALESCE(meta::jsonb->'status_update', '[]'::jsonb) || {{status_select.value}}::jsonb
)::json
WHERE id={{table1.selectedRow.id}};
but it is giving the same error. Can you suggest me what to do please?

8

Hey @Harsh_Chandel!

Can you try using something like the following to format the status select value as a json object as opposed to an ordinary string?

UPDATE access_management
SET meta = jsonb_insert( meta::jsonb,
'{status_update}',
COALESCE(meta::jsonb->'status_update', '[]'::jsonb) || {{'["' + status_select.value + '"]'}}::jsonb
)::json
WHERE id={{table1.selectedRow.id}};

You may also want to use jsonb_set instead of jsonb_insert if you are ending to append values to an existing array at the status_update key.

9