在PostgreSQL中，从多个嵌套的未知键值的jsonb对象中删除键。

Question

我有一张表，其中一列包含类似的jsonb对象，但每行的顶层键不同。每个记录看起来像这样，但顶层键不同：

{
   "10": {
      "key": "value",
      "toDelete": "value"
   },
   "42": {
      "key": "value",
      "toDelete": "value"
   },...
}

我需要进行一次更新，以从每一行的每个记录中的每个对象中删除toDelete键/值。手动使用#-运算符逐个删除每个键是很容易的，但每个记录中可能有数百甚至数千个顶级键，因此我需要某种动态解决方案。我尝试将所有要删除的路径聚合到一个数组中，并使用column #- array一次性删除所有路径，其中数组看起来像{{10, toDelete},{42,toDelete}...}，但这并没有奏效。

- Colin

---

如果你在jsonb中存储了成千上万个键，我认为你正在滥用postgresql。也许你应该考虑从json中提取数据，并将其存储在真正的SQL表中。 - mvp

Answer 1

JSON列的结构是一种反模式，我完全同意@mvp的评论：

......你应该考虑从json中提取数据并将其存储在真正的SQL表中。

如果你被迫处理原始数据，请使用以下函数：

最初的回答

create or replace function remove_nested_object(obj jsonb, key_to_remove text)
returns jsonb language sql immutable as $$
    select jsonb_object_agg(key, value- key_to_remove)
    from jsonb_each(obj)
$$;

update my_table
set json_column = remove_nested_object(json_column, 'toDelete')
where json_column::text like '%"toDelete":%';

- klin

---

我使用 plpgsql 存储过程得到了类似的答案，用于构建删除所有查询字符串的操作。但是速度非常慢。这种方法效果要好得多。谢谢！ - Colin

Answer 2

使用 @klin 的函数时，我遇到了这个错误

错误：无法从标量中删除 上下文：SQL 函数“remove_nested_object”语句 1 SQL 状态：22023

因为我试图递归地使其工作。 最终我得到了这个函数，它可以删除 JSON 层次结构的任何级别上不需要的键。出于经济和方便的考虑，它还会删除多个键。

CREATE OR REPLACE FUNCTION public.remove_nested_object(
    obj jsonb,
    keys_to_remove text[])
    RETURNS jsonb
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL SAFE 
AS $BODY$
declare
    returned_object jsonb;
    key_to_remove text;
begin
    returned_object := obj;

    case
        when jsonb_typeof(obj) = 'object' then
            foreach key_to_remove in array keys_to_remove
            loop
                returned_object := returned_object - key_to_remove;
            end loop;
            return jsonb_object_agg(key, remove_nested_object(value, keys_to_remove)) from jsonb_each(returned_object);
        when jsonb_typeof(obj) = 'array' then 
            return (select jsonb_agg(remove_nested_object(item, keys_to_remove)) from jsonb_array_elements(obj) item);
        else return obj;
    end case;
end;
$BODY$;

使用示例：

select remove_nested_object(yourJsonB, '{field1,field2,field3}') 

- alexbogush