如何修改 PostgreSQL JSONB 数据类型中的单个属性值

Question

如何修改PostgreSQL JSONB数据类型中的单个字段？

假设我有一张名为 animal 的表，如下所示：

id       info
------------------------------------------------------------
49493   {"habit1":"fly","habit2":"dive","location":"SONOMA NARITE"}

我想简单地更改位置属性的值（例如，将文本更改为大写或小写）。 所以更新后的结果是

    id       info
------------------------------------------------------------
49493   {"habit1":"fly","habit2":"dive","location":"sonoma narite"}

我在下面试过这个，但它不起作用

update animal set info=jsonb_set(info, '{location}', LOWER(info->>'location'), true) where id='49493';
----------------------------------
ERROR:  function jsonb_set(jsonb, unknown, text, boolean) does not exist
LINE 7: update animal set info=jsonb_set(info, '{con...
                                           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********
ERROR: function jsonb_set(jsonb, unknown, text, boolean) does not exist

如果我只是知道更新的值是什么，那么我可以使用这个：

update animal set info=jsonb_set(info, '{location}', '"sonoma narite"', true) where id='49493';

但是，如果文本值未知，我们只想做一些简单的操作，例如追加、前置、大写/小写，我无法简单地找到答案。

令我惊讶的是，jsonb set 函数不提供如此微不足道的操作，只尝试更新 jsonb 中文本属性的大小写。

有人可以帮忙吗？

Answer 1

jsonb_set()的第三个参数应该是jsonb类型。问题在于将文本字符串转换为 jsonb 字符串，您需要一个双引号中的字符串。您可以使用concat()或format()：

update animal
set info = 
    jsonb_set(info, '{location}', concat('"', lower(info->>'location'), '"')::jsonb, true) 
--  jsonb_set(info, '{location}', format('"%s"', lower(info->>'location'))::jsonb, true) 
where id='49493'
returning *;
  id   |                               info                               
-------+------------------------------------------------------------------
 49493 | {"habit1": "fly", "habit2": "dive", "location": "sonoma narite"}
(1 row)

---

在 Postgres 9.4 中，您应该使用 jsonb_each_text（） 取消嵌套 json 列，动态聚合修改正确值的键和值，最后构建一个 json 对象：

update animal a
set info = u.info
from (
    select id, json_object(
        array_agg(key), 
        array_agg(
            case key when 'location' then lower(value)
            else value end))::jsonb as info
    from animal,
    lateral jsonb_each_text(info) 
    group by 1
    ) u
where u.id = a.id
and a.id = 49493;

如果您可以创建函数，则此解决方案可能会更令人愉快：

create or replace function update_info(info jsonb)
returns jsonb language sql as $$
    select json_object(
        array_agg(key), 
        array_agg(
            case key when 'location' then lower(value)
            else value end))::jsonb
    from jsonb_each_text(info)
$$
update animal
set info = update_info(info)
where id = 49493;

Answer 2

我想在这里分享一些好消息。感谢克林，他的投入帮助我发现了这个解决方案。在上面的例子中。如果我只是使用 concat 函数，那么我在 klin 发布的代码中发现的问题就解决了（简而言之，它仅在文本值包含空格时才有效）。现在我可以将单个属性值小写！

UPDATE test1 set info=jsonb_set(info, '{location}', concat('"',lower(info->>'locatioin'),'"')::jsonb, true) returning *;