0

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

Nitin Mukesh
  • 4,266
  • 6
  • 22
  • 26
  • Sorry fixed the tag, it is v13 – Nitin Mukesh Jul 20 '21 at 05:46
  • This would be so much easier with a properly normalized data model. I assume, something like `["Admin", "User"]` should be changed to `["SYSTEM_ADMINISTRATOR", "User"]`? –  Jul 20 '21 at 05:49
  • Yeah that would be another scenario, but at the moment it is to update the value at 0 index. The second would be to add in Array. – Nitin Mukesh Jul 20 '21 at 05:55

2 Answers2

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.

0

So this worked.

UPDATE public.bo_user
SET json = jsonb_set(json, '{roles}', ('["SYSTEM_ADMINISTRATOR"]')::jsonb, true)
where id = '??';
Nitin Mukesh
  • 4,266
  • 6
  • 22
  • 26
  • 2
    That replaces the whole array, which is something entirely different than "to update the value at 0 index" –  Jul 20 '21 at 06:08
  • Yes, I understand that. Basically when the first user register in the system it is assigned a user role which need to be replaced with Admin role. This is the only time user info is to be updated using DB, everything else is done using application. Your query is going to help within the application. – Nitin Mukesh Jul 20 '21 at 06:09