score:1

Accepted answer

but at the moment it is to update the value at 0 index

That can be done using an index based "path" for jsonb_set()

update bo_user
  set "json" = jsonb_set("json", '{roles,0}'::text[], '"SYSTEM_ADMINISTRATOR"')
where "json" #>> '{roles,0}' = 'Admin'

The "path" '{roles,0}' references the first element in the array and that is replaced with the constant "SYSTEM_ADMINISTRATOR"' Note the double quotes inside the SQL string literal which are required for a valid JSON string

The WHERE clause ensures that you don't accidentally change the wrong value.

score:0

So this worked.

UPDATE public.bo_user
SET json = jsonb_set(json, '{roles}', ('["SYSTEM_ADMINISTRATOR"]')::jsonb, true)
where id = '??';

More questions

More questions with similar tag