将元素添加到 Postgres 中的 JSON 对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13615760/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add element to JSON object in Postgres
提问by thepriebe
I have a text field in a database (postgres 9.2.1) with a json blob in it. It looks something similar to this except all on a single line, obviously:
我在数据库（postgres 9.2.1）中有一个文本字段，其中有一个 json blob。它看起来与此类似，但都在一行上，显然：
{
"keyword": {
"checked": "1",
"label": "Keyword"
},
"agency_name": {
"checked": "0",
"label": "Agency Name"
}
}
I need to add an element to json array so that it looks like this:
我需要向 json 数组添加一个元素，使其看起来像这样：
{
"keyword": {
"checked": "1",
"label": "Keyword"
},
"something_new": {
"checked": "1",
"label": "Something New"
},
"agency_name": {
"checked": "0",
"label": "Agency Name"
}
}
I'm not as concerned about the placement of the new array element. It could be after agency_name. Is there an easy way to do this in postgres?
我不太关心新数组元素的位置。它可能在agency_name 之后。有没有一种简单的方法可以在 postgres 中做到这一点？
采纳答案by Dhruvil Thaker
Even I had the same problem, I wanted to dynamically append new elements to jsonb[].
即使我有同样的问题，我也想动态地将新元素附加到 jsonb[]。
Assume column_jsonb[] = [{"name":"xyz","age":"12"}]
假设 column_jsonb[] = [{"name":"xyz","age":"12"}]
UPDATE table_name
SET column_jsonb[] = array_append(column_jsonb[],'{"name":"abc","age":"22"}');
Result : [{"name":"xyz","age":"12"},{"name":"abc","age":"22"}]
结果：[{"name":"xyz","age":"12"},{"name":"abc","age":"22"}]
回答by NewBee
If upgrade to PG9.5.1, then you can use sql operate ||to merge jsonb, example
如果升级到PG9.5.1，那么可以使用sql操作||合并jsonb，例子
select '{"a":1}'::jsonb || '{"a":2, "b":2}'::jsonb
will return {"a": 2, "b": 2}
将返回 {"a": 2, "b": 2}
If you can't upgrade to pg9.5.1, IMHO, doing the job in your code will be a better choice. You can parse old jsonb string as a map, and then update the map, then convert to string and update db-record.
如果您无法升级到 pg9.5.1，恕我直言，在您的代码中完成这项工作将是更好的选择。您可以将旧的 jsonb 字符串解析为映射，然后更新映射，然后转换为字符串并更新 db-record。
回答by pumpkindle
use ||to merge jsonb and set the value to it
用于||合并 jsonb 并为其设置值
example:
例子：
origin:
起源：
in table a:
在表中a：
id | info
1 | {"aa":"bb"}
2 | {"aa":"cc"}
身 | 信息
1 | {"aa":"bb"}
2 | {"aa":"cc"}
after executing:
执行后：
update a set info = info::jsonb || ('{"id":}' || id || '}' )::jsonb
generates:
产生：
id | info
1 | {"aa":"bb","id":1}
2 | {"aa":"cc","id":2}
身 | 信息
1 | {"aa":"bb","id":1}
2 | {"aa":"cc","id":2}
something_else:
别的东西：
- use
-‘key'to delete element in jsonb - merge will replace origin one if the two jsonb has same key
- 用于
-‘key'删除jsonb中的元素 - 如果两个 jsonb 具有相同的键，合并将替换原点一
回答by Edmund
PostgreSQL does not yet have much in the way of JSON support functions: all I can see are ones like array_to_json, which could be useful if there was a corresponding way to convert the original JSON into an array, which you could then manipulate to add that additional element before converting back to JSON.
PostgreSQL 还没有太多的 JSON 支持函数：我能看到的只有像array_to_json，如果有相应的方法将原始 JSON 转换为数组，这可能很有用，然后您可以操作它来添加额外的转换回 JSON 之前的元素。
Possibly the best thing is to use a PL language to manipulate the JSON. One obvious one would be PLV8, which provides JavaScript programming functionality in PostgreSQL. You would write a user-defined function in JavaScript which would manipulate the JSON blob accordingly:
可能最好的方法是使用 PL 语言来操作 JSON。一个明显的例子是 PLV8，它在 PostgreSQL 中提供 JavaScript 编程功能。您将在 JavaScript 中编写一个用户定义的函数，该函数将相应地操作 JSON blob：
Of course, many other PL languages like Java, Python or Perl may be just as good at working with JSON data, and possibly easier to install on your system. User-defined functions can be written in each of these if you have them set up.
当然，Java、Python 或 Perl 等许多其他 PL 语言可能同样擅长处理 JSON 数据，并且可能更容易在您的系统上安装。如果您设置了用户定义的函数，则可以在其中的每一个中编写它们。
回答by Andrey Nehaychik
Version 9.5 provides jsonb_setfunction with create_missing=TRUE. In any other cases use the following hack for appending information:
9.5 版提供了jsonb_set函数，create_missing=TRUE。在任何其他情况下，使用以下技巧来附加信息：
SELECT (trim( trailing '}' from data::text) || ', "c":2}')::json
To add/replace new value using more correct way:
使用更正确的方法添加/替换新值：
UPDATE t
SET data=t3.data
FROM t AS t1
INNER JOIN
(
SELECT id, json_object_agg(t.k,t.v)
FROM
(
SELECT *
FROM (SELECT id, json_object_keys(data) as k, data->json_object_keys(data) as v FROM t) as t2
WHERE t.k != 'c'
UNION ALL
SELECT id, 'c'::text as k, '"new value"'::json as v FROM t1
) as t3
GROUP by id
) as t4 ON (t1.id=t4.id)
To remove key:
删除密钥：
UPDATE t
SET data=t3.data
FROM t AS t1
INNER JOIN
(
SELECT id, json_object_agg(t.k,t.v)
FROM (SELECT id, json_object_keys(data) as k, data->json_object_keys(data) as v FROM t) as t2
WHERE t.k != 'c'
GROUP by id
) as t4 ON (t1.id=t4.id)
回答by Andrew Wolfe
I had exactly this problem. This solution is fairly 'pure' object manipulation and I sort of prefer 'sql' functions to plpgsql. The main thing is to disaggregate using json_each - giving you a record - and then creating a record from the
我正是有这个问题。这个解决方案是相当“纯”的对象操作，我更喜欢“sql”函数而不是plpgsql。最主要的是使用 json_each 进行分解 - 给你一个记录 - 然后从
CREATE OR REPLACE FUNCTION json_extend_object(
input_object json,
append_key text,
append_object json)
RETURNS json AS
$BODY$
select json_object_agg (((json_val)::record).key, ((json_val)::record).value)
from (
select json_val
from (select json_each (input_object) as json_val) disaggr
where ((json_val::record).key != append_key)
union
select newvals
from (
select append_key, append_object
) newvals
) to_rows;
$BODY$
LANGUAGE sql IMMUTABLE
;
回答by clancien
Solution provided by Andrew Wolfe is good, but not compatible with postgres version < 9.4. And if you have a 9.4+ version, just use jsonb and || (concatenate) operator to add an element to json.
Andrew Wolfe 提供的解决方案很好，但与 postgres 版本 < 9.4 不兼容。如果您有 9.4+ 版本，只需使用 jsonb 和 || （连接）运算符将元素添加到 json。
So, here is the 9.3 compatible version of json_extend_object:
所以，这里是 json_extend_object 的 9.3 兼容版本：
CREATE OR REPLACE FUNCTION json_extend_object(
input_object json,
append_key text,
append_object json)
RETURNS json AS
$BODY$
select ('{'||string_agg(''||to_json((json_val::record).key)||':'|| to_json((json_val::record).value), ',')||'}')::json
from (
select json_val
from (select json_each (input_object) as json_val) jsonvals
where ((json_val::record).key != append_key)
union
select newvals
from (select append_key, append_object) newvals
) to_rows;
$BODY$
LANGUAGE sql IMMUTABLE;
