sql - 在 SQL Server 表中更新 json 数组
问题描述
我在 SQL Server 列中有一个 json 数组,我正在尝试将所有名称更新为“Joe”。
我尝试了下面的代码,但它只更新了 json 数组的第一个元素
CREATE TABLE #t (I INT, JsonColumn NVARCHAR(MAX) CHECK (ISJSON(JsonColumn) > 0))
INSERT INTO #t
VALUES (1, '[{"id":"101","name":"John"}, {"id":"102","name":"peter"}]')
INSERT INTO #t VALUES (2,'[{"id":"103","name":"dave"}, {"id":"104","name":"mark"}]')
SELECT * FROM #t
SELECT * FROM #t
CROSS APPLY OPENJSON(JsonColumn) s
WITH cte AS
(
SELECT *
FROM #t
CROSS APPLY OPENJSON(JsonColumn) s
)
UPDATE cte
SET JsonColumn = JSON_MODIFY(JsonColumn, '$[' + cte.[key] + '].name', 'Joe')
SELECT * FROM #t
-- DROP TABLE #t
它只是将数组的第一个元素更新为 joe
当前结果:
[{"id":"101","name":"Joe"}, {"id":"102","name":"cd"}]
[{"id":"103","name":"Joe"}, {"id":"104","name":"mark"}]
预期的
[{"id":"101","name":"Joe"}, {"id":"102","name":"Joe"}]
[{"id":"103","name":"Joe"}, {"id":"104","name":"Joe"}]
解决方案
由于您想在一个事务中进行操作,因此除了创建另一个表并将值存储到新表中并将值用于 XML 路径之外,我想不出任何其他方法。问题是您正在尝试更新 JSON 数组,但我不确定如何使用不同的值更新同一行两次。如您所示,使用交叉应用它会创建两行,然后只有您可以将其更新为 JOE。
您的查询将根据值列更新 name = Joe for ID = 101 和 Name = Joe for ID = 102。由于它们位于两个不同的行上,因此您只能在临时表中看到一个变化。
我又创建了一个 #temp2 表来存储这些值并使用 XML 路径进行连接。决赛桌将是您预期结果的#t2 桌。
SELECT *
into #t2
FROM #t
CROSS APPLY OPENJSON(JsonColumn) s
select *, json_value (value, '$.name') from #t2
UPDATE #t2
SET value = JSON_MODIFY(value, '$.name', 'Joe')
select t.I ,
JSONValue = concat('[',stuff((select ',' + value from #t2 t1
where t1.i = t.i
for XML path('')),1,1,''),']')
from #t2 t
group by t.I
输出:
I JSONValue
1 [{"id":"101","name":"Joe"},{"id":"102","name":"Joe"}]
更新原表:
update t
set t.JsonColumn =t2.JSONValue
from #t t
join (select t.I ,
JSONValue = concat('[',stuff((select ',' + value from #t2 t1
where t1.i = t.i
for XML path('')),1,1,''),']')
from #t2 t
group by t.I ) t2 on t.I = t2.i
推荐阅读
- javascript - 如何将本地 json 文件渲染成 HTML 和浏览器
- bash - How do I not have a duplicated custom bash script directory on my machine and add it as a link that is picked up with PATH?
- java - Java Web SQL: No suitable driver found for jdbc, but driver is added in project and code works fine local
- mysql - MYSQL Random Entry with weight fails
- laravel - Two models, two fields, return preferred if present
- jenkins - 无法在 Jenkins 中通过 SSH 连接远程服务器
- javascript - 对带有 role='button' 的 div 元素的点击不起作用
- javascript - 将 X,Y 像素转换为经度和纬度
- php - 应该如何预先加载属性?
- perl - perl 脚本中的 Tcl 调用