Postgresql update jsonb keys recursively-CodePudding

Having the following datamodel:

create table test
(
    id int primary key,
    js jsonb
);
insert into test values (1, '{"id": "total", "price": 400, "breakdown": [{"id": "product1", "price": 400}] }');
insert into test values (2, '{"id": "total", "price": 1000, "breakdown": [{"id": "product1", "price": 400}, {"id": "product2", "price": 600}]}');

I need to update all the price keys to a new name cost. It is easy to do that on the static field, using:

update test
set js = jsonb_set(js #- '{price}', '{cost}', js #> '{price}');

result:

1 {"id": "total", "cost": 1000, "breakdown": [{"id": "product1", "price": 400}]}
2 {"id": "total", "cost": 2000, "breakdown": [{"id": "product1", "price": 400}, {"id": "product2", "price": 600}]}

But I also need to do this inside the breakdown array.

How can I do this without knowing the number of items in the breakdown array? In other words, how can I apply a function in place on every element from a jsonb array.

Thank you!

CodePudding user response：

SOLUTION 1 : clean but heavy

First you create an aggregate function simlilar to jsonb_set :

CREATE OR REPLACE FUNCTION jsonb_set(x jsonb, y jsonb, _path text[], _key text, _val jsonb, create_missing boolean DEFAULT True)
RETURNS jsonb LANGUAGE sql IMMUTABLE AS
$$
    SELECT jsonb_set(COALESCE(x, y), COALESCE(_path, '{}' :: text[]) || _key, COALESCE(_val, 'null' :: jsonb), create_missing) ;
$$ ;

DROP AGGREGATE IF EXISTS jsonb_set_agg (jsonb, text[], text, jsonb, boolean) CASCADE ;
CREATE AGGREGATE jsonb_set_agg (jsonb, text[], text, jsonb, boolean)
(
  sfunc = jsonb_set
, stype = jsonb
) ;

Then, you call the aggregate function while iterating on the jsonb array elements :

WITH list AS (
SELECT id, jsonb_set_agg(js #- '{breakdown,' || ind || ',price}', '{breakdown,' || ind || ',cost}', js #> '{breakdown,' || ind || ',price}', true) AS js
FROM test
CROSS JOIN LATERAL generate_series(0, jsonb_array_length(js->'{breakdown}') - 1) AS ind
GROUP BY id)
UPDATE test AS t
SET js = jsonb_set(l.js #- '{price}', '{cost}', l.js #> '{price}')
FROM list AS l
WHERE t.id = l.id ;

SOLUTION 2 : quick and dirty

You simply convert jsonb to string and replace the substring 'price' by 'cost' :

UPDATE test
SET js = replace(js :: text, 'price', 'cost') :: jsonb

In the general case, this solution will replace the substring 'price' even in the jsonb string values and in the jsonb keys which include the substring 'price'. In order to reduce the risk, you can replace the substring '"price" :' by '"cost" :' but the risk still exists.

CodePudding user response：

This query is sample and easy for change field:

You can see my query structure in: dbfiddle

update test u_t
set js = tmp.new_js
from (
         select t.id,
                (t.js || jsonb_build_object('cost', t.js ->> 'price')) - 'price'
                    ||
                jsonb_build_object('breakdown', jsonb_agg(
                        (b.value || jsonb_build_object('cost', b.value ->> 'price')) - 'price')) as new_js
         from test t
                  cross join jsonb_array_elements(t.js -> 'breakdown') b
         group by t.id) tmp
where u_t.id = tmp.id;