SQL Server JSON_Modify，如何全部更新? [英] SQL Server JSON_Modify, How to Update all?

问题描述

我正在尝试使用Json_Modify的值更新所有列:

i am trying update all columns with a value with Json_Modify:

DECLARE @JSON NVARCHAR(MAX)
SET @JSON = 
N'{
"A":1,
"TMP": [    
    {"A":"VALUE1", "B": "VALUE2", "C": 1},
    {"A":"VALUE3", "B": "VALUE4", "C": 2},
    {"A":"VALUE5", "B": "VALUE6", "C": 3}]}
'

SET @JSON = JSON_MODIFY(@JSON, '$.TMP.A', 'JEJE')

SELECT * FROM OPENJSON(@JSON, '$.TMP') WITH ( A NCHAR(10), B NCHAR(10), C INT )

例如，我需要用"JEJE"更新所有列"A"，但它不起作用.

I need update all columns "A" with "JEJE" for example, it is not working.

推荐答案

这里有两个选项.免责声明:我不是通过SQL Server 2016进行JSON的专业人士，但是我已经一起破解了一些东西.

Here are two options. Disclaimer: I am not a pro at JSON through sql server 2016, but I have hacked some stuff together.

选项1:您显然是根据JSON字符串创建结果集.为什么不创建结果集然后进行更新?

Option 1: You are clearly creating a result set from the JSON string. Why not create the result set and then update it?

DECLARE @jsontable TABLE (A varchar(50), b varchar(50), c varchar(50))
DECLARE @JSON NVARCHAR(MAX)

SET @JSON = 
N'{
"A":1,
"TMP": [    
    {"A":"VALUE1", "B": "VALUE2", "C": 1},
    {"A":"VALUE3", "B": "VALUE4", "C": 2},
    {"A":"VALUE5", "B": "VALUE6", "C": 3}]}
'

--SET @JSON = JSON_MODIFY(@JSON, '$.TMP.A', 'JEJE')
INSERT INTO @jsontable (a,b,c)
SELECT * FROM OPENJSON(@JSON, '$.TMP') WITH ( A NCHAR(10), B NCHAR(10), C INT )

UPDATE @jsontable 
SET a = 'JEJE' 

SELECT * 
FROM @jsontable

选项2:您可以操纵JSON组件，但是必须将索引传递给该函数.

Option 2: you can manipulate the JSON components, but you have to pass the index to the function.

DECLARE @JSON NVARCHAR(MAX)
SET @JSON = 
N'{
"A":1,
"TMP": [    
    {"A":"VALUE1", "B": "VALUE2", "C": 1},
    {"A":"VALUE3", "B": "VALUE4", "C": 2},
    {"A":"VALUE5", "B": "VALUE6", "C": 3}]}
'

SET @JSON = JSON_MODIFY(@JSON, '$.TMP[0].A', 'JEJE')
SET @JSON = JSON_MODIFY(@JSON, '$.TMP[1].A', 'JEJE')
SET @JSON = JSON_MODIFY(@JSON, '$.TMP[2].A', 'JEJE')

SELECT * FROM OPENJSON(@JSON, '$.TMP') WITH ( A NCHAR(10), B NCHAR(10), C INT )

这篇关于SQL Server JSON_Modify，如何全部更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！