By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table location (id int, name varchar(10), neighbors json);
insert into location (id, name) values
(1, 'loc1'), (2, 'loc2'), (3, 'loc3'), (4, 'loc4'), (5, 'loc5');
create table nearby (place int, nextdoor int, distance varchar(10), mygroup varchar(10));
insert into nearby values
(1, 2, '500m', 'g1'), (1, 3, '900m', 'g1'),
(2, 1, '500m', 'g3'), (2, 3, '100m', 'g4'), (2, 4, '80m', 'g4');
with flatten_neighbor as (
select nn.place, nn.name, l.name as nextdoorname, nn.distance, nn.mygroup
from (
select l.id, l.name, n.place, n.nextdoor, n.distance, n.mygroup
from location l
join nearby n on n.place = l.id
) nn
join location l on l.id = nn.nextdoor
),
obj_by_place as (
select *, json_object('name', nextdoorname, 'distance', distance) as jo from flatten_neighbor
),
concat_obj as (
select place, mygroup, concat('[', group_concat(jo), ']') as jason from obj_by_place group by place, mygroup
),
final as (
select place, json_objectagg(mygroup, jason) stuff from concat_obj group by place
)
select * from final;
| place | stuff |
|---|---|
| 1 | {"g1": "[{"name": "loc2", "distance": "500m"},{"name": "loc3", "distance": "900m"}]"} |
| 2 | {"g3": "[{"name": "loc1", "distance": "500m"}]", "g4": "[{"name": "loc3", "distance": "100m"},{"name": "loc4", "distance": "80m"}]"} |
with flatten_neighbor as (
select nn.place, nn.name, l.name as nextdoorname, nn.distance, nn.mygroup
from (
select l.id, l.name, n.place, n.nextdoor, n.distance, n.mygroup
from location l
join nearby n on n.place = l.id
) nn
join location l on l.id = nn.nextdoor
),
obj_by_place as (
select *, json_object('name', nextdoorname, 'distance', distance) as jo from flatten_neighbor
),
concat_obj as (
select place, mygroup, concat('[', group_concat(jo), ']') as jason from obj_by_place group by place, mygroup
),
final as (
select place, json_objectagg(mygroup, jason) stuff from concat_obj group by place
)
update location l join final f on l.id = f.place set l.neighbors = f.stuff;
select * from location;
| id | name | neighbors |
|---|---|---|
| 1 | loc1 | {"g1": "[{"name": "loc2", "distance": "500m"},{"name": "loc3", "distance": "900m"}]"} |
| 2 | loc2 | {"g3": "[{"name": "loc1", "distance": "500m"}]", "g4": "[{"name": "loc3", "distance": "100m"},{"name": "loc4", "distance": "80m"}]"} |
| 3 | loc3 | null |
| 4 | loc4 | null |
| 5 | loc5 | null |
select * from location where json_extract(neighbors, '$.g3');
| id | name | neighbors |
|---|---|---|
| 2 | loc2 | {"g3": "[{"name": "loc1", "distance": "500m"}]", "g4": "[{"name": "loc3", "distance": "100m"},{"name": "loc4", "distance": "80m"}]"} |