MySQL 8.0

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');

Records: 5  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0


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
7b226731223a20225b7b5c226e616d655c223a205c226c6f63325c222c205c2264697374616e63655c223a205c223530306d5c227d2c7b5c226e616d655c223a205c226c6f63335c222c205c2264697374616e63655c223a205c223930306d5c227d5d227d

2
7b226733223a20225b7b5c226e616d655c223a205c226c6f63315c222c205c2264697374616e63655c223a205c223530306d5c227d5d222c20226734223a20225b7b5c226e616d655c223a205c226c6f63335c222c205c2264697374616e63655c223a205c223130306d5c227d2c7b5c226e616d655c223a205c226c6f63345c222c205c2264697374616e63655c223a205c2238306d5c227d5d227d


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;

Rows matched: 2  Changed: 2  Warnings: 0




id
name
neighbors


1
loc1
7b226731223a20225b7b5c226e616d655c223a205c226c6f63325c222c205c2264697374616e63655c223a205c223530306d5c227d2c7b5c226e616d655c223a205c226c6f63335c222c205c2264697374616e63655c223a205c223930306d5c227d5d227d

2
loc2
7b226733223a20225b7b5c226e616d655c223a205c226c6f63315c222c205c2264697374616e63655c223a205c223530306d5c227d5d222c20226734223a20225b7b5c226e616d655c223a205c226c6f63335c222c205c2264697374616e63655c223a205c223130306d5c227d2c7b5c226e616d655c223a205c226c6f63345c222c205c2264697374616e63655c223a205c2238306d5c227d5d227d

3
loc3
null

4
loc4
null

5
loc5
null

place	stuff
1	7b226731223a20225b7b5c226e616d655c223a205c226c6f63325c222c205c2264697374616e63655c223a205c223530306d5c227d2c7b5c226e616d655c223a205c226c6f63335c222c205c2264697374616e63655c223a205c223930306d5c227d5d227d
2	7b226733223a20225b7b5c226e616d655c223a205c226c6f63315c222c205c2264697374616e63655c223a205c223530306d5c227d5d222c20226734223a20225b7b5c226e616d655c223a205c226c6f63335c222c205c2264697374616e63655c223a205c223130306d5c227d2c7b5c226e616d655c223a205c226c6f63345c222c205c2264697374616e63655c223a205c2238306d5c227d5d227d

id	name	neighbors
1	loc1	7b226731223a20225b7b5c226e616d655c223a205c226c6f63325c222c205c2264697374616e63655c223a205c223530306d5c227d2c7b5c226e616d655c223a205c226c6f63335c222c205c2264697374616e63655c223a205c223930306d5c227d5d227d
2	loc2	7b226733223a20225b7b5c226e616d655c223a205c226c6f63315c222c205c2264697374616e63655c223a205c223530306d5c227d5d222c20226734223a20225b7b5c226e616d655c223a205c226c6f63335c222c205c2264697374616e63655c223a205c223130306d5c227d2c7b5c226e616d655c223a205c226c6f63345c222c205c2264697374616e63655c223a205c2238306d5c227d5d227d
3	loc3	null
4	loc4	null
5	loc5	null