使用另一列的值更新PostgreSQL中的JSONB列

Question

我想将数据从一个列（varchar）迁移到另一个列（jsonb）

   Column   |            Type             |                       Modifiers                        
------------+-----------------------------+--------------------------------------------------------
 id         | integer                     | not null default nextval('merchants_id_seq'::regclass)
 name       | character varying           | not null
 nameb      | jsonb                       | not null default '{}'::jsonb

这样nameb将变为{"en": "$name"}，其中$name是name字段中的值。

例如：

SELECT name, nameb

之前：

                 name                 |   nameb    
--------------------------------------+------------
 hello                                | {} 
 world                                | {}

之后：

                 name                 |   nameb    
--------------------------------------+------------
 hello                                | {"en": "hello"}
 world                                | {"en": "world"}

对于常规类型，我可以使用 UPDATE SET whatever = (SELECT ...)，但是如何在jsonb中实现呢？

UPDATE merchants SET nameb = (SELECT '{"en": "fillme!"}'::jsonb); 是可行的，但是如何从另一个字段设置 "fillme!" 的值呢？

- Oleg Antonyan

Answer 1

这可以通过jsonb_build_object函数来实现，该函数允许您从简单的数据类型构建json对象。

因此，要做你想要的事情：

update merchants set nameb = nameb || jsonb_build_object('en', name)

使用json_build_object，我们可以动态地基于"name"列的值创建{"en": "hello"}, {"en": "world"}等内容。 之后，我们可以使用||运算符简单地将jsonb值连接起来。

如果nameb为NULL，则这种方法不起作用，因为NULL会“吞掉”所有内容，结果仍然是NULL。 在这种情况下，我建议使用COALESCE：

update merchants set nameb = COALESCE(nameb, '{}') || jsonb_build_object('en', name)

使用jsonb_set函数也可以实现相同的操作。对于这个特定的情况来说，这种方法可能有点过度，但如果您需要在json中深入设置某些键，则可能会很方便：

update merchants set nameb = jsonb_set(nameb, '{en}', ('"' || name || '"')::jsonb)

这看起来很奇怪，因为我们必须构建被引号包围的字符串，例如：'"hello"' ，才能将其设置为“en”键的值。如果您需要设置一些JSON，则使用jsonb_build_object更方便。

- Dmitry Chirkin

Answer 2

我找到了解决方案：

UPDATE merchants AS m1 
SET nameb = (
  SELECT row_to_json(t) FROM (
    SELECT name as en FROM merchants AS m2 WHERE m1.id = m2.id
  ) t
)::jsonb;

不确定是否正确，但它能够正常工作

- Oleg Antonyan

Answer 3

是的，jsonb_build_object 是最好的选择。

UPDATE merchants 
SET nameb = jsonb_build_object('en', "name", 
                               'cs', '')
WHERE ...

创建

             name         |   nameb    
--------------------------+------------------------------
 hello                    | {"en": "hello", "cs": ""}
 world                    | {"en": "world", , "cs": ""}

- Dingo