Update bool value in a jsonb column - jsonb_set

Question

With the following json exemple:

{
  "red": false,
  "blue": false,
  "yellow": false
}

I have to update one of the elements to true and the expected result is:

{
  "red": false,
  "blue": false,
  "yellow": true
}

First, i tried to update this way:

UPDATE table_name
   SET jsonb_column_name = jsonb_set(jsonb_column_name, '{yellow}', ('"true"')::jsonb, true) 

But the result was

{
  "red": false,
  "blue": false,
  "yellow": "true"
}

not what i want, its a string, not bool

Also tried:

UPDATE table_name
   SET jsonb_column_name = jsonb_set(jsonb_column_name, '{yellow}', true, true)

But i got an error, that makes sense, the 3rd parameter has to be jsonb

SQL Error [42883]: ERROR: function jsonb_set(jsonb, unknown, boolean, boolean) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

And i cannot make true::jsonb because bool cant be cast to jsonb:

SQL Error [42846]: ERROR: cannot cast type boolean to jsonb

Ther is another way to do this? no need to use jsonb_set, i think i can user str_replace an then convert to jsonb but i don't know if its safe

Answer 1

You don't need jsonb_set for this example. As this is a jsonbcolumn, you can simply append the new value and the existing key/value pair will be replaced with the new one.

update table_name
   set jsonb_column_name = jsonb_column_name || '{"yellow": true}';