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
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.
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;
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
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