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?

Dmitry Chirkin · Accepted Answer · 2021-03-01 09:00:55Z

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.

Oleg Antonyan · Accepted Answer · 2016-03-27 12:00:52Z

3

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

answered Mar 27, 2016 at 12:00

Oleg Antonyan

3,1434 gold badges33 silver badges48 bronze badges

Comments

Dingo · Accepted Answer · 2021-04-07 14:44:27Z

2

Yes, jsonb_build_object is best choice.

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

create

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

answered Apr 7, 2021 at 14:44

Dingo

2,7041 gold badge19 silver badges16 bronze badges

Collectives™ on Stack Overflow

PostgreSQL update JSONB column with value from another column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related