Mysql: Update json column with values from different table/column

I’ve been trying to update the below neighbors column in location table as json object, as seen below, but cannot get this even remotely close to working in any way. Any idea on the query syntax?

Original table:

location
---------------
id  name  neighbors
1   loc1
2   loc2
3   loc3
4   loc4
5   loc5

nearby (this is join table where place & nextdoor refer to location.id)

nearby
-------------------
place   nextdoor    distance    group
1       2           500m         g1
1       3           900m         g1
2       1           500m         g3
2       3           100m         g4
2       4           80m          g4

Expected after update:

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
4   loc4
5   loc5

Answer

Give this a shot. It’s tested on MySQL 8. I broke it down into steps so you can test each CTE on after the other to see how it comes together.

Tables

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

Update query

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;

Result

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                                                                                                                                                        

Example

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=789fb79d10b745414ca7270a5a6ef004

Another one with an example of json_extract after the update has taken place.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fe651372ca754cc05eab842d6fced9a3