如何在PostgreSQL中替换JSON字段内的旧JSON对象

PostgreSQL数据库的suppliers表中，assignCluster字段为jsonb类型，当前存储的旧JSON对象如下：

{"role": "cluster_head", "zone": "gazipur", "email": "a28@gmail.com", "phone": "+8801328282828", "userId": "01GCNEE49Z3ZCBT73BK2BHVY6P", "fullName": "Iftikar Bhaia Gazipur"}

需要将其替换为新的JSON对象：

{"userId": "01GBSDPSCAE74FVCK2XPPCRDYH", "profilePicture": "https://dev-cdn.loopfreight.io/public/cd4a89cae5d4516efabf73ccd774583e.jpg", "fullName": "Rayhan Zaman", "isEnabled": true, "isVerified": true, "designation": "Manager ", "department": "operation", "zone": "khulna", "phone": "+8801811111111", "email": "rayhan@gmail.com", "createdAt": "2022-08-31T07:44:00.905Z", "gender": "male", "role": "cluster_head"}

尝试了以下SQL语句但未成功，求正确实现方法：

update suppliers set "assignCluster" = jsonb_set({userId: '01GBSDPSCAE74FVCK2XPPCRDYH',profilePicture: 'https://dev-cdn.loopfreight.io/public/cd4a89cae5d4516efabf73ccd774583e.jpg',fullName: 'Rayhan Zaman',isEnabled: true,isVerified: true,designation: 'Manager ',department: 'operation',zone: 'khulna',phone: '+8801811111111',email: 'rayhan@gmail.com',createdAt: '2022-08-31T07:44:00.905Z',gender: 'male',role: 'cluster_head'})::jsonb where "assignCluster"->>"userId" = '01GBSDPSCAE74FVCK2XPPCRDYH'; 

1. jsonb_set用法错误：jsonb_set用于修改JSON中的特定键值，而非替换整个JSON对象，你的需求是直接替换字段内容，无需使用该函数。
2. JSON格式不符合PostgreSQL要求：PostgreSQL要求JSON键名必须用双引号，你写的单引号键名（如userId: 'xxx'）会触发语法错误。
3. WHERE条件逻辑错误：要更新的是旧userId对应的记录，但你的条件是匹配新userId，导致无法找到目标数据。

方法1：直接赋值标准JSON字符串 #

UPDATE suppliers
SET "assignCluster" = '{
    "userId": "01GBSDPSCAE74FVCK2XPPCRDYH",
    "profilePicture": "https://dev-cdn.loopfreight.io/public/cd4a89cae5d4516efabf73ccd774583e.jpg",
    "fullName": "Rayhan Zaman",
    "isEnabled": true,
    "isVerified": true,
    "designation": "Manager ",
    "department": "operation",
    "zone": "khulna",
    "phone": "+8801811111111",
    "email": "rayhan@gmail.com",
    "createdAt": "2022-08-31T07:44:00.905Z",
    "gender": "male",
    "role": "cluster_head"
}'::jsonb
WHERE "assignCluster"->>"userId" = '01GCNEE49Z3ZCBT73BK2BHVY6P';

方法2：用jsonb_build_object构造对象（可读性更强） #

UPDATE suppliers
SET "assignCluster" = jsonb_build_object(
    'userId', '01GBSDPSCAE74FVCK2XPPCRDYH',
    'profilePicture', 'https://dev-cdn.loopfreight.io/public/cd4a89cae5d4516efabf73ccd774583e.jpg',
    'fullName', 'Rayhan Zaman',
    'isEnabled', true,
    'isVerified', true,
    'designation', 'Manager ',
    'department', 'operation',
    'zone', 'khulna',
    'phone', '+8801811111111',
    'email', 'rayhan@gmail.com',
    'createdAt', '2022-08-31T07:44:00.905Z',
    'gender', 'male',
    'role', 'cluster_head'
)
WHERE "assignCluster"->>"userId" = '01GCNEE49Z3ZCBT73BK2BHVY6P';

• 移除多余的jsonb_set，直接赋值替换整个JSONB字段
• 确保JSON键名使用双引号，符合标准语法
• 修正WHERE条件，匹配旧数据中的userId以定位目标记录

内容的提问来源于stack exchange，提问作者Mehdi Hassan Jony