PostgreSQL: 从 JSON 列中删除属性

我需要从 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';这样的东西是不错的，但是有一个临时表也足够了。

98500 次浏览
小开
最佳答案

更新 : 对于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 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(或更低)是不可能的。

Edit:

更方便的形式是创建一个函数，它可以删除 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');
小开

One other convenient way of doing this is to use hstore extension. This way you can write some more convenient function to set/delete keys into a json object. I came up with following function to do the same:

CREATE OR REPLACE FUNCTION remove_key(json_in json, key_name text)
RETURNS json AS $$
DECLARE item json;
DECLARE fields hstore;
BEGIN
-- Initialize the hstore with desired key being set to NULL
fields := hstore(key_name,NULL);


-- Parse through Input Json and push each key into hstore
FOR item IN  SELECT row_to_json(r.*) FROM json_each_text(json_in) AS r
LOOP
--RAISE NOTICE 'Parsing Item % %', item->>'key', item->>'value';
fields := (fields::hstore || hstore(item->>'key', item->>'value'));
END LOOP;
--RAISE NOTICE 'Result %', hstore_to_json(fields);
-- Remove the desired key from store
fields := fields-key_name;


RETURN hstore_to_json(fields);
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
STRICT;

一个简单的使用例子是:

SELECT remove_key(('{"Name":"My Name", "Items" :[{ "Id" : 1, "Name" : "Name 1"}, { "Id" : 2, "Name 2" : "Item2 Name"}]}')::json, 'Name');
-- Result
"{"Items": "[{ \"Id\" : 1, \"Name\" : \"Name 1\"}, { \"Id\" : 2, \"Name 2\" : \"Item2 Name\"}]"}"

我还有另一个函数来执行 set _ key 操作以及以下操作:

CREATE OR REPLACE FUNCTION set_key(json_in json, key_name text, key_value text)
RETURNS json AS $$
DECLARE item json;
DECLARE fields hstore;
BEGIN
-- Initialize the hstore with desired key value
fields := hstore(key_name,key_value);


-- Parse through Input Json and push each key into hstore
FOR item IN  SELECT row_to_json(r.*) FROM json_each_text(json_in) AS r
LOOP
--RAISE NOTICE 'Parsing Item % %', item->>'key', item->>'value';
fields := (fields::hstore || hstore(item->>'key', item->>'value'));
END LOOP;
--RAISE NOTICE 'Result %', hstore_to_json(fields);
RETURN hstore_to_json(fields);
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
STRICT;

I have discussed this more in my 博客 here.

小开

这是一个丑陋的黑客，但如果 attrB不是你的第一个关键，它只出现一次，然后你可以做以下:

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

This has gotten much easier with PostgreSQL 9.5 using the JSONB type. See JSONB operators documented 给你.

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

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

您可以在 update 调用中使用它来更新现有的 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}
小开

我不能让 SELECT '{"a": "b"}'::jsonb - 'a';在9.5.2中工作。但是，SELECT '{"a": "b"}'::jsonb #- '{a}';确实工作了！

小开

While this is certainly easier in 9.5+ using the jsonb operators, the function that pozs wrote to remove multiple keys is still useful. For example, if the keys to be removed are stored in a table, you could use the function to remove them all. Here is an updated function, using jsonb and 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”的“ key”列中) ，你可以这样调用这个函数:

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

我一直在努力寻找一个简单的更新查询，它删除了 Postgres 9.4中的 json 键，而没有创建一个函数，所以这里有一个@posz 的更新答案。

UPDATE someTable t
SET someField = newValue
FROM (
SELECT id,
json_object_agg(l.key, l.value)::text AS newValue
FROM someTable t,
LATERAL (
SELECT *
FROM json_each(t.someField::json)
WHERE "key" <> ALL (ARRAY['key1', 'key2', 'key3'])
) AS l
GROUP BY id
) upd
WHERE t.id = upd.id

查询假设您拥有如下表:

CREATE TABLE myTable (
id SERIAL PRIMARY KEY,
someField text
);

我想你可以使用@posz answer 中的这一行代替 json _ object _ agg 行，使它能够在旧的 postgres 中工作，但是我没有测试它。

('{' || array_to_string(array_agg(to_json(l.key) || ':' || l.value), ',') || '}')::json AS after

还要确保从子请求中运行 select，以确保更新的数据是正确的

小开

简单地使用 #-运算符，例如:

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

如果不仅要从单个文档中删除 JSON 属性，还要从数组中删除 JSON 属性，可以考虑使用以下语句:

    UPDATE table_to_update t
SET jsonColumnWithArray = (SELECT JSON_AGG(jsonItem)
FROM (SELECT JSON_ARRAY_ELEMENTS(t1.jsonColumnWithArray)::JSONB #- '{customerChange, oldPeriod}' AS jsonItem
FROM table_to_update t1
WHERE t1.id = t1.id) AS t2);

Input

[
{
"customerChange": {
"newPeriod": null,
"oldPeriod": null
}
},
{
"customerChange": {
"newPeriod": null,
"oldPeriod": null
}
}
]

输出

[
{
"customerChange": {
"newPeriod": null
}
},
{
"customerChange": {
"newPeriod": null
}
}
]
小开

在 postgres 中从现有的 json 列中删除键-值对也是我面临的类似问题。我能够修复这使用 -操作符如下:

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

如果要删除子字段，如:

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

You can simply use:

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

In my case

{"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 _ namae

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

对于 PostgreSQL version > 9.6，只需运行以下命令:

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

提交答案