Bug in running jsonb field update query in postgresql

Queries and Resources
, ,
1

I want to access a specific value of jsonb and update only the value of the key. When writing sql directly, it works properly, but it does not work properly in Retool.

It seems that there is a problem when calling it as a {{}} value.
The query in question is:

UPDATE pig SET opinions = jsonb_set(opinions, ARRAY[{{currentOpinionKey.value}}]::TEXT[], '{{currentOpinionValue.value}}') WHERE id = {{currentReportDetailId.value}}

{{currentOpinionValue.value}} If a value is directly entered here, for example, the following query works properly in Retool.

UPDATE pig SET opinions = jsonb_set(opinions, ARRAY[{{currentOpinionKey.value}}]::TEXT[], '"123aaa"') WHERE id = {{currentReportDetailId.value}}
2

Hey @Will_Heo!

Can you try using the following to append double quotes onto the string you're passing through?

ARRAY[{{currentOpinionKey.value}}]::TEXT[], {{'"' + currentOpinionValue.value + '"'}})

Curious to know if that works! If not it might help to see exactly what currentOpinionValue.value is evaluating to.

1 Like
3

This was the perfect answer. thank you!

4