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

现在，我需要从列data中删除attrB。

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

- sja

---

你的PostgreSQL版本是什么？ - pozs

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_object_agg()聚合为一个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

---

@voxobscuro 是的，完全正确。然而在新的“版本”中，我习惯写concat('{', ..., '}')，因为concat会忽略空值。 - pozs

@Za7pi，答案中的函数是VARIADIC -- 你应该这样调用它：json_object_delete_keys(data, 'attrB', 'attrC') 或者 json_object_delete_keys(data, VARIADIC '{"attrB", "attrC"}'::text[])。 - pozs

我们可以使用json_agg("key","value")来构建JSON，而不是手动使用string_agg/array_agg构建。 - Hasselbach

json_object_agg - Hasselbach

@AlexanderHasselbach 这个答案是很久以前为9.3版本创建的，当时还没有 json_object_agg()。现在已经有了一个明确的运算符来解决 OP 的问题（因此也不需要使用那个函数），我很快会更新我的答案。 - pozs

Answer 2

使用JSONB类型，PostgreSQL 9.5使这变得更加容易。请参见此处记录的JSONB操作符。

您可以使用“-”运算符删除顶级属性。

SELECT '{"a": {"key":"value"}, "b": 2, "c": true}'::jsonb - 'a'
// -> {"b": 2, "c": true}

您可以在更新调用中使用此方法来更新现有的JSONB字段。

UPDATE my_table SET data = data - 'attrB'

如果在函数中使用，您还可以通过参数动态提供属性名称。

CREATE OR REPLACE FUNCTION delete_mytable_data_key(
    _id integer,
    _key character varying)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE my_table SET
        data = data - _key
    WHERE id = _id;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

反向操作符是“||”，用于将两个JSONB数据包连接在一起。请注意，最右边使用的属性会覆盖之前的任何属性。

SELECT '{"a": true, "c": true}'::jsonb || '{"a": false, "b": 2}'::jsonb 
// -> {"a": false, "b": 2, "c": true}

- mujimu

Answer 3

我曾遇到类似问题，需要从Postgres中的现有JSON列中删除键值对。以下是使用-运算符解决该问题的方法：

我曾遇到类似问题，需要从Postgres中的现有JSON列中删除键值对。以下是使用-运算符解决该问题的方法：

UPDATE my_table
SET data = data::jsonb - 'attrB'
WHERE id = 'A';

- Rahul Kumar

---

我不知道为什么没有人给你点赞。你的解决方案非常简单且有效，太棒了。非常感谢你。 - Mey Lin

这是最佳解决方案：不仅适用于 json 数据类型，还适用于 text。 - Marco Lackovic

Answer 4

如果您想要移除一个子字段，例如：

{
  "a": {
    "b": "REMOVE ME!"
  }
}

您可以简单地使用：

UPDATE my_table
SET my_json_column = my_json_column::jsonb #- '{a,b}';

- Pleymor

Answer 5

在9.5.2版本中，我无法让SELECT '{"a": "b"}'::jsonb - 'a';正常工作。但是SELECT '{"a": "b"}'::jsonb #- '{a}';可行！

- Westy92

Answer 6

只需使用 #- 运算符，例如：

SELECT '{"foo": 10, "bar": [], "baz": {}}'::jsonb #- '{baz}';

- Ariel Monaco

Answer 7

尽管使用jsonb运算符在9.5+版本中更加容易，但pozs编写的删除多个键的函数仍然很有用。例如，如果要删除的键存储在表中，则可以使用该函数将它们全部删除。以下是更新后的函数，使用了jsonb和postgresql 9.5+：

CREATE FUNCTION remove_multiple_keys(IN object jsonb, 
                                     variadic keys_to_delete text[], 
                                     OUT jsonb)
  IMMUTABLE
  STRICT
  LANGUAGE SQL
AS 
$$
  SELECT jsonb_object_agg(key, value)
     FROM (SELECT key, value 
           FROM jsonb_each("object")
           WHERE NOT (key = ANY("keys_to_delete")) 
     ) each_subselect
$$
;

如果要删除的键存储在一个表中（例如在名为“table_with_keys”的表的“keys”列中），您可以像这样调用此函数：

SELECT remove_multiple_keys(my_json_object, 
                            VARIADIC (SELECT array_agg(keys) FROM table_with_keys));

- Jeremy

Answer 8

这是一种不太优雅的解决方案，但如果 attrB 不是你的第一个键，并且只出现一次，则可以执行以下操作：

UPDATE my_table SET data = REPLACE(data::text, ',"attrB":' || (data->'attrB')::text, '')::json;

- KARASZI István

---

还不错。你可以通过使用 regexp_replace 来改进它 - 让逗号变成可选的，这样它就可以在任何情况下工作了。 - Lukasz Wiktor

这样做的问题在于，如果JSON表达式评估为NULL（例如，键不存在），则整个字符串连接由于||运算符而评估为NULL，然后将整个JSON列设置为NULL。因此，如果您在具有不一致数据的JSON列上运行此操作或意外输入不存在的键，则会删除所有数据。更好的解决方案是使用concat()函数，它会忽略NULL值，因此不会破坏您的数据。UPDATE my_table SET data = REPLACE(data::text, concat(',"attrB":' , (data -> 'attrB')::text), '')::json; - Skylar Brown

我并不是说它完美无缺，但这只是一个快速而粗糙的解决方案。 - KARASZI István

Answer 9

在我的情况下

{"size": {"attribute_id": 60, "attribute_name": "Size", "attribute_nameae": "Size" "selected_option": {"option_id": 632, "option_name": "S"}}, "main_color": {"attribute_id": 61, "attribute_name": "Main Color", "selected_option": {"option_id": 643, "option_name": "Red"}}}

移除 size->attribute_nameae

UPDATE table_name set jsonb_column_name = jsonb_set(jsonb_column_name, '{size}', (jsonb_column_name->'size') -  'attribute_namea') WHERE <condition>

- Châu Văn Khánh Nhật

Answer 10

对于 PostgreSQL 版本 > 9.6，您可以直接运行以下命令：

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

- Thiago Cavalcanti