Serialize an object deserialized using OpenJson() in sql server

user_9118398 0
Viewed 42

I want to serialize an object back that I had deserialized using OPENJSON(). I am storing below oject in SQL Server as NVARCHAR.

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

To replace Name field using OPENJSON(), I deserialized back, stored inside a temporary table with key, value as columns, updated the Name.

Now I want to Serialized back that table to the initial format. Tried converting using 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)

But doesn't seem to work:

[
{"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}
]

Is there a way to convert the OpenJson returned table back to string?

sql-server sql
1 Answers

If you want to modify a value of a key in a single JSON object, you need 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

If you want to use your current approach, you need to use OPENJSON() with explicit schema:

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

Finally, as @Charlieface suggested, you can simplify the statement using 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
)
user_65780800
Related