1

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

  • why use json in the first place. a normalized databse, gets you the data, and is simple to update, and if you d´then need json you can still build it. – nbk Nov 28 '20 at 11:21
  • Would you know why this approach doesnt work ? UPDATE players SET player_and_games = JSON_INSERT(player_and_games, '$.games_played.Puzzler', JSON_OBJECT('time', 20)) WHERE player_and_games->'$.name' = 'Henry'; – ErickBest Nov 28 '20 at 11:30
  • notice the WHERE player_and_games->'$.name' = 'Henry'; – ErickBest Nov 28 '20 at 11:32
1

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

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"}]');
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"}]');
✓

✓

✓
SELECT CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`campus_dataJSON`, 'one', 'meg@gmail.com'),'.',1),'"',''),'.admitted') FROM campus_tb;
| CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`campus_dataJSON`, 'one', 'meg@gmail.com'),'.',1),'"',''),'.admitted') |
| :---------------------------------------------------------------------------------------------------------------- |
| $[1].admitted                                                                                                     |
| $[1].admitted                                                                                                     |
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 = 3;
UPDATE campus_tb
SET `campus_dataJSON` = 
 JSON_INSERT(`campus_dataJSON`, '$[1].admitted', 'YES')
WHERE campus_id = 2;
SELECT * FROM `campus_tb`
campus_id | campus_dataJSON                                                                                                                                                                                                                                                           
--------: | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        2 | [{"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", "admitted": "YES"}, {"id": "102", "u_email": "kitt@joko.com", "name": "Julie", "age": "21"}]
        3 | [{"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", "admitted": "YES"}, {"id": "102", "u_email": "kitt@joko.com", "name": "Julie", "age": "21"}]
UPDATE campus_tb
SET `campus_dataJSON` =  JSON_REPLACE(`campus_dataJSON`, '$[2].age', 25)
WHERE campus_id = 2;
UPDATE campus_tb
SET `campus_dataJSON` 
=  JSON_REPLACE(`campus_dataJSON`, CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`campus_dataJSON`, 'one', 'kitt@joko.com'),'.',1),'"',''),'.age'), 25)
WHERE campus_id = 3;
SELECT * FROM `campus_tb`
campus_id | campus_dataJSON                                                                                                                                                                                                                                                         
--------: | :-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        2 | [{"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", "admitted": "YES"}, {"id": "102", "u_email": "kitt@joko.com", "name": "Julie", "age": 25}]
        3 | [{"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", "admitted": "YES"}, {"id": "102", "u_email": "kitt@joko.com", "name": "Julie", "age": 25}]

db<>fiddle here

  • This 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 = 3; ... PERFECTLY SOLVED the ((1)) question. ... Am now still banging my head for the ((2)) Mysql Query to UPDATE from "age":"21" to "age":"25" where email = 'kitt@joko.com' AND campus_id=1 . DO YOU have any idea?. I'd indeed ACCEPT the answer for sure – ErickBest Nov 28 '20 at 13:09
  • as you can see it is always the same, you have to check the different json fucntions that you have, and test it, see changed answer. – nbk Nov 28 '20 at 13:24
  • Just one word: WONDERFUL. I have ACCEPTED – ErickBest Nov 28 '20 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.