sql - 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 中的一个值 字段。

例如:

选择名字，名字b

之前:

                 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!”来自另一个领域的值(value)？

Answer 1

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

所以做你想做的:

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

使用 json_build_object，我们正在制作 {"en": "hello"}, {"en": "world"} ..动态地基于来自 "name"列的值。之后我们可以简单地使用 || 连接到 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 会更方便。