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
CodePudding user response:
Thanks to Edouard answer, i realized that i hadn't tested all the obvious possibilities.
'true'::jsonb
with just sigle quotes worked
UPDATE table_name
SET jsonb_column_name = jsonb_set(jsonb_column_name, '{yellow}', 'true'::jsonb, true)
CodePudding user response:
You don't need jsonb_set
for this example. As this is a jsonb
column, 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}';