logo标签列表

在Postgres中向JSON对象添加元素

jsonstringpostgresqlselectreplace
44

我在数据库中（postgres 9.2.1）有一个文本字段，其中包含一个JSON blob。它看起来类似于这个样子，除了所有内容都在一行上：

{
  "keyword": {
    "checked": "1",
    "label": "Keyword"
  },
  "agency_name": {
    "checked": "0",
    "label": "Agency Name"
  }
}

我需要将一个元素添加到 JSON 数组中，使它看起来像这样：

{
  "keyword": {
    "checked": "1",
    "label": "Keyword"
  },
  "something_new": {
    "checked": "1",
    "label": "Something New"
  },
  "agency_name": {
    "checked": "0",
    "label": "Agency Name"
  }
}

我不太关心新数组元素的位置。它可以在 agency_name 之后。在 postgres 中是否有简单的方法实现这一点？

1
尝试阅读https://dev59.com/1mkv5IYBdhLWcg3wfA0P。它可能会对你有所帮助。 - Ihor Romanchenko
可能属于数据库管理员组。 - Andrew Wolfe
7个回答
58

如果您升级到PG9.5.1，则可以使用SQL运算符||来合并jsonb，例如

select '{"a":1}'::jsonb || '{"a":2, "b":2}'::jsonb

将返回{"a": 2, "b": 2}

如果您无法升级到pg9.5.1，我认为在您的代码中完成该工作将是更好的选择。您可以将旧的jsonb字符串解析为映射，然后更新映射，然后将其转换为字符串并更新db记录。

如果我们想要更新（添加）JSONB字段：

UPDATE <table>
SET <field-name> = <field-name> || '{"a": 1}'::jsonb
WHERE id = <some id>

Postgres 9.5中详细介绍所有JSON操作的文档

22

使用||将jsonb合并，并将值设置为它

示例：

原始数据：

在表格a中：

id | info

1 | {"aa":"bb"}

2 | {"aa":"cc"}

执行后：

update a set info = info::jsonb || ('{"id":' || id || '}' )::jsonb

生成：

id | info

1 | {"aa":"bb","id":1}

2 | {"aa":"cc","id":2}

其他内容：

  1. 使用 - ‘key’ 删除jsonb中的元素
  2. 合并操作会替换掉原始元素，如果两个jsonb具有相同的键
10

我也遇到过同样的问题，我想动态地将新元素添加到jsonb[]中。

假设 column_jsonb[] = [{"name":"xyz","age":"12"}]

UPDATE table_name
   SET column_jsonb[] = array_append(column_jsonb[],'{"name":"abc","age":"22"}');

结果：[{"name":"xyz","age":"12"},{"name":"abc","age":"22"}]

7
即使你的高贵有同样的问题，那我们还有什么机会呢？哈哈。 - eyoeldefare
5
PostgreSQL目前还没有很多与JSON相关的支持函数，我能看到的只有像array_to_json这样的函数，如果有对应的方法将原始JSON转换为数组，则该函数可能会很有用。之后你可以操纵该数组添加额外的元素，再将其转换回JSON。

也许最好的方法是使用PL语言来操纵JSON。一个明显的选择是PLV8，在PostgreSQL中提供JavaScript编程功能。你可以在JavaScript中编写一个用户定义的函数，然后相应地操纵JSON数据块：

当然，许多其他编程语言（如Java、Python或Perl）可能同样擅长处理JSON数据，并且在您的系统上安装可能更加容易。如果您已经设置好了这些语言，那么可以使用它们编写用户定义的函数。
2
显然不是我想要的答案，但仍然可以接受。谢谢。 - PREEB
4

版本9.5提供了带有create_missing=TRUE的jsonb_set函数。在其他情况下，请使用以下技巧添加信息：

SELECT (trim( trailing '}' from data::text) || ', "c":2}')::json

使用更正确的方式添加/替换新值：

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)

移除密钥：

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)
感谢您的回答。两年前我无法使用PostgreSQL 9.5。自那时起，我们开始使用postgres提供的JSON数据类型，并使用PHP进行编码/解码。当JSON只是DB中的字符串时，这使得事情变得有点困难。 - PREEB
1
我曾经遇到过这个问题。这个解决方案相当纯粹的对象操作，而我更喜欢使用 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
  ;
如何将内容添加到文档开头？ - igilfanov
将“newvals”子查询和UNION放在“to_rows”子查询的“select json_val”分支之前，而不是之后。 - Andrew Wolfe
1

Andrew Wolfe提供的解决方案不错，但与postgres版本<9.4不兼容。如果您有9.4+版本，请使用jsonb和||（连接）运算符将元素添加到json中。

因此，这里是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;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接