特定元素的JSONB数组更新 [英] JSONB Array Update for Specific Element

问题描述

我有一个jsonb数组，其中包含大约1000个结构为 oid：aaa，instance：bbb，value：ccc的元素。

I have a jsonb array with around 1000 elements of the structure "oid: aaa, instance:bbb, value:ccc".

{"_id": 37637070
, "data": [{"oid": "11.5.15.1.4", "value": "1", "instance": "1.1.4"}
         , {"oid": "11.5.15.1.9", "value": "17", "instance": "1.1.4"}
         , {"oid": "12.5.15.1.5", "value": "0.0.0.0", "instance": "0"}]}

我在此字段上有一个索引

I have an index on this field

CREATE INDEX configuration_my_idx ON configuration
USING gin ((config->'data') jsonb_path_ops);

搜索速度非常快，但是我没有找到更新的快速方法。我想为特定的oid和实例组合更新一个值。

search is very fast, however I did not find a fast way to do update. I would like to update a value for a specific oid and instance combination.

例如，当oid为 11.5.15.1.9且实例为 1.1.4时，将值更新为 18

For example, update value to "18" when oid is "11.5.15.1.9" and instance is "1.1.4"

最快的方法是什么？

推荐答案

PostgreSQL 11 +

如果您已经在PostgreSQL v。11（由于新 JSONB 类型转换支持），最好的选择可能是用Perl或Python编写的自定义函数。

PostgreSQL 11+

If you're already at PostgreSQL v. 11 (because of the new JSONB type conversion support) your best bet would probably be a custom function written in Perl or Python.

我喜欢Python 3，下面是一个功能示例：

As I favor Python 3, here is a functional example:

CREATE OR REPLACE FUNCTION jsonb_replace_in_array (val jsonb, path_to_array text[], replacement jsonb, entry_filters jsonb)
    RETURNS jsonb
    TRANSFORM FOR TYPE jsonb
    LANGUAGE plpython3u
AS $$
v_new = val
tmp = v_new
for e in path_to_array:
    tmp = tmp[e]

for item in tmp:
    if (entry_filters is None or entry_filters.items() <= item.items()):
        item.update(replacement)

return v_new
$$;

...然后可以如下使用：

...which then can be used as follows:

UPDATE configuration
SET
  config = jsonb_replace_in_array(
    config,
    '{data}',
    '{"value":"changed"}'::jsonb,
    '{"oid":"11.5.15.1.4","instance":"1.1.4"}'::jsonb
  )
WHERE config->'data' @> '[{"oid":"11.5.15.1.4","instance":"1.1.4"}]';

是的，条件是重复的，但仅是为了限制第一个要触摸的行数

So yes, the condition is duplicated, but only to limit the amount of rows to touch in the first place.

要实际在纯PostgreSQL 11安装中工作，您需要扩展 plpython3u 和 jsonb_plpython3u ：

To actually work at a plain PostgreSQL 11 installation, you need extensions plpython3u and jsonb_plpython3u:

CREATE EXTENSION plpython3u;
CREATE EXTENSION jsonb_plpython3u;

Python逻辑解释：

The Python logic explained:

for e in path_to_array:
    tmp = tmp[e]

...使我们进入需要查看的条目数组。

...gets us down to the array of entries we need to look at.

for item in tmp:
    if (entry_filters is None or entry_filters.items() <= item.items()):
        item.update(replacement)

...对于数组中的每个项目，我们检查过滤条件是否为 null （ entry_filters为None =匹配任何条目），或者该条目是否包含所提供的示例，包括键和值（ entry_filters.items（）< = item.items（））。

...for each item in the array, we check whether the filter criteria is null (entry_filters is None = match any entry) or whether the entry "contains" the provided example including keys and values (entry_filters.items() <= item.items()).

如果en条目匹配，则使用提供的内容覆盖/添加内容

If en entry matches, then overwrite/add contents with the provided replacement.

我希望这会向您寻找的方向发展。

I hope that goes into the direction you where looking for.

看看当前的功能与JSON修改相关的PostgreSQL的细节将非常复杂（如果不复杂的话），并且引入大量开销以使用纯SQL进行相同的操作。

Looking at the current capabilities of PostgreSQL related to JSON modification it would be very complex (if not complicated) and introduce a lot of overhead to do the same with pure SQL.

如果您还没有可用的版本11，则以下功能将以相同的方式处理，但以牺牲类型转换本身，但要使其完全与API兼容，因此，升级后，您唯一要做的就是替换该函数（无需更改任何使用此函数的语句）：

In case you don't have version 11 available yet, the following function will do the same at the expense of handling the type conversions by itself but keep it completely API-compatible, so you once you upgrade, the only thing you have to do is replace the function (no change to any statements using this function required):

CREATE OR REPLACE FUNCTION jsonb_replace_in_array (val jsonb, path_to_array text[], replacement jsonb, entry_filters jsonb)
    RETURNS jsonb
    LANGUAGE plpython3u
AS $$
import json

v_new = json.loads(val)
t_replace = json.loads(replacement)
t_filters = json.loads(entry_filters)
tmp = v_new
for e in path_to_array:
    tmp = tmp[e]

for item in tmp:
    if (entry_filters is None or t_filters.items() <= item.items()):
        item.update(t_replace)

return json.dumps(v_new)
$$;

这篇关于特定元素的JSONB数组更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！