Postgresql递归更新jsonb键

Question

具有以下数据模型：

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}]}');

我需要将所有价格键更新为新名称cost。在静态字段上很容易做到这一点，使用:

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

结果：

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

但是我也需要在分解数组中做这些。

在不知道细分数组中的项目数的情况下，我如何做到这一点？换句话说，我如何在jsonb数组的每个元素上应用函数。

非常感谢。

Answer 1

将所有jsonb对象中的jsonb键替换为jsonb数组的另一种方法是:

我的查询分解了jsonb数组。对于每个对象，如果price键存在，则从jsonb对象中删除price键，使用旧的price值添加新的键，然后使用修改后的对象创建一个新的jsonb数组。最后用新的替换旧的jsonb数组。

WITH cte AS (SELECT id, jsonb_agg(CASE WHEN item ? 'price' 
                                       THEN jsonb_set(item - 'price', '{"cost"}', item -> 'price')
                                       ELSE item END) AS cost_array
              FROM test
              CROSS JOIN jsonb_array_elements(js -> 'breakdown') WITH ORDINALITY arr(item, index)
              GROUP BY id)
              
UPDATE test
SET js = jsonb_set(js, '{breakdown}', cte.cost_array, false)
FROM cte
WHERE cte.id = test.id;

Answer 2

此查询是示例，易于更改字段：

您可以在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;

Answer 3

解决方案1：干净但沉重

首先创建一个类似于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
) ;

然后，在迭代jsonb数组元素时调用聚合函数：

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 ;

解决方案2：又快又脏

您只需将jsonb转换为string，并将子字符串“price”替换为“cost”:

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

在一般情况下，此解决方案将替换子字符串“价格”，即使在 jsonb 字符串值和包含子字符串“价格”的 jsonb 键中也是如此。为了降低风险，您可以将子字符串“price” ：“替换为”成本“ ：”，但风险仍然存在。