He estado tratando de actualizar la neighbors column a continuación en location table como objeto json, como se ve a continuación, pero no puedo hacer que esto funcione ni remotamente de ninguna manera. ¿Alguna idea sobre la sintaxis de la consulta?
tabla original:
location --------------- id name neighbors 1 loc1 2 loc2 3 loc3 4 loc4 5 loc5cercano (esta es la tabla de unión donde el lugar y al lado se refieren a 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 g4Esperado después de la actualización:
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 loc5Dale una oportunidad a esto. Está probado en MySQL 8. Lo dividí en pasos para que pueda probar cada CTE uno tras otro para ver cómo encaja.
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 ) update location l join final f on l.id = f.place set l.neighbors = f.stuff;
identificación | nombre | vecinos
-: | :--- | :------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------
1 | loc1 | {"g1": "[{\"nombre\": \"ubicación2\", \"distancia\": \"500m\"},{\"nombre\": \"ubicación3\", \"distancia\ ": \"900m\"}]"}
2 | loc2 | {"g3": "[{\"nombre\": \"ubicación1\", \"distancia\": \"500m\"}]", "g4": "[{\"nombre\": \" loc3\", \"distancia\": \"100m\"},{\"nombre\": \"loc4\", \"distancia\": \"80m\"}]"}
3 | loc3 | nulo
4 | loc4 | nulo
5 | loc5 | nulo
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=789fb79d10b745414ca7270a5a6ef004
Otro con un ejemplo de json_extract después de que se haya realizado la actualización.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fe651372ca754cc05eab842d6fced9a3