3

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
Mike
  • 75
  • 7

1 Answers1

2

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

zedfoxus
  • 35,121
  • 5
  • 64
  • 63
  • This is awesome, Thanks a lot! But two things: 1) The arrays are quoted making them a string value. They should be array of objects. Like the {"g1": "[{...}]"} should be {"g1": [{..}]}. 2) Can the slashes be trimmed prior to inset? I can use the select query to do it but if its clean prior to inset its even better: SELECT replace(neighbors, '\\', '') from location – Mike Jun 11 '20 at 08:05
  • My intent was to give you a solution and you can tweak it from here on. You may have to end up using stored procedure, take pieces of SQL from this answer and build a JSON to your liking. – zedfoxus Jun 11 '20 at 12:14
  • Absolutely! You had a very interesting and valid use case and you presented that very well. It wasn't an easy one. Thanks for the good follow up questions you asked. Your thought process is so clear. It'll lead you to a good answer. – zedfoxus Jun 11 '20 at 16:22