提问人：user1251973 提问时间：11/16/2023 最后编辑：Shadowuser1251973 更新时间：11/16/2023 访问量：42
修改存储为 JSON 的列数据
Modify column data stored as JSON
问：
假设我有 json 列作为JSON_COLUMN，我在其中以以下格式存储数据
表结构：（仅 2 列）
ID - Autoincrement
JSON_COLUMN - Varchar2(1000)
示例数据：
ID : 1
JSON_COLUMN :
[
{
"Name":"Number",
"Id":"PhoneNumber",
"Value":"+393123456789"
},
{
"Name":"Name",
"Id":"FirstName",
"Value":"John"
},
{
"Name":"City",
"Id":"CityID",
"Value":"NYC"
}
]
现在我想要更新mariadb的查询，它应该将City的值从NYC替换为CALI
更新表名集 JSON_COLUMN = ???? 不确定在这里保留什么，其中 id = 1;
答：
1赞
nbk
11/16/2023
#1
MySQL 和 MariaDB 没有数据类型 varchar2，但 Oracle 有。
使用一个简单的可以帮到你。REPLACE
如果 NYC 一词在您的 json 中只有一个，这就足够了
CREATE TABLE mytable (ID int Auto_increment primary key,
JSON_COLUMN Varchar(1000))
INSERT INTO mytable VALUES (NULL,'[
{
"Name":"Number",
"Id":"PhoneNumber",
"Value":"+393123456789"
},
{
"Name":"Name",
"Id":"FirstName",
"Value":"John"
},
{
"Name":"City",
"Id":"CityID",
"Value":"NYC"
}
]')
UPDATE mytable SET JSON_COLUMN = REPLACE(JSON_COLUMN,'NYC','CALI') WHERE ID = 1
Rows matched: 1 Changed: 1 Warnings: 0
SELECT * FROM mytable
| 编号 | JSON_COLUMN |
|---|---|
| 1 | [ { “name”：“number”， “id”：“PhoneNumber”， “value”：“+393123456789” }， { “name”：“name”， “id”：“FirstName”， “value”：“john” }， { “name”：“city”， “id”：“CityID”， “value”：“CALI” } ] |
如果您知道索引，则可以使用 JSON_REPLACE
UPDATE mytable
SET JSON_COLUMN = JSON_REPLACE(JSON_COLUMN,'$[2].Value','CALI') WHERE ID = 1
Rows matched: 1 Changed: 1 Warnings: 0
SELECT JSON_EXTRACT(JSON_COLUMN,'$[2].Value') FROM mytable
| JSON_EXTRACT（JSON_COLUMN，'$[2].值'） |
|---|
| “卡利” |
评论
0赞
Bill Karwin
11/16/2023
如果“NYC”出现在 JSON 文档的另一部分，这难道不是错误的事情吗？
0赞
nbk
11/16/2023
@BillKarwin我澄清了这一点，每个用户只有一个 CityID，所以它在 JSON 中应该是唯一的
0赞
user1251973
11/16/2023
非常感谢它按预期工作。但我同意比尔·卡文的观点。如何处理这种情况？
1赞
nbk
11/16/2023
JSON 是保存数据的好方法格式，但是要编辑特定部分，它会变得很快，而且很混乱，所以你应该获取 JSON 并使用其他语言来解析 JSON 并更新它
0赞
nbk
11/16/2023
@user1251973我添加了一个版本，但您现在需要数组中的索引
1赞
Bill Karwin
11/16/2023
#2
下面是一个解决方案，它只更新与数组中的键“Value”相对应的“NYC”。
with cte as (
select ID, j.* from tablename
cross join json_table(JSON_COLUMN, '$[*]' columns(
ord for ordinality,
Value varchar(100) path '$.Value')
) as j
where j.Value = 'NYC'
)
update tablename cross join cte
set tablename.json_column = json_set(json_column, concat('$[', cte.ord-1, '].Value'), 'CALI')
where cte.id = tablename.id;
这在MySQL 8.0或更高版本中有效，但在MariaDB中无效，因为MariaDB之前不支持通用表表达式。update
演示：https://dbfiddle.uk/tN9Q9-Od
以下是 MariaDB 10.6 或更高版本的解决方案（不适用于旧版本）：
update tablename
cross join (
select ID, j.* from tablename
cross join json_table(JSON_COLUMN, '$[*]' columns(
ord for ordinality,
Value varchar(100) path '$.Value')
) as j
where j.Value = 'NYC'
) as t
set tablename.json_column = json_set(json_column, concat('$[', t.ord-1, '].Value'), 'CALI');
演示：https://dbfiddle.uk/fKHp8BmZ
评论
0赞
user1251973
11/16/2023
我需要mariadb
上一个：从 CSV 同步表？
下一个：特殊的 MariaDB 优化结果
评论