multiply a value of each item of a json array with postgres 9.6

Question

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

discrete_price_list_values is different from range_price_list_values?.. — Vao Tsun
– Vao Tsun, Commented Feb 24, 2017 at 8:43

pozs · Accepted Answer · 2017-02-24 09:07:59Z

2

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.

http://rextester.com/PQN70851

answered Feb 24, 2017 at 9:07

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

Comments

Vao Tsun · Accepted Answer · 2017-02-24 08:50:45Z

First that came (quite ugly):

t=# create table product (id text, range_price_list_values jsonb);
CREATE TABLE
t=# insert into product select 'cc80c862-c264-4bfe-a929-a52478c8d59e','[
t'#   {
t'#       "to": 10,
t'#       "from": 5,
t'#       "price": 1
t'#   },
t'#   {
t'#       "to": 20,
t'#       "from": 15,
t'#       "price": 1298000
t'#   },
t'#   {
t'#       "to": 30,
t'#       "from": 25,
t'#       "price": 500000
t'#   }
t'# ]';
INSERT 0 1

t=#  with b as (with a as (select id, jsonb_array_elements(range_price_list_values::jsonb) j from product) select id,jsonb_set(j,'{price}',((j->>'price')::int * 1000)::text::jsonb) from a) select distinct id, jsonb_pretty(concat('[',string_agg(jsonb_set::text,',') over (partition by id),']')::jsonb) from b;
                  id                  |        jsonb_pretty
--------------------------------------+-----------------------------
 cc80c862-c264-4bfe-a929-a52478c8d59e | [                          +
                                      |     {                      +
                                      |         "to": 10,          +
                                      |         "from": 5,         +
                                      |         "price": 1000      +
                                      |     },                     +
                                      |     {                      +
                                      |         "to": 20,          +
                                      |         "from": 15,        +
                                      |         "price": 1298000000+
                                      |     },                     +
                                      |     {                      +
                                      |         "to": 30,          +
                                      |         "from": 25,        +
                                      |         "price": 500000000 +
                                      |     }                      +
                                      | ]
(1 row)

having that in CTE, you can update values against it

Collectives™ on Stack Overflow

multiply a value of each item of a json array with postgres 9.6

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related