Add new Property in Jsonb column by calculating some of its properties Using Postgressql

Question

I have a jsonb column such that:

| id       | fee                                    |
|----------|----------------------------------------|
| 1        |  "[{"Step": "step1", "Value": "10"}]"  |
| 2        |  "[{"Step": "step1", "Value": "999"}]" |
| 3        |  []                                    |

And I want to calculate the Value filed and add new property in the same fee column such that:

ROUND((Value / 1.07),2)

I tried this below code but its not working.

update plans 
set excess_fees = jsonb_set(plans.excess_fees, '{0, ValueExclGst}', 
                            (select cast(round(cast(VALUE as decimal), 2) as text)  
                             from jsonb_array_elements(plans.excess_fees->'Value') as VALUE));

The output looks like this:

| id | fee |
|----|-----|
| 1  | "[{"Step": "step1", "Value": "10", "ValueExclGst":"9.35"}]" |
| 2  | "[{"Step": "step1", "Value": "999", "ValueExclGst": "933.64"}]" |
| 3  | [] |

How shall I do in Postgres since I am new to Jsonb?

Thank you for your time and answers.

Answer 1

jsonb_array_elements and jsonb_set would come handy for this. A working query:

WITH fee_json AS
(
  SELECT id, jsonb_array_elements(fee) as json_data FROM plans
),
updated_rows AS (
  SELECT id,
    jsonb_agg(
      jsonb_set(json_data, '{ValueExclGst}', CAST(round(CAST(json_data->>'Value' AS NUMERIC)/1.07, 2) as text)::jsonb)
    ) as updated_json
  FROM
    fee_json
  GROUP BY
    id
)
UPDATE plans SET fee = u.updated_json
FROM updated_rows u
WHERE plans.id = u.id;

The query works in these phases:

1. Create a fee_json auxiliary statement using CTE by deflating the fee array column. Now fee_json has a column json_data with all the deflated jsonb objects along with the corresponding id.

2. Create another updated_rows auxiliary statement using the values from fee_json. In this statement, we're inserting a new value ValueExclGst in the jsonb using the value from Value field in jsonb. We array aggregate all such jsonb objects by id using jsonb_agg.

3. We update the value from udpated_rows using the id field in our plans table.

A working db-fiddle for better understanding.

Answer 2

There is another approach I found out to solve the given problem. Yet it only work for first index which means it will work for all the id{1,2,3} but for id{1}, it will just update the first index.

id	fee
1	"[{"Step": "step1", "Value": "10"}, {"Step": "step1", "Value": "10"}]"
2	"[{"Step": "step1", "Value": "999"}]"
3	[]

UPDATE fee pl SET excess_fees = jsonb_set(pl.excess_fees, '{0, ValueExclGst}', to_jsonb(gstExclVal.exclValue::text))
FROM (
       SELECT ps.id, ROUND((Cast( arr.item_object->>'Value' as numeric) / 1.07),2) as exclValue
       FROM fee ps, jsonb_array_elements(ps.excess_fees) with ordinality arr(item_object, position)
     ) AS gstExclVal
WHERE pl.id = gstExclVal.id;

To rollback programmatically, we can use below code snippets :

UPDATE fee pl SET excess_fees = excess_fee.orginal_excess_fee
FROM (
    SELECT fee.p_id, jsonb_agg(fee.item) as orginal_excess_fee from (
    SELECT fee.id as p_id, (arr.item_object - 'ValueExclGst') as item , arr.position as pos
    FROM fee, jsonb_array_elements(excess_fees) with ordinality arr(item_object, position)
    ) as fee group by fee.p_id
 ) AS excess_fee where pl.id= excess_fee.p_id;