Delete Data JSON from table MySql

Question

Hi I need to remove from a json table everything that contains the name weapon_pistol50 , this is one of my tables in mysql

{"weapons":[{"ammo":74,"name":"WEAPON_PISTOL50"},{"ammo":118,"name":"WEAPON_PISTOL50"},{"ammo":54,"name":"WEAPON_PISTOL"}]}

The table is named: datastore_data

and the column that contains json format is called data.

I want to update all the tables by deleting this from the json: '{"ammo":118,"name":"WEAPON_PISTOL50"}'

I haven't tested many variables for now, but I need to do the above.

Answer 1

Here's a solution tested on MySQL 8.0:

update datastore_data cross join json_table(data, '$.weapons[*]' 
  columns( 
    i for ordinality, 
    ammo int path '$.ammo', 
    name varchar(20) path '$.name'
  )
) as j 
set data = json_remove(data, concat('$.weapons[', j.i-1, ']')) 
where j.ammo = 118 and j.name = 'WEAPON_PISTOL50';

If you are using a version of MySQL too old to support JSON_TABLE(), then it's a lot harder.

Frankly, it would be far easier if you didn't use JSON. Instead, store one weapon per row in a second table with normal columns named ammo and name.

create table weapons(
  id serial primary key,
  owner_id int,
  ammo int,
  name varchar(20)
);

Then you could do this task much more simply:

delete from weapons
where ammo = 118 and name = 'WEAPON_PISTOL50';

Storing data in JSON documents might seem like a convenient way to load complex data into a single row of a single table, but virtually every task you have to do with that data afterwards becomes a lot harder than if you had used normal tables and columns.