Mysql JSON通过键的值进行更新

Question

我有一个类似于的表

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": "[e-mail]","name":"James","age":"17","course":"IT"},
{"id":"101","u_email": "[e-mail]","name":"Eric","age":"19","course":"CS"},
{"id":"102","u_email": "[e-mail]","name":"Julie","age":"21"}]')


+--------------------+-----------------------------------------------------------+
| campus_id          |  campus_dataJSON                                          | 
+--------------------+-----------------------------------------------------------+
| 1                  |  [
|     {"id":"100","u_email": "[e-mail]","name":"James","age":"17","course":"IT"},
|     {"id":"101","u_email": "[e-mail]","name":"Eric","age":"19","course":"CS"},
|     {"id":"102","u_email": "[e-mail]","name":"Julie","age":"21"}
|
|  ] 
----------------------------------------------------------------------------------  
| 2                  |  [
|     {"id":"12","u_email": "[e-mail]","name":"Fomu","age":"17","course":"IT"},
|     {"id":"13","u_email": "[e-mail]","name":"Jenga","age":"19","course":"CS"},
|     {"id":"18","u_email": "[e-mail]","name":"Billie","age":"21"}
|
|  ] 
----------------------------------------------------------------------------------    

Am使用10.4.15-MariaDB

((1((MySql查询根据学生的"email"WHERE campus_id = 1更新学生的详细信息例如，我想添加"admitted":"YES"，其中email='[e-mail]'ANDcampus_id=1

`{"id":"101","u_email": "[e-mail]","name":"Eric","age":"19","course":"CS", "admitted":"YES" }`

((2((Mysql查询以从"age":"21"更新到"age":"25"，其中email='[e-mail]'ANDcampus_id=1

这是我迄今为止为((1((和(((2((所做的尝试：

UPDATE `campus_tb` set `campus_dataJSON` = JSON_SET( `campus_dataJSON` , json_unquote(json_search( `campus_dataJSON` , 'one', '[e-mail]')), 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', '[e-mail]')),   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' = '[e-mail]'; // this returns ERROR near '>u_email'

UPDATE `campus_tb` set `campus_dataJSON`  =  = JSON_SET(`campus_dataJSON` , '$.age', "25") WHERE `campus_dataJSON`->'$.u_email' = '[e-mail]'; // this returns same ERROR near '>email'

来自不同网站的示例

我看到这个

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

从此网站：https://www.compose.com/articles/mysql-for-your-json/

但使用相同的方法抛出错误：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' = '

Answer 1

因为这个`campus_dataJSON`->'$.u_email' = '[e-mail]'抛出一个错误：

mysql> select json_search(campus_dataJSON,'one','[e-mail]') from campus_tb;
+-------------------------------------------------+
| json_search(campus_dataJSON,'one','[e-mail]') |
+-------------------------------------------------+
| "$[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' |
+---------------------------------+
| "[e-mail]"                    |
+---------------------------------+
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') |
+-----------------------------------------------+
| [e-mail]                                    |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql>

EDIT：这将把campus_ id＝1中的用户的CCD_；[e-mail]">

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="[e-mail]"
;

执行SELECT * from data:时，ctedata进程

+------+--------+------+-------+---------------+------+
| i    | campus | id   | name  | email         | age  |
+------+--------+------+-------+---------------+------+
|    0 |      1 | 100  | James | [e-mail]    | 17   |
|    0 |      2 | 100  | James | [e-mail]    | 17   |
|    1 |      1 | 101  | Eric  | [e-mail] | 25   |
|    1 |      2 | 101  | Eric  | [e-mail] | 19   |
|    2 |      1 | 102  | Julie | [e-mail] | 21   |
|    2 |      2 | 102  | Julie | [e-mail] | 21   |
+------+--------+------+-------+---------------+------+

注意：10的数量应设置为高于校园内的最大学生人数。

Answer 2

我得到了来自另一个聊天的@nbk的帮助

对于((1((：添加"admitted":"YES"，其中email = '[e-mail]'

UPDATE campus_tb
SET `campus_dataJSON` = 
JSON_INSERT(`campus_dataJSON`, CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`campus_dataJSON`, 'one', '[e-mail]'),'.',1),'"',''),'.admitted'), 'YES')
WHERE campus_id = 1;
RESULT: {"id": "101", "u_email": "[e-mail]", "name": "Eric", "age": "19", "course": "CS", "admitted": "YES"}

对于((2((：从"age":"21"更新到"age":"25"，其中email = '[e-mail]'与campus_id=1

UPDATE campus_tb
SET `campus_dataJSON` 
=  JSON_REPLACE(`campus_dataJSON`, CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`campus_dataJSON`, 'one', '[e-mail]'),'.',1),'"',''),'.age'), 35)
WHERE campus_id = 1;
RESULT: {"id": "102", "u_email": "[e-mail]", "name": "Julie", "age": 35}