Updating with jsonb_set

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

Leave a Reply