Updating with jsonb_set

March 26, 2023

I have two tables:

_building:

id	cityId	location
34891	111	{"regionId": "55", "regionName": null, "full address": "…"}
09372	222	{"regionId": null, "regionName": null, "full address": "…"}

_city:

id	name	location
111	A	{"regionId": "55", "regionName": "Magic region 11"}
222	B	{"regionId": "238", "regionName": "Magic region 23"}

I need to enrich the location column of _building table with data of the location column of table _city.

That is, after completing my task, the data of table _building should become like this:

id	cityId	location
34891	111	{"regionId": "55", "regionName": "Magic region 11", "full address": "…"}
09372	222	{"regionId": "238", "regionName": "Magic region 23", "full address": "…"}

I tried to use such a query, but for some reason it did not help and did not even put down the value for the regionName column

update _building b
set location = jsonb_set(
        b.location,
        '{location,regionName}',
        c.location -> 'regionName',
        false
    )
from _city c
where c.id = cityId

>Solution :

The path on the jsonb_set is wrong, it should be '{regionName}' :

update _building b
set location = jsonb_set(
        b.location,
        '{regionName}',
        c.location -> 'regionName',
        false
    )
from _city c
where c.id = cityId

Demo here