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

Question

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

Any SQL experts having a solution? Head is going crazy trying to figure this query out. I need to transfer this data to Elasticsearch eventually, hence have to pull data into 1 json object. — Mike
– Mike, Commented Jun 11, 2020 at 2:56

zedfoxus · Accepted Answer · 2020-06-11 06:37:02Z

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

answered Jun 11, 2020 at 6:37

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mike Over a year ago

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

zedfoxus Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Tables

Update query

Result

Example

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Tables

Update query

Result

Example

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related