updating jsonb field in postgresql

Question

I'm trying to update a value in a jsonb column in postgresql.

// column 'settings'
{
  "country": "us",
  "language": "en"
}

I want to use prepared statements and I want to update a specific value, so not the whole jsonb object.

This does not work:

UPDATE users SET settings = jsonb_set(settings, '{country}', '$1'::jsonb)

I'm trying to cast the value of the prepared statement to jsonb, it does not work. Error is invalid input syntax for type json.

What is the correct way to update specific values in a jsonb field.

I'm using nodejs pg.

GMB · Accepted Answer · 2020-10-29 22:57:49Z

3

I think the following syntax should work in a prepared statement

UPDATE users SET settings = jsonb_set(settings, '{country}', to_jsonb($1::text))

answered Oct 29, 2020 at 22:57

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1