Postgresql - How to change JSONB data type?

Question

I have a table which includes jsonb column "details" with wrong data type as follow:

select id,details from products;

id |              details                
---+-----------------------------------------
 1 | {"price": "310", "supplier": "VendorA"}
 2 | {"price": "250", "supplier": "VendorB"}

Here I would like to change data type of "price" to integer which is stored as string currently. Desired result is as follows:

id |              details             
---+-----------------------------------------
 1 | {"price": 310, "supplier": "VendorA"}
 2 | {"price": 250, "supplier": "VendorB"}

I will appreciate if you can guide me how to achieve it?

Answer 1

You can cast the value to json number using the function to_jsonb():

select id, jsonb_set(details, '{price}', to_jsonb((details->>'price')::int))
from products

 id |               jsonb_set               
----+---------------------------------------
  1 | {"price": 310, "supplier": "VendorA"}
  2 | {"price": 250, "supplier": "VendorB"}
(2 rows)

The update statement may look like this:

update products
set details = jsonb_set(details, '{price}', to_jsonb((details->>'price')::int))