postgresql - 在循环中通过键从 jsonb 数组中删除属性

Question

我尝试通过循环中的 jsonb 数组中的键删除属性

groups = '
[
    {"id": "1", "name": "Ann", "age": 20 },
    {"id": "2", "name": "Margaret", "age": 30 }
]';

FOR i IN 0..(jsonb_array_length(groups) - 1)
LOOP
groups = groups - '{age}';
END LOOP;

/* but nothing happened, I see [{"id": "1", "age": 20, "name": "Ann"}, {"id": "2", "age": 30, "name": "Margaret"}] */

RAISE NOTICE '%', groups;

但什么也没发生。我该怎么做？

Answer 1

在循环的每一步你只能修改数组的一个元素，所以你必须使用一个新的变量来聚合结果:

do $$
declare 
    groups jsonb = 
    '[
        {"id": "1", "name": "Ann", "age": 20 },
        {"id": "2", "name": "Margaret", "age": 30 }
    ]';
    new_groups jsonb = '[]';
begin
    for i in 0..jsonb_array_length(groups) - 1
    loop
        new_groups:= new_groups || (groups->i) - 'age';
    end loop;
    raise notice '%', new_groups;
end $$;

NOTICE:  [{"id": "1", "name": "Ann"}, {"id": "2", "name": "Margaret"}]
DO

更好的选择是在没有循环和附加变量的单个查询中执行相同的操作:

do $$
declare 
    groups jsonb = 
    '[
        {"id": "1", "name": "Ann", "age": 20 },
        {"id": "2", "name": "Margaret", "age": 30 }
    ]';
begin
    select jsonb_agg(value- 'age')
    from jsonb_array_elements(groups)
    into groups;
    raise notice '%', groups;
end $$;