我成功了！这是我找到的解决方案：
表格：
table Bar(
id int,
my_json jsonb
)
my_json的外观示例：
{
"foo": "bar",
"columns": [
{
"type": "integer",
"field": "myField",
"children": [
{
"type": "integer",
"field": "myField"
}
]
},
{
"type": "integer",
"field": "otherField",
"children": [
{
"type": "integer",
"field": "myField"
}
]
},
{
"type": "integer",
"field": "baz",
"children": null
}
]
}
我需要将所有的"type": "integer"更改为"type": "string"，其中匹配的"field"等于"myField"。
我使用下一个sql查询创建了lvl 1(在columnslvl(
with query as (
select ('{columns,' || index - 1 || ',type}')::text[] as path,
b.id
from Bar b,
jsonb_array_elements(my_json -> 'columns') with ordinality arr(col, index)
where col ->> 'field' = 'myField'
and col ->> 'type' = 'integer'
)
update Bar b
set my_json = jsonb_set(b.my_json, query.path, '"string"', false)
from query
where b.id = query.id;
我必须用嵌套数组children实现lvl2(理想情况下理解任何lvl(，它可能为null，但我不知道如何做到
with non_null_children as (
select index - 1 as idx,
col,
b.id as bar_id
from Bar b,
jsonb_array_elements(my_json -> 'columns') with ordinality arr(col, index)
where col ->> 'children' is not null
), query as (
select ('{columns,' || nnc.idx || ',children,' || index - 1 || ',type}')::text[] as path,
b.id
from Bar b,
non_null_children nnc,
jsonb_array_elements(nnc.col -> 'children') with ordinality arr(col2, index)
where b.id = nnc.bar_id
and col2 ->> 'field' = 'myField'
and col2 ->> 'type' = 'integer'
)
update Bar b
set my_json = jsonb_set(b.my_json, query.path, '"string"', false)
from query
where b.id = query.id;
说明：
- 我列出了所有包含子项的项，每个项都与其
columns索引及其Bar-id->non_null_children - 对于每个Bar-id/column索引，我可以进行匹配字段操作，就像lvl 1一样
我不知道这是否是最优化的解决方案，如果您认为有必要，请随时改进。