Search code examples
postgresqlcastingjsonb

Changing the type of value in JSONB column in Postgres

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.
Solution

  • 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;