I tried many different things that I gathered here and there (official docs, blog posts, SO, …) but didn't succeed, so here's my question to you all:
Given this table:
basik=# select id, jsonb_pretty(range_price_list_values::jsonb) from product;
id | jsonb_pretty
--------------------------------------+--------------------------
cc80c862-c264-4bfe-a929-a52478c8d59e | [ +
| { +
| "to": 10, +
| "from": 5, +
| "price": 1 +
| }, +
| { +
| "to": 20, +
| "from": 15, +
| "price": 1298000+
| }, +
| { +
| "to": 30, +
| "from": 25, +
| "price": 500000 +
| } +
| ]
How to multiply by 1000 the price key of each element of each row of the table ?
PS: my failed tentative was to look around jsonb_* functions and window functions:
WITH prices as (select id, jsonb_array_elements(range_price_list_values::jsonb) from product)
UPDATE product SET range_price_list_values = JSONB_SET(
range_price_list_values::jsonb,
'{' || price.rank || ',price}', jsonb_extract_path('{' || price.rank || ',price}')::int * 1000, false
)::json;
Thanks for taking time to read! :)
You'll need a sub-select, as you want to update multiple fields in your JSON:
update product
set range_price_list_values = (
select jsonb_agg(case
when jsonb_typeof(elem -> 'price') = 'number'
then jsonb_set(elem, array['price'], to_jsonb((elem ->> 'price')::numeric * 1000))
else elem
end)
from jsonb_array_elements(range_price_list_values::jsonb) elem
)::json;
Note: this will only update numeric price keys, otherwise an exception would be thrown, when a price is not a number.