1. 更新jsonb中嵌套的字段
update t_config_asc_fw_acl set value=jsonb_set(value, '{args,pri}', to_jsonb(1), true);
2.更新jsonb中嵌套列表的字段
continents:[
{
id: 1,
name: 'North America',
countries: [
{
id: 1,
name: 'USA',
subdivision: [
{
id: 1,
name: 'Oregon',
type: 'SOME_TYPE'
}
]
}
]
}
]
update map
set divisions = jsonb_set( divisions, '{continents,0,countries,0,subdivisions,0,type}', '"STATE"', FALSE);
3.判断jsob中的数组是否被外部数组包含
SELECT '[1,2,3]' :: jsonb @> ( VALUE -> 'id_list' ) :: jsonb
SELECT '["apple", "orange", "banana"]'::jsonb @> '["apple", "banana"]'::jsonb // 返回 true
SQL