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_userSET json = jsonb_set(json, '{roles}', to_jsonb('SYSTEM_ADMINISTRATOR')::jsonb, true);
UPDATE public.bo_userSET json = jsonb_set(json, '{roles}', to_jsonb('["SYSTEM_ADMINISTRATOR"]')::jsonb, true);
ERROR:  could not determine polymorphic type because input has type unknownSQL state: 42804

Kindly help me with the query

askedJul 20, 2021 at 5:29
Nitin Mukesh's user avatar
3
  • Sorry fixed the tag, it is v13CommentedJul 20, 2021 at 5: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"]?CommentedJul 20, 2021 at 5: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.CommentedJul 20, 2021 at 5: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" forjsonb_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.

answeredJul 20, 2021 at 6:02
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you very much for your answer. I also got my query working and posted below.
0

So this worked.

UPDATE public.bo_userSET json = jsonb_set(json, '{roles}', ('["SYSTEM_ADMINISTRATOR"]')::jsonb, true)where id = '??';
answeredJul 20, 2021 at 6:05
Nitin Mukesh's user avatar

2 Comments

That replaces the whole array, which is something entirely different than "to update the value at 0 index"
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.

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.