PostgreSQL：从JSON列中删除属性

14 浏览
0 Comments

PostgreSQL：从JSON列中删除属性

我需要从一个json类型的列中删除一些属性。\n表格：\n

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

\n现在，我需要从列data中删除attrB。\n像alter table my_table drop column data->\'attrB\';这样的方法会很好。但是使用临时表的方法也足够。

0

3 答案

0 Comments

PostgreSQL中删除JSON列中的属性

在PostgreSQL中，有时我们需要从一个已存在的JSON列中删除一个键值对。下面是一个使用-操作符来解决这个问题的例子：

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

这是一个非常简单且有效的解决方法，不仅适用于json数据类型，也适用于text类型。

这个解决方法非常好，但不知道为什么没有人点赞。非常感谢你的帮助。

0
0 Comments

在PostgreSQL 9.5中，使用JSONB类型可以更轻松地实现删除JSON列的属性。您可以使用"-"运算符删除顶级属性。以下是一个示例：

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}

0
0 Comments

问题的出现的原因是：用户想要从PostgreSQL的JSON列中删除属性。在较新的版本中，可以使用-和#-操作符来删除JSON对象或数组中的键或索引。但对于较旧的版本（9.3或更低版本），没有直接的方法来删除属性，因此需要使用一些复杂的方法来实现。

解决方法是：用户可以使用json_each()函数将JSON对象拆分成键值对，并通过筛选掉不需要的字段来重建JSON对象。这个方法需要在一个子查询中使用VALUES来提供JSON对象，然后使用LATERAL子句和json_each()函数来拆分JSON对象，并筛选掉不需要的字段，最后使用array_agg()函数和字符串连接操作符来重新构建JSON对象。

对于较新的版本，用户也可以创建一个函数来删除JSON字段。这个函数使用json_each()函数将JSON对象拆分成键值对，并使用string_agg()函数和字符串连接操作符来重新构建JSON对象，同时排除掉要删除的键。然后用户可以使用这个函数来更新JSON字段，只需要传入要删除的键作为参数即可。

文章如下：

在PostgreSQL中删除JSON列中的属性

在PostgreSQL中，如果用户想要从JSON列中删除属性，可以使用不同的方法来实现，具体取决于PostgreSQL的版本。

对于较新的版本（9.5+），用户可以使用-操作符来删除JSON对象或数组中的键或索引。例如，可以使用以下语句来删除JSON对象中的键a：

SELECT jsonb '{"a":1,"b":2}' - 'a' -- 将返回jsonb '{"b":2}'

对于更深层次的JSON结构，用户可以使用#-操作符来删除嵌套在JSON中的键。例如，可以使用以下语句来删除JSON对象中的键a中的键b：

SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}' -- 将返回jsonb '{"a":[null,{"b":[]}]}'

但是，对于较旧的版本（9.4或更低版本），没有直接的方法来删除JSON列中的属性。在这种情况下，用户可以使用一种复杂的方法来实现。

用户可以使用json_each()函数将JSON对象拆分成键值对，并通过筛选掉不需要的字段来重建JSON对象。以下是一个示例查询，演示了如何删除JSON对象中的键attrB：

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

在这个查询中，首先使用VALUES子句提供了一个JSON对象，然后使用LATERAL子句和json_each()函数将JSON对象拆分成键值对。接下来，使用array_agg()函数和字符串连接操作符将键值对重新组合成一个JSON对象。最后，使用GROUP BY子句将结果按原始JSON对象进行分组。

对于较新的版本，用户也可以创建一个函数来删除JSON字段。以下是一个示例函数，演示了如何创建一个可以删除任意数量属性的函数：

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$;

使用这个函数，用户只需要在更新JSON字段时传入要删除的键作为参数即可。例如，可以使用以下语句来删除JSON字段中的键attrB：

UPDATE my_table
SET data = json_object_delete_keys(data, 'attrB');

通过使用这个函数，用户可以更方便地删除JSON字段中的属性。

在PostgreSQL中，用户可以根据不同的版本使用不同的方法来删除JSON列中的属性。对于较新的版本，可以使用-和#-操作符来删除JSON对象或数组中的键或索引。对于较旧的版本，可以使用json_each()函数和字符串连接操作符来重建JSON对象，并通过筛选掉不需要的字段来删除属性。此外，用户还可以创建一个函数来更方便地删除JSON字段中的属性。

0