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 to 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' = '
Best Answer
Json isn't that complicated, but update an entry is complicated, especially when you want to be flexible
The first update query is the query, which you would use to find the correct array index and add your field admitted
The second shows you the same only in a shorter version, so that you get the concept.
As i said in my comment, when you already know that you need to update, change the the, use a normalized data structure, that makes your live much easier
db<>fiddle here