Changing the type of value in JSONB column in Postgres

Question

I have the 'subject' column of JSONB type that stores JSON objects. Examples: {"team": "1234", "user": 5678} or {"org": 123} or {"team": 1234}.

What query should I use to change all the occurrences of {"team": "1234", ...} to {"team": 1234, ...}?

I tried:

UPDATE the_table SET subject = jsonb_set(subject, '{team}', (subject->>'team')::int)

but i get:

ERROR: function jsonb_set(jsonb, unknown, integer) does not exist
LINE 2: SET subject = jsonb_set(subject, 'team', (subject->>'team'):... 
                      ^ 
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

@a_horse_with_no_name No this will empty the second record: dbfiddle.uk/… — S-Man
– S-Man, Commented Jul 4, 2019 at 12:26
@a_horse_with_no_name Please have a look at the fiddle. Isn't it as you meant. This results in an empty result if "team" is not found in the json (see second example record) — S-Man
– S-Man, Commented Jul 4, 2019 at 12:29

S-Man · Accepted Answer · 2019-07-04 12:24:45Z

3

Just cast the subject->>'team' result directly into jsonb instead of int. Don't forget to add a WHERE filter because otherwise your second record will be deleted.

demo:db<>fiddle

UPDATE the_table 
SET subject = jsonb_set(subject, '{team}', (subject->>'team')::jsonb)
WHERE subject->>'team' IS NOT NULL;

answered Jul 4, 2019 at 12:24

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

alternatively where subject ? 'team'

Forseti Over a year ago

Ok, that works. But why? Originally at subject.team there were strings and after casting to JSONB suddenly there are ints. I don't understand it.

S-Man Over a year ago

->> gives out the text representation of the value, without any " chars. If you cast this directly into a json you get the without-"-representation which, in fact, is json. You approach war the right one. The only disadvantage was that your valid int cast results... in an int. This is not accepted as parameter type. So you could have cast it into ::text::jsonb afterwards.. or do it directly

Collectives™ on Stack Overflow

Changing the type of value in JSONB column in Postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related