How to update a new json in old json in postgres?-CodePudding

this is my data which is available in the database.

INSERT INTO suppliers (id, "createdAt", "updatedAt", "userId", "fullName", email, phone, "profilePicture", "isEnabled", "role", "originDistrict", tln, "companyName", "companyAddress", "assignKam", "assignCluster", "vehicleProvide", "makePayout", "subsType", gender, status, "lookAtBid", "bidAccepted", "activeTrip", "isVerified") VALUES(36, '2022-02-21 18:46:01.527', '2022-09-12 13:20:31.132', '01FRJH5K6GV6KT4YP7RQ4H7N7K', 'syed musa tazim', NULL, ' 8801696678835', NULL, false, 'vendor', '{"id": 46, "nameBn": "গাজীপুর", "nameEn": "Gazipur", "status": "active", "createdAt": "2021-12-31T18:08:40.056Z"}'::jsonb, NULL, 'Rofiqul Islam (GZP)', NULL, '{"role": "kam", "team": "vendor_team", "zone": "gazipur", "email": "[email protected]", "phone": " 8801721666512", "userId": "01GCDMQ7DS48XTCMT5JBYSS4NF", "fullName": "Mohammad Tareq"}'::jsonb, '{"role": "cluster_head", "zone": "gazipur", "email": "[email protected]", "phone": " 8801328282828", "userId": "01GCNEE49Z3ZCBT73BK2BHVY6P", "fullName": "Iftikar Bhaia Gazipur"}'::jsonb, 20, 'monday'::public."suppliers_makepayout_enum", 'gold'::public."suppliers_substype_enum", 'male'::public."suppliers_gender_enum", 'inactive'::public."suppliers_status_enum", 0, 0, 0, false); INSERT INTO suppliers (id, "createdAt", "updatedAt", "userId", "fullName", email, phone, "profilePicture", "isEnabled", "role", "originDistrict", tln, "companyName", "companyAddress", "assignKam", "assignCluster", "vehicleProvide", "makePayout", "subsType", gender, status, "lookAtBid", "bidAccepted", "activeTrip", "isVerified") VALUES(143, '2022-07-14 10:39:36.397', '2023-01-11 16:03:26.445', '01G7XG2M1X1Y60H3W6GYBR2Q40', 'abcd', '[email protected]', ' 8801828378263', NULL, true, 'vendor', '{"id": 46, "nameBn": "গাজীপুর", "nameEn": "Gazipur", "status": "active", "createdAt": "2021-12-31T18:08:40.056Z"}'::jsonb, NULL, 'Transport Agency (GZP)', 'abcd', '{"role": "kam", "team": "vendor_team", "zone": "gazipur", "email": "[email protected]", "phone": " 8801312121215", "userId": "01GCB9NMCR0FVE7NTE825T06PF", "fullName": "A.B.M. Asaff-Ud-Daula"}'::jsonb, '{"role": "cluster_head", "zone": "gazipur", "email": "[email protected]", "phone": " 8801328282828", "userId": "01GCNEE49Z3ZCBT73BK2BHVY6P", "fullName": "Iftikar Bhaia Gazipur"}'::jsonb, 50, 'wednesday'::public."suppliers_makepayout_enum", 'bronze'::public."suppliers_substype_enum", 'male'::public."suppliers_gender_enum", 'active'::public."suppliers_status_enum", 3, 0, 0, true); INSERT INTO suppliers (id, "createdAt", "updatedAt", "userId", "fullName", email, phone, "profilePicture", "isEnabled", "role", "originDistrict", tln, "companyName", "companyAddress", "assignKam", "assignCluster", "vehicleProvide", "makePayout", "subsType", gender, status, "lookAtBid", "bidAccepted", "activeTrip", "isVerified") VALUES(51, '2022-02-21 18:46:01.527', '2022-10-19 14:17:16.790', '01FV9WW0SZ58C3HX1XV8N6AKSR', 'some vendor 3 update yes', NULL, ' 8801711223343', NULL, false, 'vendor', '{"id": 46, "nameBn": "গাজীপুর", "nameEn": "Gazipur", "status": "active", "createdAt": "2021-12-31T18:08:40.056Z"}'::jsonb, NULL, 'Matrichaya Transport Agency (GZP) yes', 'some vendor 3 company address', '{"role": "kam", "team": "vendor_team", "zone": "gazipur", "email": "[email protected]", "phone": " 8801721666512", "userId": "01GCDMQ7DS48XTCMT5JBYSS4NF", "fullName": "Mohammad Tareq"}'::jsonb, '{"role": "cluster_head", "zone": "gazipur", "email": "[email protected]", "phone": " 8801328282828", "userId": "01GCNEE49Z3ZCBT73BK2BHVY6P", "fullName": "Bimol Das"}'::jsonb, 200, 'monday'::public."suppliers_makepayout_enum", 'gold'::public."suppliers_substype_enum", 'male'::public."suppliers_gender_enum", 'inactive'::public."suppliers_status_enum", 0, 0, 0, true); INSERT INTO suppliers (id, "createdAt", "updatedAt", "userId", "fullName", email, phone, "profilePicture", "isEnabled", "role", "originDistrict", tln, "companyName", "companyAddress", "assignKam", "assignCluster", "vehicleProvide", "makePayout", "subsType", gender, status, "lookAtBid", "bidAccepted", "activeTrip", "isVerified") VALUES(45, '2022-02-21 18:46:01.527', '2022-10-18 10:13:43.622', '01FSP5XNHEN9WH06Z320YSE416', 'tazim', NULL, ' 8801676553344', NULL, false, 'vendor', '{"id": 46, "nameBn": "গাজীপুর", "nameEn": "Gazipur", "status": "active", "createdAt": "2021-12-31T18:08:40.056Z"}'::jsonb, NULL, 'Md. Parvez Alam (GZP)', 'h-747, road-08 ,Adabor ,Dhaka', '{"role": "kam", "team": "vendor_team", "zone": "gazipur", "email": "[email protected]", "phone": " 8801721666512", "userId": "01GCDMQ7DS48XTCMT5JBYSS4NF", "fullName": "Mohammad Tareq"}'::jsonb, '{"role": "cluster_head", "zone": "gazipur", "email": "[email protected]", "phone": " 8801328282828", "userId": "01GCNEE49Z3ZCBT73BK2BHVY6P", "fullName": "Iftikar Bhaia Gazipur"}'::jsonb, 12, 'monday'::public."suppliers_makepayout_enum", 'tin'::public."suppliers_substype_enum", 'male'::public."suppliers_gender_enum", 'inactive'::public."suppliers_status_enum", 0, 0, 0, false); INSERT INTO suppliers (id, "createdAt", "updatedAt", "userId", "fullName", email, phone, "profilePicture", "isEnabled", "role", "originDistrict", tln, "companyName", "companyAddress", "assignKam", "assignCluster", "vehicleProvide", "makePayout", "subsType", gender, status, "lookAtBid", "bidAccepted", "activeTrip", "isVerified") VALUES(138, '2022-07-06 13:33:43.305', '2023-01-11 16:33:44.418', '01G796VP5GZWBM90BSXSB5QE6A', 'testing vendor fullname 5', NULL, ' 8801345454545', NULL, true, 'vendor', '{"id": 46, "nameBn": "গাজীপুর", "nameEn": "Gazipur", "status": "active", "createdAt": "2021-12-31T18:08:40.056Z"}'::jsonb, NULL, 'Connect Transport Agency (GZP)', 'tesiting vendor address', '{"role": "kam", "team": "vendor_team", "zone": "gazipur", "email": "[email protected]", "phone": " 8801312121215", "userId": "01GCB9NMCR0FVE7NTE825T06PF", "fullName": "A.B.M. Asaff-Ud-Daula"}'::jsonb, '{"role": "cluster_head", "zone": "gazipur", "email": "[email protected]", "phone": " 8801328282828", "userId": "01GCNEE49Z3ZCBT73BK2BHVY6P", "fullName": "Iftikar Bhaia Gazipur"}'::jsonb, 0, 'wednesday'::public."suppliers_makepayout_enum", 'gold'::public."suppliers_substype_enum", 'male'::public."suppliers_gender_enum", 'active'::public."suppliers_status_enum", 14, 0, 1, true);

I want to replace this

{"role": "cluster_head", "zone": "gazipur", "email": "[email protected]", "phone": " 8801328282828", "userId": "01GCNEE49Z3ZCBT73BK2BHVY6P", "fullName": "Iftikar Bhaia Gazipur"}

with

{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: '[email protected]',createdAt: '2022-08-31T07:44:00.905Z',gender: 'male',role: 'cluster_head'}

how can I change it?

I will get the 2nd json from another service. So I want to replace it. I have tried like this

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: '[email protected]',createdAt: '2022-08-31T07:44:00.905Z',gender: 'male',role: 'cluster_head'})::jsonb where "assignCluster"->>"userId" = '01GBSDPSCAE74FVCK2XPPCRDYH';

CodePudding user response：

Don't use jsonb_set. You want to replace the entire value of the column, not manipulate some JSON.
Also you need to put the JSON value in an SQL literal, and properly quote property names and strings in it.

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": "[email protected]", "createdAt": "2022-08-31T07:44:00.905Z", "gender": "male", "role": "cluster_head"}'
where "assignCluster"->>'userId' = '01GBSDPSCAE74FVCK2XPPCRDYH';