当我尝试更新字符串类型字段时，这对我有用。

UPDATE table_name 
SET body = jsonb_set(body, '{some_key}', to_json('value'::text)::jsonb);

希望它能帮助其他人!

假设表table_name有一个名为body的jsonb列，并且您想要更改body。Some_key = 'value'

我发现以前的答案更适合有经验的PostgreSQL用户。这是给初学者的:

假设你有一个JSONB类型的表列，值如下:

{
    "key0": {
        "key01": "2018-05-06T12:36:11.916761+00:00",
        "key02": "DEFAULT_WEB_CONFIGURATION",
        
    "key1": {
        "key11": "Data System",
        "key12": "<p>Health,<p>my address<p>USA",
        "key13": "*Please refer to main screen labeling"
    }
}

让我们假设我们想在行中设置一个新值:

"key13": "*Please refer to main screen labeling"

取而代之的是放置值:

"key13": "See main screen labeling"

我们使用json_set()函数将一个新值赋给key13

jsonb_set()的参数

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

在“target”-我将放置jsonb column-name(这是正在修改的表列)

"path"-是指向(包括)我们要覆盖的键的"json keys路径"

"new_value" -这是我们分配的新值

在我们的例子中，我们想要更新位于key1下的key13的值(key1 -> key13):

因此路径语法是:'{key1,key13}' (路径是最棘手的部分-因为教程是可怕的)

jsonb_set(jsonb_column,'{key1,key13}','"See main screen labeling"')

从PostgreSQL 14开始，你可以使用jsonb下标直接访问JSON字段的元素，并最终更新它们。

UPDATE test SET data['a'] = '5' WHERE data['b'] = '2';

你也可以像这样在jsonb中原子地增加键:

UPDATE users SET counters = counters || CONCAT('{"bar":', COALESCE(counters->>'bar','0')::int + 1, '}')::jsonb WHERE id = 1;

SELECT * FROM users;

 id |    counters
----+------------
  1 | {"bar": 1}

>假设起始值为0。

更详细的解释，请看我的回答:https://stackoverflow.com/a/39076637

在@pozs回答的基础上，这里有两个可能对一些人有用的PostgreSQL函数。(需要PostgreSQL 9.3+)

按键删除:按键从JSON结构中删除一个值。

CREATE OR REPLACE FUNCTION "json_object_del_key"(
  "json"          json,
  "key_to_del"    TEXT
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE
  WHEN ("json" -> "key_to_del") IS NULL THEN "json"
  ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
          FROM (SELECT *
                  FROM json_each("json")
                 WHERE "key" <> "key_to_del"
               ) AS "fields")::json
END
$function$;

按键递归删除:按键路径从JSON结构中删除一个值。(需要@pozs的json_object_set_key函数)

CREATE OR REPLACE FUNCTION "json_object_del_path"(
  "json"          json,
  "key_path"      TEXT[]
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE
  WHEN ("json" -> "key_path"[l] ) IS NULL THEN "json"
  ELSE
     CASE COALESCE(array_length("key_path", 1), 0)
         WHEN 0 THEN "json"
         WHEN 1 THEN "json_object_del_key"("json", "key_path"[l])
         ELSE "json_object_set_key"(
           "json",
           "key_path"[l],
           "json_object_del_path"(
             COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json,
             "key_path"[l+1:u]
           )
         )
       END
    END
  FROM array_lower("key_path", 1) l,
       array_upper("key_path", 1) u
$function$;

使用例子:

s1=# SELECT json_object_del_key ('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}',
                                 'foo'),
            json_object_del_path('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}',
                                 '{"foo","moe"}');

 json_object_del_key |          json_object_del_path
---------------------+-----------------------------------------
 {"hello":[7,3,1]}   | {"hello":[7,3,1],"foo":{"mofu":"fuwa"}}

select * from pg_settings where name = 'deadlock_timeout';
begin;
create temp table a2(data jsonb);
insert into a2 values('{
    "key0": {
        "key01": "2018-05-06T12:36:11.916761+00:00",
        "key02": "DEFAULT_WEB_CONFIGURATION",
    "key1": {
        "key11": "Data System",
        "key12": "<p>Health,<p>my address<p>USA",
        "key13": "*Please refer to main screen labeling"
    }
}}'::jsonb);
commit;

嵌套的jsonb结构更新。可应用于删除。

update a2 set data =
    data::jsonb #-  '{key0, key1, key13}'
        || '{"key13":"screen labeling"}'::jsonb
returning *;