CODEWITHSUNDEEP
mysqljson
cloude
cloude

Reputation: 346

Update string JSON with mySql query

I have this mySql (ver 5.7.14) fields.

id  shop_name   json_string

 1   shop_1     [{"your_number": "2", "player_id": "6789" }, {"your_number": "3", "player_id": "9877" }, {"your_number": "4",   "player_id": "132456" }]  

 2   shop_2     [{"your_number": "2", "player_id": "6789" }, {"your_number": "3", "player_id": "9877" }, {"your_number": "4",   "player_id": "132456" }]

how can I update string based on id and JSON your_number?

For example I'd like remove your_number = 3 string where id = 2

Result:

id  shop_name   json_string
 2   shop_2     [{"your_number": "2", "player_id": "6789" }, {"your_number": "4",   "player_id": "132456" }]

thanks!

Upvotes: 0

Views: 94

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562348

I tested this and it works:

UPDATE Shops
SET json_string = JSON_REMOVE(
  json_string, 
  SUBSTRING_INDEX(
    JSON_UNQUOTE(JSON_SEARCH(json_string, 'one', '3', null, '$[*].your_number')), '.', 1)
)
WHERE id = 2;

I have to comment that this does NOT make JSON seem like a good idea for your application.

If you have a requirement to manipulate JSON documents, it would be easier to store your database as a normalized set of tables.

CREATE TABLE Shops (
  id INT PRIMARY KEY,
  shop_name VARCHAR(10)
);

CREATE TABLE ShopPlayers (
  shop_id INT NOT NULL,
  your_number INT NOT NULL,
  player_id INT NOT NULL,
  PRIMARY KEY (shop_id, your_number)
);

Now you can remove a player with more straightforward SQL:

DELETE FROM ShopPlayers WHERE shop_id = 2 AND your_number = 3;

I've been watching questions about mysql and json on Stack Overflow for a while, and I have to say that in virtually all cases I've seen, it would be better if the tables were designed in a traditional way, according to rules of normalization. The SQL queries would be easier to write and easier to debug, they would run faster, and the database would store the data more efficiently.

Upvotes: 1

Related Questions