PostgreSQL:从JSON列中删除属性 [英] PostgreSQL: Remove attribute from JSON column

问题描述

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

I need to remove some attributes from a json type column.

表格:

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.

Now, I need to remove attrB from column data.

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

Something like alter table my_table drop column data->'attrB'; would be nice. But a way with a temporary table would be enough, too.

推荐答案

更新:对于9.5+，有明确的运算符可与jsonb一起使用(如果键入了json列，您可以使用强制转换来应用修改):

Update: for 9.5+, there are explicit operators you can use with jsonb (if you have a json typed column, you can use casts to apply a modification):

可以使用-运算符从JSON对象(或数组)中删除键(或索引):

Deleting a key (or an index) from a JSON object (or, from an array) can be done with the - operator:

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层次结构的深层进行删除:

Deleting, from deep in a JSON hierarchy can be done with the #- operator:

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

对于9.4，您可以使用原始答案的修改版本(如下所示)，但是可以使用json_object_agg()直接将其汇总为json对象，而不是汇总JSON字符串.

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 json object directly with json_object_agg().

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

Related: other JSON manipulations whithin PostgreSQL:

• 如何修改新的PostgreSQL JSON数据类型内的字段?

原始答案(适用于PostgreSQL 9.3):

Original answer (applies to PostgreSQL 9.3):

如果您至少具有PostgreSQL 9.3，则可以使用json_each()将对象拆分成对，并过滤不想要的字段，然后再次手动构建json.像这样:

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:

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(或更低版本)是不可能的.

With 9.2 (or lower) it is not possible.

修改:

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

A more convenient form is to create a function, which can remove any number of attributes in a json field:

编辑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');

这篇关于PostgreSQL:从JSON列中删除属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！