Update new column with value from jSON in the same row Postgres

Question

I had a table with 20k products. In this table I had a jSON column which contained several information about the product. I had to add a new column by a requirement of my boss which will contain the model name (which is inside the jSON). How can I do it? My table product has id = id of the product product_description = jSON with many details about the product and now a new column model_name which my boss wants available outside the jSON

I usually did this query:

SELECT "productInfo"->'product_info'->>'model_name' AS model_name FROM inv.product WHERE id = 1

to obtain the model_name

I want to update all the products so the model_name is now in the new column model_name but i couldn't do it because i'm not familiar with mass updates.

What i want to do in words is:

UPDATE inv.product SET model_name = ( QUERY TO SELECT THE VALUE OF THE model_name ) FOR EACH ROW

Ihor Romanchenko · Accepted Answer · 2013-09-25 18:41:43Z

2

Try something like:

UPDATE inv.product
SET model_name = "productInfo"->'product_info'->>'model_name'
WHERE id = 1;

Or

UPDATE inv.product
SET model_name = "productInfo"->'product_info'->>'model_name';

If you want to update all records in inv.product.

answered Sep 25, 2013 at 18:41

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

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

1 Comment

Tales Over a year ago

Thanks, that's the right answer, in both cases, i figured it out a few minutes ago, and didn't realize that the answer was so simple!

Collectives™ on Stack Overflow

Update new column with value from jSON in the same row Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related