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?

klin · Accepted Answer · 2018-12-15 12:53:29Z

3

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))

answered Dec 15, 2018 at 12:53

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Postgresql - How to change JSONB data type?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related