POSTGRES JSON: Updating array value in column

Question

I am using POSTGRES SQL JSON.

In json column the value is stored as array which I want to update using SQL query

{"roles": ["Admin"]}

The output in table column should be

{"roles": ["SYSTEM_ADMINISTRATOR"]}

I tried different queries but it is not working.

UPDATE public.bo_user
SET json = jsonb_set(json, '{roles}', to_jsonb('SYSTEM_ADMINISTRATOR')::jsonb, true);

---

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

---

ERROR:  could not determine polymorphic type because input has type unknown
SQL state: 42804

Kindly help me with the query

Answer 1

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.