Mysql JSON update by value of a key

Question

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

doubole posting is not good https://dba.stackexchange.com/questions/280487/mysql-json-update-by-value-of-a-key — nbk, Nov 28 '20 at 12:28

Luuk · Answer 1 · 2020-11-28T11:16:21.407

Because this `campus_dataJSON`->'$.u_email' = 'dr@kol.vop' throws an error:

mysql> select json_search(campus_dataJSON,'one','dr@kol.vop') from campus_tb;
+-------------------------------------------------+
| json_search(campus_dataJSON,'one','dr@kol.vop') |
+-------------------------------------------------+
| "$[0].u_email"                                  |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select campus_dataJSON->'$[0].u_email' from campus_tb;
+---------------------------------+
| campus_dataJSON->'$[0].u_email' |
+---------------------------------+
| "dr@kol.vop"                    |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select json_unquote(campus_dataJSON->'$[0].u_email') from campus_tb;
+-----------------------------------------------+
| json_unquote(campus_dataJSON->'$[0].u_email') |
+-----------------------------------------------+
| dr@kol.vop                                    |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql>

EDIT: This will update the age to 25 for the user in campus_id=1 with the emailaddres "meg@gmail.com"

WITH RECURSIVE abc as (
    SELECT 0 as i
    UNION ALL
    SELECT i+1 FROM abc WHERE i<10), -- See note!
    data as (
    SELECT 
    i,
    campus_id as campus,
    json_unquote(json_extract(campus_dataJSON,CONCAT("$[",i,"].id"))) as id,
    json_unquote(json_extract(campus_dataJSON,CONCAT("$[",i,"].name"))) as name,
    json_unquote(json_extract(campus_dataJSON,CONCAT("$[",i,"].u_email"))) as email,
    json_unquote(json_extract(campus_dataJSON,CONCAT("$[",i,"].age"))) as age
    FROM abc
    CROSS JOIN campus_tb 
    WHERE not json_extract(campus_dataJSON,CONCAT("$[",i,"].id")) is null)
UPDATE `campus_tb`,`data` 
SET campus_dataJSON = json_set(campus_dataJSON,CONCAT('$[',i,'].age'),25)
WHERE campus_id = campus and campus=1 and email="meg@gmail.com"
;

The cte data procuces, when doing SELECT * from data:

+------+--------+------+-------+---------------+------+
| i    | campus | id   | name  | email         | age  |
+------+--------+------+-------+---------------+------+
|    0 |      1 | 100  | James | dr@kol.vop    | 17   |
|    0 |      2 | 100  | James | dr@kol.vop    | 17   |
|    1 |      1 | 101  | Eric  | meg@gmail.com | 25   |
|    1 |      2 | 101  | Eric  | meg@gmail.com | 19   |
|    2 |      1 | 102  | Julie | kitt@joko.com | 21   |
|    2 |      2 | 102  | Julie | kitt@joko.com | 21   |
+------+--------+------+-------+---------------+------+

Note: the number 10 should be set higher than the maximum number of students in a campus.

Thanks, however, could you do it with UPDATE instead of SELECT?... There is absolutely no issue with SELECT, but UPDATE and setting the DATA.... knowing that a campus can have about 1000 students or more and students can be identified by there Email address — ErickBest, Nov 28 '20 at 10:03
Not sure but I get `Unrecognized statement near WITH` .... thanks for the efforts really. Let me know if you know a workaround — ErickBest, Nov 28 '20 at 13:17
`WITH` is only available in Mysql8.0, for a fiddle see [this](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a5a09b64cc97ab986549451326675f74) — Luuk, Nov 28 '20 at 13:42
How to do WITH in MySQL before 8.0 is [here](https://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql) — Luuk, Nov 28 '20 at 13:44
WITH should be supported in Mariadb10.4, but it only allows to do a SELECT, see: https://mariadb.com/kb/en/recursive-common-table-expressions-overview/#summary-so-far — Luuk, Nov 28 '20 at 13:59

score 0 · Accepted Answer · answered Nov 28 '20 at 14:08

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}

Mysql JSON update by value of a key

2 Answers2