Warm tip: This article is reproduced from serverfault.com, please click

Mysql JSON update by value of a key

发布于 2020-11-28 09:02:35

I have a table like:

CREATE TABLE `campus_tb` (
 `campus_id` int(11) NOT NULL AUTO_INCREMENT,
 `campus_dataJSON` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`campus_dataJSON`)),
 PRIMARY KEY (`campus_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

INSERT INTO `campus_tb`( `campus_dataJSON`) VALUES ( '[
                          {"id":"100","u_email": "dr@kol.vop","name":"James","age":"17","course":"IT"},
                          {"id":"101","u_email": "meg@gmail.com","name":"Eric","age":"19","course":"CS"},
                          {"id":"102","u_email": "kitt@joko.com","name":"Julie","age":"21"}]')




+--------------------+-----------------------------------------------------------+
| campus_id          |  campus_dataJSON                                          | 
+--------------------+-----------------------------------------------------------+
| 1                  |  [
                     |     {"id":"100","u_email": "dr@kol.vop","name":"James","age":"17","course":"IT"},
                     |     {"id":"101","u_email": "meg@gmail.com","name":"Eric","age":"19","course":"CS"},
                     |     {"id":"102","u_email": "kitt@joko.com","name":"Julie","age":"21"}
                     |
                     |  ] 
----------------------------------------------------------------------------------  
| 2                  |  [
                     |     {"id":"12","u_email": "dr2@kol.vop","name":"Fomu","age":"17","course":"IT"},
                     |     {"id":"13","u_email": "meg2@gmail.com","name":"Jenga","age":"19","course":"CS"},
                     |     {"id":"18","u_email": "kitt2@joko.com","name":"Billie","age":"21"}
                     |
                     |  ] 
----------------------------------------------------------------------------------    

Am using 10.4.15-MariaDB

((1)) MySql query UPDATE the details for a student based on their "email" WHERE campus_id = 1 for example I'd like to add "admitted":"YES" where email = 'meg@gmail.com' AND campus_id=1

`{"id":"101","u_email": "meg@gmail.com","name":"Eric","age":"19","course":"CS", "admitted":"YES" }`

((2)) Mysql Query to UPDATE from "age":"21" to "age":"25" where email = 'kitt@joko.com' AND campus_id=1

This is what I have tried so far for both ((1)) and ((2)):

UPDATE `campus_tb` set `campus_dataJSON` = JSON_SET( `campus_dataJSON` , json_unquote(json_search( `campus_dataJSON` , 'one', 'dr@kol.vop')), JSON_MERGE(`campus_dataJSON`,'$.admitted','YES') ) where `campus_id` = 1 //Strangely, this clears out all data in the column.

UPDATE `campus_tb` set `campus_dataJSON`  = JSON_MERGE(   `campus_dataJSON`  ,    json_unquote(json_search(`campus_dataJSON`  , 'one', 'meg@gmail.com')),   JSON_OBJECT('$.admitted','YES')) where `campus_id` =1;

UPDATE `campus_tb` set `campus_dataJSON`  =  = JSON_INSERT(`campus_dataJSON` , '$.admitted', "YES") WHERE `campus_dataJSON`->'$.u_email' = 'dr@kol.vop'; // this returns ERROR near '>u_email'


 UPDATE `campus_tb` set `campus_dataJSON`  =  = JSON_SET(`campus_dataJSON` , '$.age', "25") WHERE `campus_dataJSON`->'$.u_email' = 'kitt@joko.com'; // this returns same ERROR near '>email'

EXAMPLE FROM A DIFFERENT WEBSITE

I saw this

UPDATE players SET player_and_games = JSON_INSERT(player_and_games, '$.games_played.Puzzler', JSON_OBJECT('time', 20)) WHERE player_and_games->'$.name' = 'Henry';  

From this site: https://www.compose.com/articles/mysql-for-your-json/

But using same method throws Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$.email' = '

Questioner
ErickBest
Viewed
0
ErickBest 2020-11-28 22:08:59

I was assisted by @nbk from a different chat

for the ((1)): to add "admitted":"YES" where email = 'meg@gmail.com'

UPDATE campus_tb
SET `campus_dataJSON` = 
JSON_INSERT(`campus_dataJSON`, CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`campus_dataJSON`, 'one', 'meg@gmail.com'),'.',1),'"',''),'.admitted'), 'YES')
WHERE campus_id = 1;

RESULT: {"id": "101", "u_email": "meg@gmail.com", "name": "Eric", "age": "19", "course": "CS", "admitted": "YES"}

for the ((2)): to UPDATE from "age":"21" to "age":"25" where email = 'kitt@joko.com' AND campus_id=1

UPDATE campus_tb
SET `campus_dataJSON` 
=  JSON_REPLACE(`campus_dataJSON`, CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`campus_dataJSON`, 'one', 'kitt@joko.com'),'.',1),'"',''),'.age'), 35)
WHERE campus_id = 1;

RESULT: {"id": "102", "u_email": "kitt@joko.com", "name": "Julie", "age": 35}