Most elegant way to search for a json value and replace it in mysql? [closed]

I have a table called Entity which stores a bunch of jsons.

Entity
ID | Json 

The Json is basically a array of json objects. The order of the json objects is different each time…

[
    {
        "class" : "com.parallelorigin.code.ecs.components.Identity",
        "id" : 0,
        "tag" : "player",
        "typeID" : "3:1"
    },
    {
        "class" : "com.parallelorigin.code.ecs.components.transform.Transform",
        "position" : {
            "x" : 51.845858,
            "y" : 8.299743
        },
        "previousPos" : null
    },
    {
        "class" : "com.parallelorigin.code.ecs.components.collision.RangeCollider",
        "range" : 0.000100
    },
    {
        "class" : "com.parallelorigin.code.ecs.components.transform.Movement",
        "moveTo" : null,
        "speed" : 0.001000
    },
    {
        "baseDamage" : 2,
        "class" : "com.parallelorigin.code.ecs.components.combat.PhysicalDamage",
        "damage" : 0
    },
    {
        "class" : "com.parallelorigin.code.ecs.components.graphical.Mesh",
        "id" : 6
    },
    {
        "class" : "com.parallelorigin.code.ecs.components.items.Inventory"
    },
    {
        "class" : "com.parallelorigin.code.ecs.components.combat.Health",
        "health" : 100,
        "maxHealth" : 100
    },
    {
        "attackSpeed" : 1,
        "baseAttackSpeed" : 1,
        "class" : "com.parallelorigin.code.ecs.components.combat.AttackSpeed"
    },
    {
        "class" : "com.parallelorigin.code.ecs.components.animation.AnimationController",
        "controllerName" : "standardAnimationController"
    }
]

I want to replace some of the class paths, add new fields to the json object, modify existing ones or remove them.

For example, i want to replace com.parallelorigin.code.ecs.components.Identity with com.parallelorigin.code.ecs.components.Identity, add a new “name” field to the same json object, adjust the existing tag and remove the typeID completly.

What is the most elegant/easiest way of modifiying that Json array in MySQL ?

Answer

You can get the element of the json array given a value:

mysql> select substring_index(json_unquote(json_search(json, 'one', 'com.parallelorigin.code.ecs.components.Identity')), '.', 1) as element from entity;
+---------+
| element |
+---------+
| $[0]    |
+---------+

That gives the location of the element you need to replace. Then you can use JSON_REPLACE() to change it, something like this:

UPDATE Entity SET json = 
  json_replace(json, 
    substring_index(json_unquote(json_search(json, 'one', 'com.parallelorigin.code.ecs.components.Identity')), '.', 1),
    json_object(
      'class', 'com.parallelorigin.code.ecs.components.Identity', 
      'id', 0, 
      'name', 'some name')
  )
WHERE id = 1;

That is the method, but there’s nothing elegant about it. That’s typical of using JSON in MySQL. Using JSON in an SQL database usually makes data manipulation much harder.

Using JSON in MySQL, you are practically certain to experience the Inner-Platform effect.

The elegant solution would be to store the data in a normalized fashion.