blocks|key|259957|text|在9.5中使用jsonb_set-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|259958|UPDATE+objects
SET+body+=+jsonb_set(body,+'{name}',+'"Mary"',+true)
WHERE+id+=+1;+|code-block|syntax|javascript|259959|其中body是jsonb列类型。|259960|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

With 9.5 use jsonb_set-

<pre><code>UPDATE objects
SET body = jsonb_set(body, '{name}', '"Mary"', true)
WHERE id = 1; 
</code></pre>

where body is a jsonb column type.

blocks|key|43232|text|UPDATE+test
SET+data+=+data::jsonb+-+'a'+%7C%7C+'{"a":5}'::jsonb
WHERE+data->>'b'+=+'2'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|43233|这似乎可以在PostgreSQL+9.5上运行|unstyled|43234|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>UPDATE test
SET data = data::jsonb - 'a' || '{"a":5}'::jsonb
WHERE data-&gt;&gt;'b' = '2'
</code></pre>

This seems to be working on PostgreSQL 9.5

blocks|key|43334|text|当我尝试更新字符串类型字段时，这对我很有效。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|43335|UPDATE+table_name+
SET+body+=+jsonb_set(body,+'{some_key}',+to_json('value'::text)::jsonb);|code-block|syntax|javascript|43336|希望它能帮助其他人！|43337|假设表table_name有一个名为body的jsonb列，并且您想要更改body.some_key+=‘值’|43338|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This worked for me, when trying to update a string type field.
<pre><code>UPDATE table_name 
SET body = jsonb_set(body, '{some_key}', to_json('value'::text)::jsonb);
</code></pre>
Hope it helps someone else out!
Assuming the table table_name has a jsonb column named body and you want to change body.some_key = 'value'

blocks|key|43107|text|在Python9.4中，我们实现了以下PostgreSQL函数。它也可以与PostgreSQL+9.3一起工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|43108|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+==+'+%2B+str(p))

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

++else:
++++if+p.__class__.__name__+==+'unicode':
++++++plpy.notice("Traversing+'"+%2B+p+%2B+"'")
++++++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+"+%2B+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+;|code-block|syntax|javascript|43109|示例用法：|43110|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;|43111|请注意，对于以前的雇主，我编写了一组C函数，用于在PostgreSQL+7、8和9上以文本(而不是json或jsonb类型)的形式操作JSON数据。例如，使用json_path('{"obj":[12,+34,+{"num":-45.67}]}',+'$.obj[2]['num']')提取数据，使用json_path_set('{"obj":[12,+34,+{"num":-45.67}]}',+'$.obj[2]['num']',+'99.87')设置数据等等。它花了大约3天的时间，所以如果你需要在遗留系统上运行它，并且有空闲的时间，那么它可能是值得的。我想C版本要比python版本快得多。|offset|length|style|CODE|43112|entityMap^0|0|0|0|0|1D|4|1I|5|27|1S|46|25|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@$M|X|N|Y|O|P]|$M|Z|N|10|O|P]|$M|11|N|12|O|P]|$M|13|N|14|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|R|$]]

With PostgreSQL 9.4, we've implemented the following python function. It may also work with PostgreSQL 9.3.

<pre><code>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 ;
</code></pre>

Example usage:

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

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

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

select jsonb_column-&gt;'cars'-&gt;1-&gt;'partsList'-&gt;2, jsonb_column from jsonb_table;
</code></pre>

Note that for a previous employer, I have written a set of C functions for manipulating JSON data as text (not as a <code>json</code> or <code>jsonb</code> type) for PostgreSQL 7, 8 and 9. For example, extracting data with <code>json_path('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']')</code>, setting data with <code>json_path_set('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']', '99.87')</code> 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.

blocks|key|43189|text|尽管以下内容不能满足此请求(+PostgreSQL+9.3中不提供函数json_object_agg+)，但以下内容对于寻找PostgreSQL+9.4的%7C%7C运算符的任何人都很有用，如即将发布的PostgreSQL+9.5中所实现的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|43190|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+'['%7C%7C$1::text+END+%7C%7C',+'%7C%7C
++++++CASE+WHEN+jsonb_typeof($2)+=+'array'+THEN+RIGHT($2::text,+-1)+ELSE+$2::text%7C%7C']'+END)::jsonb
+++END+++++
$$+LANGUAGE+sql+IMMUTABLE+STRICT;
GRANT+EXECUTE+ON+FUNCTION+jsonb_merge(jsonb,+jsonb)+TO+public;
CREATE+OPERATOR+%7C%7C+(+LEFTARG+=+jsonb,+RIGHTARG+=+jsonb,+PROCEDURE+=+jsonb_merge+);|code-block|syntax|javascript|43191|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Even though the following will not satisfy this request (the function json_object_agg is not available in PostgreSQL 9.3), the following can be useful for anyone looking for a || operator for PostgreSQL 9.4, as implemented in the upcoming PostgreSQL 9.5:

<pre><code>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 );
</code></pre>

blocks|key|260044|text|我自己写了一个小函数，在Postgres+9.4中递归工作。下面是这个函数(我希望它能很好地为你工作)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|260045|CREATE+OR+REPLACE+FUNCTION+jsonb_update(val1+JSONB,val2+JSONB)
RETURNS+JSONB+AS+$$
DECLARE
++++result+JSONB;
++++v+RECORD;
BEGIN
++++IF+jsonb_typeof(val2)+=+'null'
++++THEN+
++++++++RETURN+val1;
++++END+IF;

++++result+=+val1;

++++FOR+v+IN+SELECT+key,+value+FROM+jsonb_each(val2)+LOOP

++++++++IF+jsonb_typeof(val2->v.key)+=+'object'
++++++++++++THEN
++++++++++++++++result+=+result+%7C%7C+jsonb_build_object(v.key,+jsonb_update(val1->v.key,+val2->v.key));
++++++++++++ELSE
++++++++++++++++result+=+result+%7C%7C+jsonb_build_object(v.key,+v.value);
++++++++END+IF;
++++END+LOOP;

++++RETURN+result;
END;
$$+LANGUAGE+plpgsql;|code-block|syntax|javascript|260046|以下是示例用法：|260047|select+jsonb_update('{"a":{"b":{"c":{"d":5,"dd":6},"cc":1}},"aaa":5}'::jsonb,+'{"a":{"b":{"c":{"d":15}}},"aa":9}'::jsonb);
++++++++++++++++++++++++++++jsonb_update+++++++++++++++++++++++++++++
---------------------------------------------------------------------
+{"a":+{"b":+{"c":+{"d":+15,+"dd":+6},+"cc":+1}},+"aa":+9,+"aaa":+5}
(1+row)|260048|正如您所看到的，它深入分析并在需要的地方更新/添加值。|260049|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I wrote small function for myself that works recursively in Postgres 9.4. Here is the function (I hope it works well for you):

<pre><code>CREATE OR REPLACE FUNCTION jsonb_update(val1 JSONB,val2 JSONB)
RETURNS JSONB AS $$
DECLARE
 result JSONB;
 v RECORD;
BEGIN
 IF jsonb_typeof(val2) = 'null'
 THEN 
 RETURN val1;
 END IF;

 result = val1;

 FOR v IN SELECT key, value FROM jsonb_each(val2) LOOP

 IF jsonb_typeof(val2-&gt;v.key) = 'object'
 THEN
 result = result || jsonb_build_object(v.key, jsonb_update(val1-&gt;v.key, val2-&gt;v.key));
 ELSE
 result = result || jsonb_build_object(v.key, v.value);
 END IF;
 END LOOP;

 RETURN result;
END;
$$ LANGUAGE plpgsql;
</code></pre>

Here is sample use:

<pre><code>select jsonb_update('{"a":{"b":{"c":{"d":5,"dd":6},"cc":1}},"aaa":5}'::jsonb, '{"a":{"b":{"c":{"d":15}}},"aa":9}'::jsonb);
 jsonb_update 
---------------------------------------------------------------------
 {"a": {"b": {"c": {"d": 15, "dd": 6}, "cc": 1}}, "aa": 9, "aaa": 5}
(1 row)
</code></pre>

As you can see it analyze deep down and update/add values where needed.

blocks|key|43038|text|遗憾的是，我在文档中没有找到任何东西，但您可以使用一些变通方法，例如，您可以编写一些扩展函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|43039|例如，在Python中：|43040|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|code-block|syntax|javascript|43041|然后|43042|update+test+set+data=json_update(data,+'a',+to_json(5))+where+data->>'b'+=+'2';|43043|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Sadly, I've not found anything in the documentation, but you can use some workaround, for example you could write some extended function.

For example, in Python:

<pre><code>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
</code></pre>

and then

<pre><code>update test set data=json_update(data, 'a', to_json(5)) where data-&gt;&gt;'b' = '2';
</code></pre>

blocks|key|259863|text|下面的plpython代码片段可能会派上用场。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|259864|CREATE+EXTENSION+IF+NOT+EXISTS+plpythonu;
CREATE+LANGUAGE+plpythonu;

CREATE+OR+REPLACE+FUNCTION+json_update(data+json,+key+text,+value+text)
+RETURNS+json
+AS+$$
++++import+json
++++json_data+=+json.loads(data)
++++json_data[key]+=+value
++++return+json.dumps(json_data,+indent=4)
+$$+LANGUAGE+plpythonu;

--+Check+how+JSON+looks+before+updating

SELECT+json_update(content::json,+'CFRDiagnosis.mod_nbs',+'1')
FROM+sc_server_centre_document+WHERE+record_id+=+35+AND+template+=+'CFRDiagnosis';

--+Once+satisfied+update+JSON+inplace

UPDATE+sc_server_centre_document+SET+content+=+json_update(content::json,+'CFRDiagnosis.mod_nbs',+'1')
WHERE+record_id+=+35+AND+template+=+'CFRDiagnosis';|code-block|syntax|javascript|259865|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The following plpython snippet might come in handy.

<pre><code>CREATE EXTENSION IF NOT EXISTS plpythonu;
CREATE LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION json_update(data json, key text, value text)
 RETURNS json
 AS $$
 import json
 json_data = json.loads(data)
 json_data[key] = value
 return json.dumps(json_data, indent=4)
 $$ LANGUAGE plpythonu;

-- Check how JSON looks before updating

SELECT json_update(content::json, 'CFRDiagnosis.mod_nbs', '1')
FROM sc_server_centre_document WHERE record_id = 35 AND template = 'CFRDiagnosis';

-- Once satisfied update JSON inplace

UPDATE sc_server_centre_document SET content = json_update(content::json, 'CFRDiagnosis.mod_nbs', '1')
WHERE record_id = 35 AND template = 'CFRDiagnosis';
</code></pre>

blocks|key|260009|text|您还可以在jsonb中原子地递增键，如下所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|260010|UPDATE+users+SET+counters+=+counters+%7C%7C+CONCAT('{"bar":',+COALESCE(counters->>'bar','0')::int+%2B+1,+'}')::jsonb+WHERE+id+=+1;

SELECT+*+FROM+users;

+id+%7C++++counters
----%2B------------
++1+%7C+{"bar":+1}|code-block|syntax|javascript|260011|未定义的密钥->假定起始值为0。|260012|有关更详细的解释，请参阅我的答案：https://stackoverflow.com/a/39076637|260013|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/39076637^0|5|5|0|0|0|H|10|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|11|8|@]|D|@$9|12|A|13|1|14]]|E|$]]|$1|O|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

You can also increment keys atomically within <code>jsonb</code> like this:

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

SELECT * FROM users;

 id | counters
----+------------
 1 | {"bar": 1}
</code></pre>

Undefined key -> assumes starting value of 0.

For more detailed explanation, see my answer here: <a href="https://stackoverflow.com/a/39076637">https://stackoverflow.com/a/39076637</a>

blocks|key|260251|text|你对这个解决方案有什么看法？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|260252|它将添加新值或更新现有值。|260253|编辑:对其进行编辑，使其适用于null和空对象|260254|Edit2:进行编辑，使其与对象中的对象一起工作...|260255|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+=+('{'+%7C%7C+tempObj1+%7C%7C+','+%7C%7C+tempObj2+%7C%7C+'}')::jsonb;
++++ELSE
++++++++result+=+('{'+%7C%7C+tempObj2+%7C%7C+'}')::jsonb;
++++END+IF;
++++return+result;
end;
$$;|code-block|syntax|javascript|260256|用法：|260257|update+table_name
set+data+=+updatejsonb(data,+'{"test":+"ok"}'::json)|260258|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|V|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|W|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|Y|8|@]|9|@]|A|$K|L]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

what do you think about this solution ?
It will add the new value or update an existing one.
Edit: edited to make it work with null and empty object
Edit2: edited to make it work with object in the object...
<pre><code>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;
$$;
</code></pre>
usage:
<pre><code>update table_name
set data = updatejsonb(data, '{&quot;test&quot;: &quot;ok&quot;}'::json)
</code></pre>

blocks|key|43380|text|例如，我的字符串是这样的：{"a1":{"a11":"x"，"a22":"y"，"a33":"z"}}|type|unstyled|depth|inlineStyleRanges|entityRanges|data|43381|我使用临时表来更新json，这对于相当少的数据量(<1.000.000)来说已经足够了。我找到了一条不同的路，但后来去度假了，忘了……|43382|所以。查询将如下所示：|43383|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":+"'%7C%7C+t.a11updated+%7C%7C'",
++++++++"a22":"'%7C%7C+t.a22+%7C%7C'",
++++++++"a33":"'%7C%7C+t.a33+%7C%7C'"}}')::jsonb
++++from+temp_table+t
++++where+t.id+=+a.id;|code-block|syntax|javascript|43384|与json相比，它与string有更多的关系，但它是有效的。基本上，它将所有数据拉到临时表中，在用备份的数据填充连接漏洞的同时创建一个字符串，并将其转换为jsonb。|43385|Json_set可能更高效，但我仍在掌握它的窍门。我第一次试着用它的时候，我把字符串完全弄乱了…|43386|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|T|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

So, for example my string looks like this:
{&quot;a1&quot;:{&quot;a11&quot;:&quot;x&quot;,&quot;a22&quot;:&quot;y&quot;,&quot;a33&quot;:&quot;z&quot;}}
I update jsons by using temp table, which is good enough for rather small amount of data (&lt;1.000.000). I found a different way, but then went on vacation and forgot it...
So. the query will be something like this:
<pre><code>with temp_table as (
select 
a.id,
a-&gt;'a1'-&gt;&gt;'a11' as 'a11',
a-&gt;'a1'-&gt;&gt;'a22' as 'a22',
a-&gt;'a1'-&gt;&gt;'a33' as 'a33',
u1.a11updated
from foo a
join table_with_updates u1 on u1.id = a.id)
 update foo a
 set a = ('{&quot;a1&quot;: {&quot;a11&quot;: &quot;'|| t.a11updated ||'&quot;,
 &quot;a22&quot;:&quot;'|| t.a22 ||'&quot;,
 &quot;a33&quot;:&quot;'|| t.a33 ||'&quot;}}')::jsonb
 from temp_table t
 where t.id = a.id;
</code></pre>
It has more to do with string than json, but it works. Basically, it pulls all the data into temp table, creates a string while plugging concat holes with the data you backed up, and converts it into jsonb.
Json_set might be more efficient, but I'm still getting a hang of it. First time I tried to use it, I messed up the string completely...

blocks|key|260179|text|UPDATE+table_name+SET+attrs+=+jsonb_set(cast(attrs+as+jsonb),+'{key}',+'"new_value"',+true)+WHERE+id+=+'some_id';|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|260180|这对我很有效，attrs是一个json类型的字段。首先强制转换为jsonb，然后更新。|260181|或|260182|UPDATE+table_name+SET+attrs+=+jsonb_set(cast(attrs+as+jsonb),+'{key}',+'"new_value"',+true)+WHERE+attrs->>key+=+'old_value';|260183|entityMap^0|0|35|0|0|0|0|3G|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|R|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|L|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|M|$]]

<code>UPDATE table_name SET attrs = jsonb_set(cast(attrs as jsonb), '{key}', '&quot;new_value&quot;', true) WHERE id = 'some_id'; </code>
This what worked for me, attrs is a json type field. first cast to jsonb then update.
or
<code>UPDATE table_name SET attrs = jsonb_set(cast(attrs as jsonb), '{key}', '&quot;new_value&quot;', true) WHERE attrs-&gt;&gt;key = 'old_value'; </code>

blocks|key|43470|text|如果希望在JSON+update命令中使用其他列的值，可以使用字符串连接：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|43471|UPDATE+table
SET+column1+=+column1::jsonb+-+'key'+%7C%7C+('{"key":+'+%7C%7C+column2::text+%7C%7C++'}')::jsonb
where+...;|code-block|syntax|javascript|43472|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you want to use values from other columns in your JSON update command you can use string concatenation:
<pre><code>UPDATE table
SET column1 = column1::jsonb - 'key' || ('{&quot;key&quot;: ' || column2::text || '}')::jsonb
where ...;
</code></pre>

With postgresql 9.3 I can <code>SELECT</code> specific fields of a JSON data type, but how do you modify them using <code>UPDATE</code>? I can't find any examples of this in the postgresql documentation, or anywhere online. I have tried the obvious:
<pre class="lang-sql prettyprint-override"><code>postgres=# create table test (data json);
CREATE TABLE
postgres=# insert into test (data) values ('{&quot;a&quot;:1,&quot;b&quot;:2}');
INSERT 0 1
postgres=# select data-&gt;'a' from test where data-&gt;&gt;'b' = '2';
 ?column?
----------
 1
(1 row)
postgres=# update test set data-&gt;'a' = to_json(5) where data-&gt;&gt;'b' = '2';
ERROR: syntax error at or near &quot;-&gt;&quot;
LINE 1: update test set data-&gt;'a' = to_json(5) where data-&gt;&gt;'b' = '2...
</code></pre>

How do I modify fields inside the new PostgreSQL JSON datatype?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

使用PostgreSQL9.3，我可以对JSON数据类型的特定字段执行SELECT操作，但是如何使用UPDATE修改它们呢？我在postgresql文档或者在线的任何地方都找不到任何这样的例子。我尝试过显而易见的方法：postgres=# create table test (data json);CREATE TABLEpostgres=# insert into test (data) val

问如何修改新的PostgreSQL JSON数据类型中的字段？
EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何修改新的PostgreSQL JSON数据类型中的字段？EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何修改新的PostgreSQL JSON数据类型中的字段？
EN