序列化sql server中使用OpenJson()反序列化的对象

Question

我想要序列化一个使用OPENJSON()反序列化的对象。我在SQL Server中存储以下对象作为NVARCHAR。

{
"Name":"Name1",
"Everyday":false,
"EveryWeek":true,
"RecurRange":"12-Apr-2022$12-Sep-2022"
}

使用OPENJSON()代替Name字段，我反序列化回来，存储在一个临时表中，键，值作为列，更新了Name。

现在我想把这个表序列化回初始格式。尝试使用FOR JSON AUTO:

转换

CREATE TABLE #InfoTable([Key] NVARCHAR(MAX),Value NVARCHAR(MAX),Type INT)
INSERT INTO #InfoTable SELECT * FROM OpenJson(@jsondata)
UPDATE #InfoTable SET Value = 'NewName' WHERE [Key] = 'Name'
DECLARE @NewJson NVARCHAR(MAX) = (select * FROM #InfoTable FOR JSON AUTO)

但似乎不工作:

[
{"Key":"Name","Value":"NewName","Type":1},
{"Key":"Everyday","Value":"false","Type":3},
{"Key":"EveryWeek","Value":"true","Type":2},
{"Key":"RecurRange","Value":"18-Apr-2022$14-May-2022","Type":1}
]

是否有一种方法将OpenJson返回的表转换回字符串?

Answer 1

如果要修改单个JSON对象中的键值，则需要JSON_MODIFY():

DECLARE @jsondata nvarchar(max) = N'{"Name":"Name1","Everyday":false,"EveryWeek":true,"RecurRange":"12-Apr-2022$12-Sep-2022"}'
SELECT @jsondata = JSON_MODIFY(
@jsondata,
'$.Name',
'NewName'
)
SELECT @jsondata

如果你想使用当前的方法，你需要使用OPENJSON()和显式模式:

DECLARE @jsondata nvarchar(max) = N'{"Name":"Name1","Everyday":false,"EveryWeek":true,"RecurRange":"12-Apr-2022$12-Sep-2022"}'
SELECT *
INTO #InfoTable 
FROM OPENJSON(@jsondata) WITH (
Name nvarchar(max),
Everyday bit,
EveryWeek bit,
RecurRange nvarchar(max)
)
UPDATE #InfoTable 
SET Name = 'NewName' 
DECLARE @newjson NVARCHAR(MAX) = (
SELECT * 
FROM #InfoTable 
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
)
SELECT @newjson

最后，正如@Charlieface所建议的，您可以使用FOR JSON PATH来简化语句:

DECLARE @jsondata nvarchar(max) = N'{"Name":"Name1","Everyday":false,"EveryWeek":true,"RecurRange":"12-Apr-2022$12-Sep-2022"}'
SELECT @jsondata = (
SELECT 'NewName' AS Name, Everyday, EveryWeek, RecurRange
FROM OPENJSON(@jsondata) WITH (
Name nvarchar(max),
Everyday bit,
EveryWeek bit,
RecurRange nvarchar(max)
)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)