SQL PostgreSQL：从 JSON 列中删除属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23490965/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: Remove attribute from JSON column
提问by sja
I need to remove some attributes from a json type column.
我需要从 json 类型列中删除一些属性。
The Table:
桌子：
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"]}'
);
Now, I need to remove attrBfrom column data.
现在，我需要attrB从 column 中删除data。
Something like alter table my_table drop column data->'attrB';would be nice. But a way with a temporary table would be enough, too.
像alter table my_table drop column data->'attrB';这样的东西会很好。但是使用临时表的方法也足够了。
回答by pozs
Update: for 9.5+, there are explicit operators you can use with jsonb(if you have a jsontyped column, you can use casts to apply a modification):
更新：对于 9.5+，您可以使用显式运算符jsonb（如果您有json类型列，则可以使用强制转换来应用修改）：
Deleting a key (or an index) from a JSON object (or, from an array) can be done with the -operator:
可以使用-运算符从 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]'
Deleting, from deep in a JSON hierarchy can be done with the #-operator:
可以使用#-运算符从 JSON 层次结构的深处删除：
SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}'
-- will yield jsonb '{"a":[null,{"b":[]}]}'
For 9.4, you can use a modified version of the original answer (below), but instead of aggregating a JSON string, you can aggregate into a jsonobject directly with json_object_agg().
对于 9.4，您可以使用原始答案的修改版本（如下），但不是聚合 JSON 字符串，您可以json直接使用json_object_agg().
Related: other JSON manipulations whithin PostgreSQL:
相关：PostgreSQL 中的其他 JSON 操作：
Original answer(applies to PostgreSQL 9.3):
原始答案（适用于 PostgreSQL 9.3）：
If you have at least PostgreSQL 9.3, you can split your object into pairs with json_each()and filter your unwanted fields, then build up the json again manually. Something like:
如果你至少有 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
With 9.2 (or lower) it is not possible.
使用 9.2（或更低版本）是不可能的。
Edit:
编辑：
A more convenient form is to create a function, which can remove any number of attributes in a jsonfield:
更方便的形式是创建一个函数，它可以删除json字段中任意数量的属性：
Edit 2: string_agg()is less expensive than array_to_string(array_agg())
编辑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$;
With this function, all you need to do is to run the query below:
使用此功能，您需要做的就是运行以下查询：
UPDATE my_table
SET data = json_object_delete_keys(data, 'attrB');
回答by mujimu
This has gotten much easier with PostgreSQL 9.5 using the JSONB type. See JSONB operators documented here.
使用 JSONB 类型的 PostgreSQL 9.5 使这变得更加容易。见JSONB运营商记录在这里。
You can remove a top-level attribute with the "-" operator.
您可以使用“-”运算符删除顶级属性。
SELECT '{"a": {"key":"value"}, "b": 2, "c": true}'::jsonb - 'a'
// -> {"b": 2, "c": true}
You can use this within an update call to update an existing JSONB field.
您可以在更新调用中使用它来更新现有的 JSONB 字段。
UPDATE my_table SET data = data - 'attrB'
You can also provide the attribute name dynamically via parameter if used in a function.
如果在函数中使用，您还可以通过参数动态提供属性名称。
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;
The reverse operator is the "||", in order to concatenate two JSONB packets together. Note that the right-most use of the attribute will overwrite any previous ones.
反向运算符是“||”，以便将两个 JSONB 数据包连接在一起。请注意，属性的最右侧使用将覆盖任何先前的使用。
SELECT '{"a": true, "c": true}'::jsonb || '{"a": false, "b": 2}'::jsonb
// -> {"a": false, "b": 2, "c": true}
回答by Westy92
I couldn't get SELECT '{"a": "b"}'::jsonb - 'a';to work in 9.5.2. However, SELECT '{"a": "b"}'::jsonb #- '{a}';did work!
我无法SELECT '{"a": "b"}'::jsonb - 'a';在 9.5.2 开始工作。但是，SELECT '{"a": "b"}'::jsonb #- '{a}';确实有效！
回答by Jeremy
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+:
虽然这在 9.5+ 中使用 jsonb 运算符肯定更容易，但 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
$$
;
If the keys to be removed are stored in a table, (e.g. in the column "keys" of the table "table_with_keys") you could call this function like this:
如果要删除的键存储在表中（例如，在表“table_with_keys”的“键”列中），您可以像这样调用此函数：
SELECT remove_multiple_keys(my_json_object,
VARIADIC (SELECT array_agg(keys) FROM table_with_keys));
回答by KARASZI István
It is an ugly hack but if attrBisn't your first key and it appears only once then you can do the following:
这是一个丑陋的黑客，但如果attrB不是您的第一个密钥并且它只出现一次，那么您可以执行以下操作：
UPDATE my_table SET data = REPLACE(data::text, ',"attrB":' || (data->'attrB')::text, '')::json;
回答by Sumit Chawla
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:
另一种方便的方法是使用 hstore 扩展。通过这种方式，您可以编写一些更方便的函数来将键设置/删除到 json 对象中。我想出了以下功能来做同样的事情：
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;
A simple example of use is:
一个简单的使用示例是：
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\"}]"}"
I have another function to do the set_key operation as well as following:
我还有另一个函数来执行 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 bloghere.
我在我的博客中对此进行了更多讨论。
回答by Kremnev Sergey
I was struggling to find a simple update query that removed json keys in Postgres 9.4 without creating a function, so here's an update to @posz answer.
我正在努力寻找一个简单的更新查询，该查询在不创建函数的情况下删除了 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
Query assumes you have a table like this:
查询假设您有一个这样的表：
CREATE TABLE myTable (
id SERIAL PRIMARY KEY,
someField text
);
I guess you can use this line from @posz answer instead of json_object_agg line, to make it work on older postgres, but I didn't test it.
我想您可以使用@posz 答案中的这一行而不是 json_object_agg 行，使其在较旧的 postgres 上工作，但我没有对其进行测试。
('{' || array_to_string(array_agg(to_json(l.key) || ':' || l.value), ',') || '}')::json AS after
Also make sure to run select from subrequest to make sure you're updating correct data
还要确保从子请求中运行 select 以确保更新正确的数据
