即使下面的函数不能满足这个请求(函数json_object_agg在PostgreSQL 9.3中不可用)，下面的函数对于任何为PostgreSQL 9.4寻找||运算符的人来说都是有用的，因为在即将到来的PostgreSQL 9.5中实现了:

CREATE OR REPLACE FUNCTION jsonb_merge(left JSONB, right JSONB)
RETURNS JSONB
AS $$
SELECT
  CASE WHEN jsonb_typeof($1) = 'object' AND jsonb_typeof($2) = 'object' THEN
       (SELECT json_object_agg(COALESCE(o.key, n.key), CASE WHEN n.key IS NOT NULL THEN n.value ELSE o.value END)::jsonb
        FROM jsonb_each($1) o
        FULL JOIN jsonb_each($2) n ON (n.key = o.key))
   ELSE 
     (CASE WHEN jsonb_typeof($1) = 'array' THEN LEFT($1::text, -1) ELSE '['||$1::text END ||', '||
      CASE WHEN jsonb_typeof($2) = 'array' THEN RIGHT($2::text, -1) ELSE $2::text||']' END)::jsonb
   END     
$$ LANGUAGE sql IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION jsonb_merge(jsonb, jsonb) TO public;
CREATE OPERATOR || ( LEFTARG = jsonb, RIGHTARG = jsonb, PROCEDURE = jsonb_merge );

如果您的字段类型是json，下面将为您工作。

UPDATE 
table_name
SET field_name = field_name::jsonb - 'key' || '{"key":new_val}' 
WHERE field_name->>'key' = 'old_value'.

操作符“-”从左操作数中删除键/值对或字符串元素。键/值对根据键值进行匹配。

运算符'||'将两个jsonb值连接成一个新的jsonb值。

因为这些都是jsonb操作符，你只需要将它们类型转换为::jsonb

更多信息:JSON函数和操作符

你可以在这里阅读我的笔记

遗憾的是，我没有在文档中找到任何东西，但您可以使用一些变通方法，例如您可以编写一些扩展函数。

例如，在Python中:

CREATE or REPLACE FUNCTION json_update(data json, key text, value json)
returns json
as $$
from json import loads, dumps
if key is None: return data
js = loads(data)
js[key] = value
return dumps(js)
$$ language plpython3u

然后

update test set data=json_update(data, 'a', to_json(5)) where data->>'b' = '2';

在PostgreSQL 9.4中，我们实现了以下python函数。它也可以用于PostgreSQL 9.3。

create language plpython2u;

create or replace function json_set(jdata jsonb, jpaths jsonb, jvalue jsonb) returns jsonb as $$
import json

a = json.loads(jdata)
b = json.loads(jpaths)

if a.__class__.__name__ != 'dict' and a.__class__.__name__ != 'list':
  raise plpy.Error("The json data must be an object or a string.")

if b.__class__.__name__ != 'list':
   raise plpy.Error("The json path must be an array of paths to traverse.")

c = a
for i in range(0, len(b)):
  p = b[i]
  plpy.notice('p == ' + str(p))

  if i == len(b) - 1:
    c[p] = json.loads(jvalue)

  else:
    if p.__class__.__name__ == 'unicode':
      plpy.notice("Traversing '" + p + "'")
      if c.__class__.__name__ != 'dict':
        raise plpy.Error("  The value here is not a dictionary.")
      else:
        c = c[p]

    if p.__class__.__name__ == 'int':
      plpy.notice("Traversing " + str(p))
      if c.__class__.__name__ != 'list':
        raise plpy.Error("  The value here is not a list.")
      else:
        c = c[p]

    if c is None:
      break    

return json.dumps(a)
$$ language plpython2u ;

使用示例:

create table jsonb_table (jsonb_column jsonb);
insert into jsonb_table values
('{"cars":["Jaguar", {"type":"Unknown","partsList":[12, 34, 56]}, "Atom"]}');

select jsonb_column->'cars'->1->'partsList'->2, jsonb_column from jsonb_table;

update jsonb_table
set jsonb_column = json_set(jsonb_column, '["cars",1,"partsList",2]', '99');

select jsonb_column->'cars'->1->'partsList'->2, jsonb_column from jsonb_table;

Note that for a previous employer, I have written a set of C functions for manipulating JSON data as text (not as a json or jsonb type) for PostgreSQL 7, 8 and 9. For example, extracting data with json_path('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']'), setting data with json_path_set('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']', '99.87') and so on. It took about 3 days work, so if you need it to run on legacy systems and have the time to spare, it may be worth the effort. I imagine the C version is much faster than the python version.

例如，我的字符串是这样的: {" a1 ":{“a11”:“x”,“a22”:“y”、“a33”:“z”}}

我使用临时表更新jsons，这对于相当小的数据量(<1.000.000)已经足够了。我找到了另一条路，但后来去度假了，就忘了……

所以。查询将是这样的:

with temp_table as (
select 
a.id,
a->'a1'->>'a11' as 'a11',
a->'a1'->>'a22' as 'a22',
a->'a1'->>'a33' as 'a33',
u1.a11updated
from foo a
join table_with_updates u1 on u1.id = a.id)
    update foo a
    set a = ('{"a1": {"a11": "'|| t.a11updated ||'",
        "a22":"'|| t.a22 ||'",
        "a33":"'|| t.a33 ||'"}}')::jsonb
    from temp_table t
    where t.id = a.id;

它与string的关系比json更大，但它是有效的。基本上，它将所有数据拉到临时表中，在用备份的数据插入concat漏洞时创建一个字符串，并将其转换为jsonb。

Json_set可能更有效，但我仍然掌握了它的窍门。当我第一次尝试使用它时，我完全把弦弄乱了……

你觉得这个解决办法怎么样?

它将添加新值或更新现有值。

编辑:编辑后使其与null和空对象一起工作

Edit2:编辑使它与对象中的对象一起工作…

create or replace function updateJsonb(object1 json, object2 json)
returns jsonb
language plpgsql
as
$$
declare
    result jsonb;
    tempObj1 text;
    tempObj2 text;

begin
    tempObj1 = substr(object1::text, 2, length(object1::text) - 2); --remove the first { and last }
    tempObj2 = substr(object2::text, 2, length(object2::text) - 2); --remove the first { and last }

    IF object1::text != '{}' and object1::text != 'null' and object1::text != '[]' THEN
        result = ('{' || tempObj1 || ',' || tempObj2 || '}')::jsonb;
    ELSE
        result = ('{' || tempObj2 || '}')::jsonb;
    END IF;
    return result;
end;
$$;

用法:

update table_name
set data = updatejsonb(data, '{"test": "ok"}'::json)