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从column中删除data。

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

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直接将其聚合为一个对象，而不是聚合JSON字符串json_object_agg()。

相关：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');