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' = '
