如何根据 Postgres 中的过滤条件更新深度嵌套的 JSON 对象？

我有一张桌子mapping_transform带有 JSONB 列content_json包含类似的东西

{
    "meta": {...},
    "mapping": [
        ...,
        {
            "src": "up",
            "dest": "down",
            ...
        },
        ...
    ]
}

我想添加一个新的 JSON 条目（"rule_names": [ "some name" ]) 到 JSON 对象匹配src = up and dest = down，这会导致

{
    "meta": {...},
    "mapping": [
        ...,
        {
            "src": "up",
            "dest": "down",
            ...,
            "rule_names": [ "some name" ]
        },
        ...
    ]
}

以下查询返回JSON 对象满足过滤要求：

WITH elems AS (SELECT json_array_elements(content_json->'mapping') from mapping_transform)
SELECT * FROM elems WHERE json_array_elements->>'src' = 'up' and json_array_elements->>'dest' = 'down';

-- Alternative

SELECT mt_entry
FROM mapping_transform,
LATERAL jsonb_array_elements(content_json::jsonb->'mapping') mt_entry
WHERE mt_entry->>'src' = 'up' and mt_entry->>'dest' = 'down';

我现在的问题是我不知道如何将新条目添加到特定对象。我尝试过类似的东西

WITH elems AS (SELECT json_array_elements(content_json->'mapping') from mapping_transform),
     results SELECT * FROM elems WHERE json_array_elements->>'src' = 'up' and json_array_elements->>'dest' = 'down'
UPDATE mapping_transform
SET content_json = jsonb_set(results, '{"rule_names"}', '["some name"]'); -- this does obviously not work

但这并不执行为results是未知列。我还需要合并结果jsonb_set与其余的content_json在分配给之前content_json，因为否则它将覆盖整个内容。

如何根据过滤条件更新特定的深层嵌套 JSON 对象？ 如果我有一个明确定义的路径来确定我想要更新的对象的位置，那么事情就会容易得多。但由于目标对象位于 JSON 数组中并且具有任意位置，查找和更新它要困难得多。

如果您熟悉 JavaScript，您会很乐意安装和使用JavaScript 过程语言 plv8。 https://github.com/plv8/plv8此扩展允许您本地修改 json 值，例如：

create extension if not exists plv8;

create or replace function update_mapping_v8(data json)
returns json language plv8 as $$
    var len = data['mapping'].length;
    for (var i = 0; i < len; i++) {
        var o = data['mapping'][i];
        if (o.src == 'up' && o.dest == 'down') {
            o.rule_names = 'some name'
        }
    }
    return data;
$$;

update mapping_transform
set content_json = update_mapping_v8(content_json);

对于 MS Windows 用户：准备安装 Windows 二进制文件。 http://www.postgresonline.com/journal/archives/367-PLV8-binaries-for-PostgreSQL-9.6beta1-windows-both-32-bit-and-64-bit.html

plpgsql 替代解决方案使用 jsonb 类型：

create or replace function update_mapping_plpgsql(data jsonb)
returns json language plpgsql as $$
declare
    r record;
begin
    for r in
        select value, ordinality- 1 as pos
        from jsonb_array_elements(data->'mapping') with ordinality
        where value->>'src' = 'up' and value->>'dest' = 'down'
    loop
        data = jsonb_set(
            data,
            array['mapping', r.pos::text],
            r.value || '{"rule_names": "some name"}'
            );
    end loop;
    return data;
end $$;

update mapping_transform
set content_json = update_mapping_plpgsql(content_json::jsonb);