如何基于Postgres中的过滤条件更新深层嵌套的JSON对象? [英] How to update deeply nested JSON object based on filter criteria in Postgres?
问题描述
我有一个表mapping_transform
,其中的JSONB列content_json
包含类似内容
I have a table mapping_transform
with a JSONB column content_json
containing something like
{
"meta": {...},
"mapping": [
...,
{
"src": "up",
"dest": "down",
...
},
...
]
}
我想向与src
= up
和dest
= down
匹配的JSON对象添加一个新的JSON条目("rule_names": [ "some name" ]
),这将导致
I want to add a new JSON entry ("rule_names": [ "some name" ]
) to the JSON object matching src
= up
and dest
= down
, which would result in
{
"meta": {...},
"mapping": [
...,
{
"src": "up",
"dest": "down",
...,
"rule_names": [ "some name" ]
},
...
]
}
以下查询返回满足过滤条件的 JSON对象:
The following query returns the JSON object that meets the filter requirements:
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';
我现在的问题是我不知道如何将新条目添加到特定对象.我尝试过类似的
My problem now is that I do not know how to add the new entry to the specific object. I tried something like
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
执行,是未知列.在分配给content_json
之前,我还确实需要将jsonb_set
的结果与content_json
的其余部分合并,因为否则它将覆盖整个内容.
but that does not execute as results
is an unknown column. I also do need to merge the result of the jsonb_set
with the rest of the content_json
before assigning to content_json
, because otherwise it would override the whole content.
如何根据过滤条件更新特定的深层嵌套JSON对象? 如果我有一个明确的路径来确定要更新的对象,那么事情会容易得多.但是由于目标对象位于JSON数组中并且具有任意位置,因此查找和更新目标要困难得多.
How can I update specific deeply nested JSON objects based on filter criteria? If I had a well defined path as to where my object is that I want to update, things would be much easier. But as the target object lies within a JSON array and has an arbitrary position, finding and updating it is much more difficult.
推荐答案
如果您熟悉JavaScript,将很乐意安装和使用 JavaScript过程语言plv8.此扩展允许您本地修改json值,例如:
If you are familiar with JavaScript you'll be happy to install and use JavaScript procedural language plv8. This extension allows you to modify json values natively, example:
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);
For MS Windows users: ready to install Windows binaries.
plpgsql替代解决方案使用jsonb类型:
A plpgsql alternative solution uses jsonb type:
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);
这篇关于如何基于Postgres中的过滤条件更新深层嵌套的JSON对象?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!