json - Mysql JSON 更新键值
问题描述
我有一张像这样的桌子:
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","name":"James","age":"17","course":"IT"},
{"id":"101","name":"Eric","age":"19","course":"CS"},
{"id":"102","name":"Julie","age":"21"}]')
+--------------------+-----------------------------------------------------------+
| campus_id | campus_dataJSON |
+--------------------+-----------------------------------------------------------+
| 1 | [
| {"id":"100","name":"James","age":"17","course":"IT"},
| {"id":"101","name":"Eric","age":"19","course":"CS"},
| {"id":"102","name":"Julie","age":"21"}
|
| ]
----------------------------------------------------------------------------------
| 2 | [
| {"id":"34","name":"Mimi","age":"18","course":"IT"},
| {"id":"35","name":"Jose","age":"20","course":"CS"},
| {"id":"36","name":"Ken","age":"24"}
|
| ]
----------------------------------------------------------------------------------
我正在使用 MariaDB 10.4
MySql 查询 UPDATE the details for Julie of
campus_id = 1
添加“课程”让她变成这样:{"id":"102","name":"Julie","age":"21","course":"MASCOM"},
Mysql Query to UPDATE从“Eric”到“Erick”,末尾带有“k”
到目前为止我已经尝试过:
UPDATE `campus_tb`
SET `campus_dataJSON` = JSON_SET (`campus_dataJSON`, TRIM( '"' FROM JSON_SEARCH(`campus_dataJSON`,'one','Julie')),'MASCOM')
WHERE
`campus_id` = 1 AND
JSON_EXTRACT(`campus_dataJSON`,'$.id') = '102';
这返回0 rows affected
......我已经大量搜索。
解决方案
基本上你应该使用JSON_SET函数。例如,第一个问题可以通过以下方式解决:
update campus
set campus_data = json_set(
campus_data, '$[2].course', 'MASCOM')
where campus_id = 1;
如果您不知道 JSON 路径,您可以使用JSON_SEARCH和JSON_UNQUOTE ,例如:
update campus
set campus_data = json_set(
campus_data,
json_unquote(json_search(campus_data, 'one', 'Eric')),
'Erick'
)
where campus_id = 1;
在这里提琴
推荐阅读
- python - 如何打印特定级别的所有节点?
- reactjs - 如何在我的 JS 应用程序中为 Google Drive 文件夹创建搜索栏?
- javascript - 在 mousemove 函数上获取光标坐标的问题
- laravel - Laravel S3 文件上传 - PutObject 操作需要非空参数:Bucket
- django - Django - 异常值:if 标记中的表达式意外结束
- for-loop - 为什么我的 for 循环增加超过它应该停止的位置?
- javascript - 即使用户点击了他们的偏好并关闭了框,Cookie 通知也会继续打开
- haskell - Haskell堆栈安装包,但模块无法导入
- python - Python sounddevice 示例不发出任何声音(树莓派)
- python - 执行 np.where 字段为日期类型时的类型提升无效