0

The problem

Each entity owns an id and a json field. That json field simply stores a json list of objects. 
Entity{ id, json }
"1, '[{"tag": "Player"}, {"position": {"x": 20, "y": 20}}]'"

The order of those json objects is not always the same and i want to update the json object inside the array where "tag" :"Player". I basically wanna change the tag.

I tried to use json_replace, but it didnt worked because it seems like that function does not accept the $** wildcard. But i cant use $[0] because that json object is not always at the first position. Thats what i tried.

UPDATE entity
SET jsonComponents = JSON_REPLACE(
    jsonComponents ,
    '$**.tag' ,
    'NewTag'
)
WHERE
    entity.id = 1

The Question

How are we supposed to modify/remove an json object inside an pure json list, if we dont know where its located at ? How can we modify/remove a json object inside a list regardless of its position inside the list ?

Im actually very glad for any help on this topic, couldnt find anything about it...

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
genaray
  • 1,080
  • 1
  • 9
  • 30
  • I think you need to use `JSON_SEARCH()` to find the position. – Barmar Mar 01 '21 at 21:55
  • This seems like poor design of your JSON. An array should usually be homogeneous. Why isn't this an object? e.g. `{"tag": "Player", "position": {"x": 20, "y": 20}}` – Barmar Mar 01 '21 at 21:56
  • @Barmar Im gonna try search, thanks ! Well... actually thats not my fault. Im using a game framework and that one serializes lists.. oh wait... no thats actually intended, why ? Because of composition > inheritance. Its for an game, where i use components. – genaray Mar 01 '21 at 21:57

1 Answers1

-1

The solution

If we dont know the path of the json object we seek to modify... we simply query for the path using json_search

update entity 
set jsonComponents = JSON_REPLACE(
    jsonComponents,
     JSON_UNQUOTE(json_search(jsonComponents, 'one', 'Player')),
    'NewTag'
)
where entity.id = 0
genaray
  • 1,080
  • 1
  • 9
  • 30