Advertisements
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