如何修改PostgreSQL JSONB数据类型内的单个属性值? [英] How do I modify a single property value inside the PostgreSQL JSONB datatype?

问题描述

如何修改PostgreSQL JSONB数据类型内的单个字段?

How do I modify a single field inside the PostgreSQL JSONB datatype?

比方说，我有一个叫动物的桌子，像这样:

Let's say I have a table called animal like this:

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

我想简单地更改location属性的值(例如，将文本更改为大写或小写).所以更新后的结果是

I'd like to simply change value(say, to upper case or lower case the text) of the location property. so the result, after UPDATE is

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

我在下面尝试了此方法，但这不起作用

I tried this below and it does not work

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

如果我只是知道更新后的值是什么，那么我可以使用它:

if I simply know what the updated value would be then I can use just use this:

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

但是，如果文本值未知，而我们只想执行一些简单的操作(例如追加，前置，大写/小写)，我就无法简单地找到答案.

However, if the text value is unknown and we just want to do some simply operation such as append, prepend, upper/lower case, I can't simply find an answer to it.

我对jsonb设置函数没有提供仅尝试更新jsonb内的text属性大小写的微不足道的操作感到惊讶.

I was surprised by the fact that jsonb set function does not offer such a trivial operation that only try to update the case of a text property inside a jsonb.

有人可以帮忙吗?

推荐答案

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

The third argument of jsonb_set() should be of jsonb type. The problem is in casting a text string to jsonb string, you need a string in double quotes. You can use concat() or 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对象:

---

In Postgres 9.4 you should unnest the json column using jsonb_each_text(), aggregate keys and values modifying the proper value on the fly, and finally build a json object:

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;

如果您可以创建函数，则此解决方案可能会更令人满意:

If you can create functions this solution might be more pleasant:

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;

这篇关于如何修改PostgreSQL JSONB数据类型内的单个属性值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！