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.