Update to remove double quotes from number jsonb postgresql

I am using PostgreSQL and I am trying to run an update for jsonb.

I want “2W” to change to just the number 2.

So the query below removes the W, but leaves it as “2”.

How I would go about removing the double quotes ?

currently it looks like {“size”: “2W”} and I would like it to look like {“size”: 2}

UPDATE x
SET x = jsonb_set(x, '{size}', ('"' || replace(x->>'size', 'W', '') || '"')::jsonb)
WHERE x IN ('')

Answer

You can use to_jsonb() for this:

jsonb_set(x, '{size}', to_jsonb(replace(x->>'size', 'W', '')::int))

Demo on DB Fiddle:

select jsonb_set(x, '{size}', to_jsonb(replace(x->>'size', 'W', '')::int))
from (values('{"size": "2W"}'::jsonb)) as t(x)

| jsonb_set   |
| :---------- |
| {"size": 2} |

You can replace ::int with ::numeric to handle decimal values if needed.