PostgreSQL中实现未知是否存在的jsonb数组追加值

初始状态与准备工作 #

数据库中目标条目的初始JSONB状态：

{
  "a": "b"
}

执行以下SQL完成表创建与初始数据插入：

-- 创建测试表
create table if not exists testing (val jsonb);
-- 插入初始JSONB数据
insert into testing values('{"a":"b"}'::jsonb);

需求说明 #

需要实现统一的SQL语句，无需提前判断errors键是否存在：

• 当errors键不存在时：创建该键并插入目标数组
• 当errors键已存在时：向已有数组追加指定值

注：单独使用原有语句会出现问题——若errors键不存在时执行追加语句，会导致整行val值丢失。

解决方案 #

使用COALESCE函数处理errors键不存在的情况，结合jsonb_set完成统一操作，SQL语句如下：

update testing 
set val = jsonb_set(val, '{errors}', COALESCE(val->'errors', '[]'::jsonb) || '["a","b"]'::jsonb, true)
where val->>'a' = 'b';

逻辑说明 #

1. COALESCE(val->'errors', '[]'::jsonb)：如果errors键不存在，val->'errors'返回null，此时用空数组[]替代，保证后续数组追加操作合法
2. || '["a","b"]'::jsonb：将目标数组追加到（空数组或已有数组）末尾
3. jsonb_set的第四个参数true：允许创建不存在的键，确保errors键不存在时自动生成该键

验证结果 #

1. 初始状态（无errors键）执行后，结果为：

{
  "a": "b",
  "errors": [
    "a",
    "b"
  ]
}

2. 已有errors键时执行后，结果为：

{
  "a": "b",
  "errors": [
    "a",
    "b",
    "a",
    "b"
  ]
}

内容的提问来源于stack exchange，提问作者Dwarakesh Pallagolla