更新包含多个对象的jsonb列中的特定记录

Question

考虑名为"DocumentInformation"；类型jsonb，具有此特定记录：

[
{
"SchoolsCode": 22,
"SchoolsName": "Home School",
},
{
"SchoolsCode": "101770",
"SchoolsName": "Blossom Senior High School",
}
]

这是我尝试使用的postgresql查询，用于根据SchoolName更新schoolCode的值

Update SchoolRecords set DocumentInformation = jsonb_set(documentInformation, '{schoolCode}', '"00001"') where documentInformation ->> 'SchoolName' = 'Home School'

但得到的失败响应为：

UPDATE 0
Query returned successfully in 401 msec.

Answer 1

您必须找到要修改的数组的索引，然后使用jsonb_set进行修改，如下所示：

with my_json as (
select ('{'||index-1||',SchoolsCode}')::text[] as path
from school_records,
jsonb_array_elements(document_information) with ordinality arr(di,index)
where di->> 'SchoolsName'='Home School'
)
update school_records set document_information = jsonb_set(document_information,my_json.path,'"000001"')
from my_json;

此处显示结果