PostgreSQL：从 JSON 列中删除属性

Question

我需要从 json 类型列中删除一些属性。

桌子：

 CREATE TABLE my_table( id VARCHAR(80), data json);
INSERT INTO my_table (id, data) VALUES (
  'A',
  '{"attrA":1,"attrB":true,"attrC":["a", "b", "c"]}'
);

现在，我需要从 attrB data 。

像 alter table my_table drop column data->'attrB'; 这样的东西会很好。但是使用临时表的方法也足够了。

原文由 sja 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

更新：对于 9.5+，您可以将显式运算符与 jsonb 一起使用（如果您有 json 类型的列，您可以使用强制转换来应用修改）：

可以使用 - 运算符从 JSON 对象（或从数组）中删除键（或索引）：

 SELECT jsonb '{"a":1,"b":2}' - 'a', -- will yield jsonb '{"b":2}'
       jsonb '["a",1,"b",2]' - 1    -- will yield jsonb '["a","b",2]'

可以使用 #- 运算符从 JSON 层次结构的深处删除：

 SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}'
-- will yield jsonb '{"a":[null,{"b":[]}]}'

对于 9.4，您可以使用原始答案的修改版本（如下），但您可以直接使用 json_object_agg() 聚合到 json 对象中，而不是聚合 JSON 字符串。

相关：PostgreSQL 中的其他 JSON 操作：

• 如何修改新 PostgreSQL JSON 数据类型中的字段？

原始答案（适用于 PostgreSQL 9.3）：

如果您至少有 PostgreSQL 9.3，您可以使用 json_each() 将对象分成对并过滤不需要的字段，然后再次手动构建 json。就像是：

 SELECT data::text::json AS before,
       ('{' || array_to_string(array_agg(to_json(l.key) || ':' || l.value), ',') || '}')::json AS after
FROM (VALUES ('{"attrA":1,"attrB":true,"attrC":["a","b","c"]}'::json)) AS v(data),
LATERAL (SELECT * FROM json_each(data) WHERE "key" <> 'attrB') AS l
GROUP BY data::text

对于 9.2（或更低），这是不可能的。

编辑：

更方便的形式是创建一个函数，它可以删除 json 字段中的任意数量的属性：

编辑2 ： string_agg() 比 array_to_string(array_agg()) 便宜

CREATE OR REPLACE FUNCTION "json_object_delete_keys"("json" json, VARIADIC "keys_to_delete" TEXT[])
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT COALESCE(
  (SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}')
   FROM json_each("json")
   WHERE "key" <> ALL ("keys_to_delete")),
  '{}'
)::json
$function$;

使用此功能，您需要做的就是运行以下查询：

 UPDATE my_table
SET data = json_object_delete_keys(data, 'attrB');

原文由 pozs 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

对于 PostgreSQL 版本 > 9.6，您可以简单地运行：

 UPDATE my_table
set data = data::jsonb - 'attrB'

原文由 Thiago Cavalcanti 发布，翻译遵循 CC BY-SA 4.0 许可协议