PostgreSQL update JSONB column with value from another column

Question

I want to migrate data from one column (varchar) to another column (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

So that nameb will became {"en": "$name"} where $name is a value in name field.

For example:

SELECT name, nameb

before:

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

after:

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

With regualar types I can do UPDATE SET whatever = (SELECT ...), but How to do this with jsonb?

UPDATE merchants SET nameb = (SELECT '{"en": "fillme!"}'::jsonb); works, but how to set "fillme!" value from another field?

Answer 1

This can be done with jsonb_build_object function which allows you to build json objects from simple data types.

So to do what you want:

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

With json_build_object we are making {"en": "hello"}, {"en": "world"} ..dynamically based on value from "name" column. After that we can simply concat to jsonb values with || operator.

This will not work if nameb is NULL because NULL will "eat" everything and result will be NULL again. In that case I'd suggest to use COALESCE:

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

The other way to achieve the same is to use jsonb_set function. For this particluar case it's overkill, however it may be handy if you need to set some keys somewhere deeply in json:

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

This looks weird because we have to construct string surrounded of quotes , i.e: '"hello"' to set it as value for 'en' key. In case if you need to set some json, jsonb_build_object is more handy.

Answer 2

I found solution:

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;

Not sure if it's right, but it works